I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

W-Rule

"Attitude is a little thing that makes a big difference." [Winston Churchill]

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.

U1 - Understanding Excel array formulas

See, for example, Chip Pearson's introduction page on array formulas.

U2 - 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: 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

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

Another example: Filling gaps of a table with linearly interpolated values

20091010_Interpolate_Fill_Gaps

The results of Charles Williams' FastExcel © clearly show that even in this small example helper columns are preferable:

The sum of column D, E and F runtimes (0.27ms) is smaller by far than those of B (0.47ms) or C (0.66ms).

20091010_Interpolate_Fill_Gaps_FastExcel
Sulprobil   Get it done   Contact   Disclaimer   Download