I find myself having to defend my intense dislike of Excel's array formulae methodology. Since
I’m no expert in this aspect of Excel, this consideration relies upon Chip Pearson’s advice.

In reality, most familiar Excel formulae use arrays like
=SUM(A2:A4). In this case the formula instructs Excel to sum all the values in
the array of cells {A2, A3, A4}. In fact the formula = SUM(A2, A3, A4) performs exactly the same task as =SUM(A2:A4).
Excel is also quite happy mixing
different forms of presentation such as =SUM(A2:A4, C5:C20, D1:D2). In short
Excel is pretty clever at interpreting user requirements.

Array formulae are different and the user must give Excel a
specific instruction for the software to handle the instruction correctly. This
takes the form of curly braces around the formulae {} generated by posting the
formula using Control, Shift and Enter.

Chip offers the example of {=AVERAGE(IF(A7:A13>0,A7:A13,FALSE))}.
Interpret this in plain English as for each element in the array A7:A13, if it
is more than zero include it is the average otherwise leave it out (FALSE). The
curly braces are absolutely crucial to this interpretation. Here is the evaluation
of this formula with the curly braces in place.

See cell B7 for the result 5.25 |

On the other hand the identical formula, if posted using
Enter it delivers the following result. This is a standard average.

See cell C7 for the result 2.142857 |

Even more confusing, if you list the formulae in your
worksheet by evaluating each cell for its formula you get the following. Here
it looks as if the identical formula cell delivers different results, because
formula is never listed with its curly braces.

To me this was sufficient to put me off ever using an array
formula, but in reality this is the more simple methodology.

You can use array
formulae to deliver an array of answers. The spreadsheet below has identical
formulae in each of cells in columns E {=ROW(A7:A15)} and similarly in column F {=A7:A13}. However, because they are entered
using the control, shift method each cell delivers a different answer
appropriate to its position in the array.

If, you list the formulae however it is not possible to tell
that these have been array entered – as shown below. The user or a reviewer has
to deduce the array entry methodology based on the number of identical
formulae which deliver different answers.

In each of these cases the identical formula appears to delivers a series of different answers |

In all other languages that I have used, it has always been
immediately obvious when working with arrays. It simplifies matters both for the
user and the auditor.

No matter how technically clever Excel’s array formula
methodology, I will always avoid it if I can.

## No comments:

## Post a comment