Circular References in Spreadsheets

When working on the spreadsheet for the Psion MC400 and Series 3 way back in the mid 80’s, I planned to use an implementation of Cambridge mathematician John Conway’s Game of Life as one of my test sheets. Life is an iterative simulation that, given a starting position, evolves at each iteration following a few very simple rules;

• If a cell is 1 (alive), it dies (becomes 0) if it has 1 or less live neighbours around it (underpopulation)
• A live cell dies if it has 4 or more live neighbours (overpopulation)
• Otherwise a live cell stays alive if it has 2 or 3 live neighbours
• A dead cell becomes alive if it has 3 live neighbours (reproduction)

The result is a visually appealing animated simulation of the evolution of life from the initial state.

To implement it in a spreadsheet you need to create 2 grids that reference each other (plus a 3rd for the initial state). These 2 grids cause a raft of circular references in the spreadsheet, the calculation order of which you need to manually control in order for the simulation to work correctly. This can be very messy to sort out, but very satisfying once you have it working.

This got me thinking about how you could simplify the solution to not only do it with just 1 grid not 2, but also to avoid circular references completely.

The problem is that spreadsheets assume that when you type in a calculation that accesses other cells that it is the latest value of the cells that you need, and orders the calculations appropriately. In spreadsheets this is called natural order sorting of calculations. In this case though, each cell wants to know the value of its neighbours (and itself) before they have been calculated, i.e. It wants the previous values not the latest.

You could have a special function to access this, say PREVIOUSVALUE(A1). This would increase readability, though it would require the natural order sort mechanism of the spreadsheet to recognise this function as special and ignore any cell references within it otherwise the calculation would still trigger a circular reference. This also only allows for single cell references, not ranges, and to be fully flexible it would be useful to make range references easy too.

A better solution then would be to use a modifier to cell and range references, and to cut a long story short, I decided on using the ‘ character for this purpose. So to access the previous value of a cell you would type A1’, and to access the previous value of a range you would type A1:C10’.

This means that the main calculation for the cells in our Game of Life simulation (ignoring initial state set up) could look something like this;

B2 = IF(SUM(A1:C3’)=3, 1, IF(SUM(A1:C3’)=4,IF(B2’,1,0),0))

Now I wasn’t about to add this functionality just to make it easier to create versions of the Game of Life in my spreadsheet, but I did feel that there must be a wealth of real world calculations where this ability to access values prior to the recalc would be useful. A simple recalc counter for instance is, well simple (A1 = A1’+1). You could access values further back in time through recursion (B1 = A1’, C1 = B1’, etc.). The main crunch of all this though was that you could create these iterative calculations while avoiding introducing circular references, so the natural order sorting of the spreadsheet calculations continued to do their job for you.

I thought this was a great idea, and it was relatively simple to implement, so I took it to the sales director to, well, sell it to him, and officially add it to the spec. His reply was harsh but somewhat understandable. It was very easy for his sales team to sell a Lotus 123 compatible spreadsheet (yeah, it was THAT long ago), so he didn’t want or need something extra to complicate what was already an easy sale.

So that was a no then, but that didn’t necessarily stop me. I figured I could still add the feature in as an Easter egg and quietly let slip about it after the launch. The MC400 version of the spreadsheet was set to be released 2 weeks after the release of the V2 upgrade of the MC400 operating system, and that release had slipped by a month. As the spreadsheet was dependent on features of the V2 upgrade, its release had also been slipped by a month. I had a really good team on the spreadsheet and we were on track (albeit with the need for a few obligatory overnighters) to make our original date. That extra month meant we could not only finish it without burning any midnight oil, but I’d still have time to add my little Easter egg.

Unfortunately the decision was made at the last minute to release the spreadsheet at the same time as the V2 upgrade. We still finished it with no overnighters (possibly a Psion 1st), but there was no longer enough time to add this feature. I still hoped to add it at a later date, but another sad turn of fate led to my parting ways with Psion before the Series 3 launch and so it never got to see the light of day.