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.
"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 |
|
1 |
3 |
3 |
|
2 |
8 |
11 |
|
3 |
5 |
16 |
|
4 |
1 |
17 |
|
5 |
6 |
23 |
|
Solution |
Runtime |
|
B1: =A1, B2: A2+B1, copy down |
0.51 |
|
=SUM(SUM(A1:INDEX(A1:A5,ROW(A1:A5)))) |
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 |
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).
[Sulprobil] [Get it done] [Organisation] [IT] [Controlling] [Human Resources] [Family] [Contact] [Download] [Disclaimer]