> The *only* way to repeat a calculation on more than one
> input is to literally copy and paste it.

Or use data tables. Very useful if you've spent ages making a whole 
sheet (or more) to model something (your "function") and then want to 
repeat that calculation over a range of values. You are limited to two 
"parameters" though.

> The mechanism they chose is to let you define a special "function
> worksheet". Certain cells are the function's input, and a certain cell
> is the function's output. Every time you call that function, notionally
> a new copy of this function worksheet is dynamically created with the
> appropriate cells filled in, and whatever ends up in the output cell is
> copied back to the formula that mentions the function.

I wouldn't be surprised if that is exactly how data tables work 
internally already.

Post a reply to this message

>  >Problem #2 is "how do we support recursion?" They chose the solution
>  >"you don't". Which seems reasonable; how many people actually want to
>  >use that feature? Do we really want to complicate the interface further
>  >just to make those guys happy?
>
> The function definition can use the standard Excel =IF(,,) function
> to make a conditional result to stop the recursion.
> The rest would be provided by the standard mechanism of function calls.
>

I think Andy was talking about how Excel would support recursion, not 
how a user would implement a recusive function.  Currently Excel would 
throw a "Circular reference" error.


-- 
/*Francois Labreque*/#local a=x+y;#local b=x+a;#local c=a+b;#macro P(F//
/*    flabreque    */L)polygon{5,F,F+z,L+z,L,F pigment{rgb 9}}#end union
/*        @        */{P(0,a)P(a,b)P(b,c)P(2*a,2*b)P(2*b,b+c)P(b+c,<2,3>)
/*   gmail.com     */}camera{orthographic location<6,1.25,-6>look_at a }

Post a reply to this message

>> The function definition can use the standard Excel =IF(,,) function
>> to make a conditional result to stop the recursion.
>> The rest would be provided by the standard mechanism of function calls.
>
> I think Andy was talking about how Excel would support recursion, not
> how a user would implement a recusive function.  Currently Excel would
> throw a "Circular reference" error.

...unless you have checked the "Iteration" box under 
Options->Calculation. It's possible to then write a recursive formula, 
the classic example of course is factorial:

Cell A2: =IF(A1="",IF(A2<=1,A2,A2-1),A1+1)
Cell A3: =IF(A1="",A2*A3,1)

You can then type a number in cell A1, hit enter and then delete the 
number (the formula in A2 restarts the iteration when A1 goes empty). 
Cell A3 will then give you the answer.

Post a reply to this message

On 02/25/2013 08:42 PM, Francois Labreque wrote:
> I think Andy was talking about how Excel would support recursion, not
> how a user would implement a recusive function.  Currently Excel would
> throw a "Circular reference" error.
>

For the reference: it is not (always) an error,
sometimes this could be intentional.
In fact, Excel does support recursion (iterations),
it is just switched off by default,
because in most cases circular reference indeed
means an error somewhere.
But you can turn it on, check out:
Tools->Options->Calculations->Iteration, Maximum iterations.

Post a reply to this message

>> I think Andy was talking about how Excel would support recursion, not
>> how a user would implement a recusive function. Currently Excel would
>> throw a "Circular reference" error.
>
> ...unless you have checked the "Iteration" box under
> Options->Calculation. It's possible to then write a recursive formula,

Yes. The paper specifically mentions this, and how it breaks the 
spreadsheet model by making each cell's value time-dependent.

(They referred to it as a "dangerous feature". I'm not sure I'd go that 
far, but...)

Usually you use this because you expect the values involved to converge 
to some kind of fixed-point after a few iterations. If that actually 
turns out to be true, then time-dependent behaviour isn't such a big deal.

Post a reply to this message

On 25/02/2013 10:01 AM, scott wrote:
>> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
>>
>
> Shame.

Indeed.

> Actually IME I find a lot of people are able to write VBA macros,
> yet they purposely go to huge lengths to use only "native" Excel just to
> avoid the dreaded "this sheet has macros" warning when opened. In fact
> it turned into a bit of a competition at my old place; "I challenge you
> to implement this in Excel without a macro". It's surprising how
> versatile it is without VBA.

Well, that's true of anything.

I used to think shell scripts were just for invoking binary files in a 
certain order. But that's because I was writing MS-DOS shell scripts. If 
you write your scripts with Bash, it's quite frightening how much stuff 
you can do - all in a non-interactive, untyped, unchecked environment 
where almost anything can go wrong. Frightening stuff indeed! o_O

I'm sure I've quipped at some point that *I* have computing 

