I am a supporter ofSt. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation toSt. Joseph's, please. Information onSt. Joseph's
 "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 andU2) 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
 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).