Further improvements to the EQUS visualisation tool – array formulae
As people use EQUS more we come across interesting new requirement, here we describe how the visualisation has been enhanced for array formulae.
Why array formulae
An important improvement to EQUS is that it now visualises array formula. Array formula are formula for “power users”.
Imagine the common spreadsheet pattern: you copy a formula down a column to process a mass of data in rows. You use the same formula lots of times. Well a problem arises when you realise you need to update that formula. You have to change one version of the formula and make sure you copy and paste it again all the way down the row. Now that is easy, but it can lead to difficulties when the formulae are not documented. Poorly structured sheets, or someone else’s work or work you did years ago, all can make copying that new formula correctly rather hard.
Array formulae avoid this difficulty by allowing you to specify a formula overall the entire range of cells involved with just one expression. Once the expression is in place, the single array formula can be updated and it will immediately work across all the rows it involves. In a way this means that you end up being far more explicit with the cell inter-relations you want to compute. It also means you don’t have to rely on copying your formula correctly.
You can see when a formula is an array formula because it is shown the formula bar with curly braces around it. The braces are added by Excel, to enter an array formula you must press control-shift-enter. There is a lot more to array formulae, to find out more try searching using “excel array formula”, or “excel control-shift-enter” (see: Microsoft examples).
In our experience array formulae are a love hate thing. Those who have mastered them cannot believe how difficult life is without them. Others survive perfectly being disciplined with the copying of normal formulae.
An Example
Cut and paste
Here’s an example of how they work, we’ve included the EQUS visualisation throughout to help show how things are working. We start looking at the copy and paste approach, here the need is to compute row 2 added to the product of rows 3 and 4. First we do it for the first column, column B:
This figure shows how a normal copy and paste solution starts. The requirement was for row 2 to be added to row 3 times row 4, and figure shows this for column B. That formula is them copied to all the required columns, C and so on. For C5 the formula looks like this:
It is this situation where the general requirement is lost. All those copies are different individual formulae that just happen to match. There is nothing linking them but for our knowledge that they should all hold that addition and multiplication formula. For example, the formula in column P could be changed and we’d never know until we spotted something that would make us check it.
Array formula
The same addition and multiplication can be expressed in a array formula B2:V2+B3:V3*B4:V4. You can see the same pattern and operators, but the arguments are the range expressions for all the columns required. This captures the entire general requirement in one formula. To enter it into the spreadsheet first select the range of cells that are to hold the answers (in this example the light blue ones, B5:V5), then entering =B2:V2+B3:V3*B4:V4 and then pressing control-shift-enter.
The formula bar now shows: {=B2:V2+B3:V3*B4:V4} – the curly brackets show the formula is an array.
Cells B5 and C5 are shown below.
You see in these figures that the formula is exactly the same formula associated with each cell in the result range. By contrast to help judge that things are working as planned, the EQUS visualisation shows the formula and for any individual cell it shows the computation broken down. So you can see the general view given by the formula and the individual cases given by the data in any one element.
Differences
Array formulae do not have strictly line up, this allows more interesting expressions to be used. For example, for the figures computed row 5 we might need to see the differences between each column. This can be done by computing the difference between B5 and C5, C5 and D5, and so on. For just B5 and C5 the difference would be absolute value of one subtracted from the other: abs(B5-C5).
The cut and paste solution would be copy this across all the columns needed. But the array formula captures it completely in one: abs(B5:S5-C5:T5). Notice how one input range starts at B and runs to S, while the other starts at C and runs through to T.
Again the EQUS visualisation helps you see if it is doing what you want, by showing the formula and also the specific cases. Below shows C7:
And that’s not all
Array formula are in a way a shift to using more programming-like constructs within a spreadsheet. The simple examples are the most compelling but in general they can be used to harness significant power and efficiency. They can work across rows, columns and areas.
Interestingly Microsoft are now providing more powerful approaches to arrays of data with their dynamic array formulae. The principles they follow are much the same as the array formula discussed here.