Spreadsheets Are Special
Written by Janet Swift   
Thursday, 17 September 2020
Article Index
Spreadsheets Are Special
Cellular Automata And Spreadsheets

Don't dismiss the spreadsheet. It not only brought programming to the masses, but was an early entry into rapid application development, functional programming and an easy and natural approach to parallel programming, reactive programming and cellular automata.


 More cartoon fun at xkcd a webcomic of romance,sarcasm, math, and language

I've been asked more than once why I Programmer bothers to cover spreadsheets. They are not, after all, obviously anything much to do with programming?



 Yes the first spreadsheets really did look like this - or worse!

That is where you would be wrong. The spreadsheet is one of the most amazing inventions of the computer age. It not only makes programming easy for non-programmers, it also impacts on some of our most pressing problems - how to create parallel programs. If you think that reactive programming is a hot topic, then think again because spreadsheets invented reactive programming. 




The spreadsheet isn't an obvious idea.

It may seems so now because a whole generation of programmers and users have grown up knowing not only the basic idea but experiencing it first hand. Want to add a few numbers - use a spreadsheet. It seems so obvious that it almost doesn't need inventing. As one whos programming career spans the invention of the spreadsheet I can tell you that before it was invented it wasn't obvious in the least. 

When spreadsheets were first introduced they were taken up by users who had been looking for something to unlock the power of the machine that they had bought into without the need to hire a programmer or even learn a programming language.

Spreadsheets met a need that few even guessed existed.

Today spreadsheets are often relegated to the task of ad-hoc data presentation by way of creating graphs and charts. Useful though this occupation is the real power of the spreadsheet lies in the two words "ad-hoc". You could say that spreadsheets were the first step towards rapid application development and perhaps even agile programming - but this might be a claim too far.




Loops and flow of control

Before the spreadsheet if you wanted to get a computer to do a simple sum you had to program it.

After all, the first high level programming language was called Fortran - FORmula TRANslation - as I'm sure you know. You could use other tools put together by programmers to do sums - like a command line calculator - but it was the spreadsheet that first allowed innocent users to type in mathematical formulae and have the computer work it out for them.

This was good but there is more.

If you needed to total a column of numbers before the spreadsheet you needed to know how to write a for loop and to work with an array. This is sophisticated stuff. But, after the spreadsheet, you could simply type in a formula that said something like Sum(A1:A10).




Now where did that for loop and the whole concept of iteration go?

The answer is that it is hidden in the range reference A1 to A10 and the function applied to it. Clever stuff but at the time spreadsheets were being invented no one thought about it like this. No one sat back and said

"I can get rid of iteration from programming by inventing the spreadsheet Sum function". 

But this is what it did as Sum(A1:A10) is equivalent to:

For i= A1 to A10
Next i

Users who had no idea what an enumeration loop was were very happy to write spreadsheet sum functions. 

In the same way the branching if statement was replaced by a simple IF function which returned one of two possible expressions depending on the condition. 

For example:


evaluates to 0 if A1 is zero and A2/A1 otherwise. Notice that this is exactly like the ternary operator ?  that is available in many languages e.g. A1==0?0,A2/A1 gives the same result. 

This hid from view the full idea of the flow of control splitting into two possible paths at each and every if statement.

However, you mustn't think that the spreadsheet version was in some way weaker because it only evaluated to one of two possible expressions. Because other cells in the spreadsheet could reference the result of an IF function it really did create a split in the flow of control.

Two different chains of computation could result from each IF function.

It has to be admitted that the spreadsheet way of implementing the conditional has never been as successful as its way of dealing with the loop. Ranges and functions seem simpler but IF functions are almost as complicated as if statements and while most spreadsheet users found summing a column trivial mastering conditional functions was more of a problem. 

In other word even the most novice spreadsheet user quickly discovered and used range functions like Sum they tended to be slower to figure out what the if function was all about. Even when they did discover the conditional they often used it in very limited ways. Oddly in the spreadsheet world it is the conditional that is the difficult idea - not iteration. 

Making Progress

 More cartoon fun at xkcd a webcomic of romance,sarcasm, math, and language





Last Updated ( Monday, 21 September 2020 )