Abstract

“I can calculate the motion of heavenly bodies but not the madness of people.” [Isaac Newton]

Excel array formulas are powerful. They can be short and fast.

But: Many Excel users do not understand them. They are not able to adapt or to maintain them. Quite often the runtime increases drastically if more and more records are added to the array.

My w-rule for Excel array formulas is:

U1) understand them and

U2) u(a)void them.

Understanding Excel array formulas

See, for example, (external link!) Chip Pearson’s introduction page on array formulas.

U(a)void Excel array formulas

Array formulas should only be used if a series of cells is dependent on each other. See my solution for unique random integers, for example.

A nice example where you should NOT apply array formulas is shown below - but of course it is an interesting example to get more insight into them…

Example 1: Given a series of numbers in column A, how can we get the series of corresponding cumulative sums in column B?


A B Explanation for result in B
1 3 3 3 = 3
2 8 11 3 + 8 = 11
3 5 16 3 + 8 + 5 = 16
4 1 17 3 + 8 + 5 + 1 = 17
5 6 23 3 + 8 + 5 + 1 + 6 = 23


Solution Runtime [ms]
B1: =A1, B2: A2+B1, copy down 0.51
=SUM(SUM(A$1:INDEX(A$1:A5,ROW(A$1:A5))))} or non-array in B5 with ROWS instead of ROW and copy up 0.92
{=SUM(A1:INDEX(A1:A5,ROW(A1:A5)))} 0.96
{=SUM(OFFSET(A1,0,0,ROW(),1))} 1.25
{=SUBTOTAL(9,(OFFSET(A1:A5,,,ROW(INDIRECT(“1:5”)),1)))} 12.32
{=PROB(ROW(A1:A5),A1:A5/SUM(A1:A5),,ROW(A1:A5))*SUM(A1:A5)} 114.84
{=MMULT(–(ROW(A1:A5)>=TRANSPOSE(ROW(A1:A5))),A1:A5)} 534.70

Example 2: Filling gaps of a table with linearly interpolated values

Interpolate_Fill_Gaps

The results of Charles Williams' (external link!) FastExcel clearly show that even in this small example helper columns are preferrable:

Interpolate_Fill_Gaps_FastExcel