Excel spreadsheets laced with VBA code. And she has no computer training 
at all!

Post a reply to this message

On 25/02/2013 12:53 PM, bart wrote:
>  >When you start to look at a spreadsheet as a program,
>  >you realise that as a programming language,
>  >it's pretty damned weak.
>  >In particular, it is impossible to define new data types,
>  >and it has impossible to define subroutines.
>
> The lack of subroutines is the main concern of the paper,
> but a new data types (structures) can be simulated easily in Excel.
> Just put all the fields in the block of cells - and you have it.
> The one can access it by a reference.

Or, you /could/ do that if the indirect cell lookup functions weren't so 
hard to use.

>  >Trouble is... Well, actually there's a whole *host*
>  >of problems. Problem
>  >#1 is that your new function might be called twenty billion times.
>  >You're not going to display twenty billion worksheet tabs!
>  >The solution
>  >they came up with as that only one "instance"
>  >of each function sheet can
>  >be visible at once.
>
> It would be probably better to call it not an instance,
> but a function definition.
> The user's concern would be only to copy a working code
> to the function sheet and somehow indicate the locations
> of the parameters and the result.
> The extension (could be in a form of xll) should then
> take care of some kind of translation of the algorithm,
> derived from the function sheet, into, say,
> a sequence of calls and store it in the memory.
> The biggest problem is to make Excel to recognize
> the names of the new functions.
> But just for the working demonstration they could use a single
> function, say, ufun("function-sheet-name",params) which return a matrix.

They say the idea is to click on a cell and say "extract as function", 
and it'll generate the appropriate function sheet, and replace the 
original cell with a reference to that. Which *sounds* very nice... but 
they never actually implemented it.

>  >Problem #3 is how to support functions that take *ranges* of cells as
>  >input. Their solution was to make a cell matrix a first-class value,
>  >so that a single cell can contain an entire matrix.
>  >And since a matrix is made up of cells,
>  >matrices can be nested arbitrarily deep.
>
> This is not a big problem after all:
> Excel functions do support ranges
> (and expressions that operate on ranges)
> as their arguments.

Yeah, they chose to not allow user-defined functions to take cell ranges 
as input - which would seem the /logical/ solution to me. The trouble 
is, each function input becomes a cell on the function sheet. It's 
notoriously hard to deal with variable table sizes in a spreadsheet.

Another possibility would be to define each cell-range input as an 
entire virtual sheet in its own right - but designing a nice GUI for 
that would be difficult...

Post a reply to this message

> Yes. The paper specifically mentions this, and how it breaks the
> spreadsheet model by making each cell's value time-dependent.
>
> (They referred to it as a "dangerous feature". I'm not sure I'd go that
> far, but...)
>
> Usually you use this because you expect the values involved to converge
> to some kind of fixed-point after a few iterations. If that actually
> turns out to be true, then time-dependent behaviour isn't such a big deal.

I think at some point in the past I actually wrote a simple cpu 
instruction decoder and emulator in Excel. If you set "iterations" to 1 
each time you hit refresh it would increment the program counter, read 
and decode the instruction it pointed to and update registers etc. Was a 
pretty awesome abuse of Excel :-)

Post a reply to this message

>> Yes. The paper specifically mentions this, and how it breaks the
>> spreadsheet model by making each cell's value time-dependent.
>>
>> (They referred to it as a "dangerous feature". I'm not sure I'd go that
>> far, but...)
>>
>> Usually you use this because you expect the values involved to converge
>> to some kind of fixed-point after a few iterations. If that actually
>> turns out to be true, then time-dependent behaviour isn't such a big
>> deal.
>
> I think at some point in the past I actually wrote a simple cpu
> instruction decoder and emulator in Excel. If you set "iterations" to 1
> each time you hit refresh it would increment the program counter, read
> and decode the instruction it pointed to and update registers etc. Was a
> pretty awesome abuse of Excel :-)
>
This is, IMHO, the most awesome abuse of Excel.

WARNING: ontopicness ahead!

http://www.gamasutra.com/view/feature/3563/microsoft_excel_revolutionary_3d_.php

-- 
/*Francois Labreque*/#local a=x+y;#local b=x+a;#local c=a+b;#macro P(F//
/*    flabreque    */L)polygon{5,F,F+z,L+z,L,F pigment{rgb 9}}#end union
/*        @        */{P(0,a)P(a,b)P(b,c)P(2*a,2*b)P(2*b,b+c)P(b+c,<2,3>)
/*   gmail.com     */}camera{orthographic location<6,1.25,-6>look_at a }

Post a reply to this message