In Defence of Array Formulas [Business Functions/General Excel]

[john]

Array formulas are formulas that return arrays, ie more than one number. The simplest example would be to type in: ={1,2,3}. Press 'array-enter' (Control-Shift-Enter) to enter it in a cell. You will get just '1'. But then highlight that cell and the two to the right, click in the formula pane at the top of screen, where the ={1,2,3} is showing, and press Control-Shift-Enter, and you have an array formula. If you still don't understand what array formulae are, do a quick Google on it.

The point is, array formulae get a rather patchy press. People generally concede that you can do some fancy things with them (1), but they CLAIM that they suffer from the following disadvantages:

1. No-one understands them
2. They are too complex
3. They slow a workbook down

Now, Business Functions supports array formulas in quite a big way, in fact, any projections function (eg MkPmts, Con, AnnGrow etc) can return an array simply by putting in the entire timebase in as the first Time parameter, and in that way you only need one formula per row (or column) for the whole timebase, and you don't need to keep copying formulae across. We use array formulae all the time now in our consultancy work as well as our Cashflow Modelling Seminars, and here's why, in order of importance:

1. They are faster. Yes, thats right, contrary to popular myth, the way we use them, to return an array from either a C++ or VBA (2) custom function, they are MUCH quicker. The reason for this is that Excel is relatively slow at updating a cell, but if you are updating a whole range of cells AT THE SAME TIME, then that overhead is minimised.
2. They are built in formula protection. This isn't something you'd expect, but that fiendish Control-Shift-Enter sequence is so fiddley that its good protection from destructive users (3). Also, if you try to insert a row or column in a array formula range, Excel won't do it and will complain. So its a great way of protecting your formulas.
3. They can be more readable and easier to maintain. Again, this is a surprising effect. When you don't have to copy cells across a whole timebase because the array formula is covering this for you, you don't need to dollar so many cell references, which makes the formula slightly shorter and more readable. Also, you KNOW that there is only one formula for the whole timebase, so you need to go searching for any exceptions.

(1) Like for example a multi-condition SUMIF(), just by using SUM() with any number of control arrays
(2) VBA custom functions are so slow that to me they ONLY make sense when used as array functions.
(3) There is one case where the Ctrl-Shift-Enter protection REALLY lets you down, and that is when you have an array formula (not function) in just one cell. In that case, inadvertently editing it (F2) will 'un-array' the formula, normally breaking it, and Excel won't say a word! One-cell array formulas are therefore not one of my favourite techniques.