http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf

Post a reply to this message

Am 23.02.2013 17:17, schrieb Orchid Win7 v1:
> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf

No connection from here.

Post a reply to this message

I couldn't connect either--"The connection has timed out."

Post a reply to this message

Le 2013-02-23 11:29, clipka a écrit :
> Am 23.02.2013 17:17, schrieb Orchid Win7 v1:
>> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
>>
>
> No connection from here.
>

It's working from here.  I guess the Internet hamsters have gone back on 
their wheel.

The paper talks about user-defined functions in Excel for example, if 
column a has temperatures in Celcius, and you want column B to have them 
in Farenheit, you can easily type "=A1*9/5+32" in B1 and copy that the 
whole way down, but their ide was to create a function called, say 
"C2F()" that did that and then B1 would be: ="C2F(A1)".

Of course, this is a trivial example, but if you need to revisit your 
formula, modify one user-defined function would a lot simpler than 
having to hunt down every cell in the spreadsheet where it might have 
been used.

To Andy's point of it not catching on, The authors make it quite clear 
in the abstract that they don't like the idea of a full-fledged 
programming language being bolted-on to Excel.  I guess they got shot 
down when the Powers-That-Be(tm) decided that VBA would be the way to go.
-- 
/*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

On 02/23/2013 10:17 PM, Orchid Win7 v1 wrote:
> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
>
An interesting idea. However, it looks contradictory:
to have all calculations visible in cells on a function instance sheet
and to be faster than VBA function at the same time?
There is even a speed-up option in VBA to switch off cell updating.

Post a reply to this message

On 23/02/2013 11:01 PM, bart wrote:
> On 02/23/2013 10:17 PM, Orchid Win7 v1 wrote:
>> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf
>>
>>
> An interesting idea. However, it looks contradictory:
> to have all calculations visible in cells on a function instance sheet
> and to be faster than VBA function at the same time?
> There is even a speed-up option in VBA to switch off cell updating.

I don't think this is the problem. You can view the function sheet while 
you're developing it, and then turn it off when you have hundreds of 
instances of that function.

I think the real problem is more... Well, let me put it this way. I just 
tried to explain to my imaginary friend how this extension works. I 
spent about half an hour trying to come up with a simple way to explain 
it. I couldn't find one.

Post a reply to this message

On 23/02/2013 10:07 PM, Francois Labreque wrote:
> To Andy's point of it not catching on, The authors make it quite clear
> in the abstract that they don't like the idea of a full-fledged
> programming language being bolted-on to Excel. I guess they got shot
> down when the Powers-That-Be(tm) decided that VBA would be the way to go.

Reading the conclusion, it appears they never actually wrote the running 
code. They had a PowerPoint mockup, a demo written in Flash, and so on. 
But they never actually implemented a real Excel extension that really 
did work.

Also, the report makes quite clear that VBA already exists today. The 
problem is, the number of people who understand spreadsheets vastly 
outnumbers the number of people who can work something as abstract as 
VBA (or any other purely text-based language). Hence the motivation for 
this paper.

I guess MS figured there was no market for it. Which is not an 
unreasonable conclusion...

Post a reply to this message

On 23/02/2013 04:17 PM, Orchid Win7 v1 wrote:
> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf

For those unable to read the paper, a short summary...

The number of people who use spreadsheets dwarfs the number of 
professional computer programmers. And yet, some of these spreadsheets 
are extremely complex, sophisticated constructions. In the main, these 
are built not by trained programmers, but by accountants, salesmen, 
purchasing associates, and anybody else who has a need to do complicated 
numerical calculations.

A complex spreadsheet can be considered to be a "program", the formulas 
in the cells being its "source code". And yet, accountants don't 
generally sit down and learn to write code in C, C++, Java, Perl, 
Python, R, or even something like Mathematica which is heavily 
problem-oriented rather than machine-oriented. Why is that?

Arguably a spreadsheet is easier to comprehend. You type in a formula 
and it makes a number. You can immediately *see* what is happening. In 
traditional programming languages, you write a bunch of text, press go, 
and maybe some kind of result is produced. It's far less visual, even if 
you have a REPL to use. You don't literally *see* all the intermediate 
calculation steps, unless you fire up a purpose-built debugger.

Which a spreadsheet, there is no separate coding and debugging phase. 
You just have a grid of cells, which you can modify at will, at any 
time, for any reason. This makes it easier to gradually construct a 
sophisticated application from humble beginnings.

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 *only* way to repeat a calculation on more than one 
input is to literally copy and paste it. (And most spreadsheets have at 
least moderately sophisticated mechanisms for doing intelligent copy and 
pasting.)

As a trained computer programmer, this makes me gasp in horror. It is a 
clear violation of the DRY principle. But as an accountant, or a 
quantity surveyor, you're not going to know or care about such things. 
You're just trying to produce this month's figures (or whatever).

There *is* of course a facility in Excel to define functions. However, 
this requires you to learn Visual Basic, a completely different 
programming language, with radically different semantics. It's a bit 
like the way C lets you write macros, but in a totally different 
language (CPP), whereas Lisp lets you write macros in Lisp.

So, the main thrust of the paper is "what if you could write Excel 
functions in Excel?"

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.

That's the gist of the idea. Naturally, if you edit one instance of the 
function worksheet, the change affects *all* instances, automatically. 
And therein lies the payoff.

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.

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?

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.

In short, the solution is to UTTERLY BREAK THE SPREADSHEET MODEL! This 
is the least convincing part of the paper for me. The cost of the user 
learning this completely new mechanism seems too high compared to the 
modest payoff of handling ranges of different sizes. I think a better 
approach could be found.

So there we are. There's lots of screenshots, which gives the 
*impression* that they had a working product. In 2003. But in the 
conclusion they explain that these are simply mockups and simulations, 
not a real, usable product. They make it clear that they are in 
discussions with Microsoft about making this feature available in the 
real commercial Excel product (Simon PJ works at Microsoft Research 
Cambridge, after all). But, clearly, this never came to anything.

I think this could very easily be one of those features that nobody 
knows about and nobody ever uses, unless you made it *extremely* simple 
to comprehend and to use. For example, the vast majority of people who 
use Word apparently have no idea what the hell a "mail merge" is, or 
what Word has the capability to do such a thing. (Then again, most 
people probably don't *need* it either...)

I think it would be very cool to define functions in Excel, but I doubt 
Excel will be the one to pioneer this. I'm also not sure how much 
anybody would use it...

Post a reply to this message

> http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf

Shame. 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.

Also I just learned about the F4 key in Excel the other day (when used 
while editing functions) - I've been wanting that functionality for 
years but never bothered to look up if there was a keyboard shortcut...

Post a reply to this message

>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.

 >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.

 >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.

 >
 >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.

Post a reply to this message

> 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

Le 2013-02-25 07:53, bart a écrit :
>  >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 
qualifications, yet it's my sister who gets paid £££ to write complex 
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

Le 2013-02-26 03:20, scott a écrit :
>> 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