Microsoft Excel cumulative calculation performance

Question

I know 2 ways to calculate cumulative values in Excel.

1st method:

A         B
Value     Cumulative total
9         =A1
8         =B1+A2
7         =B2+A3
6         =B3+A4

2nd method:

A         B
Value     Cumulative total
9         =SUM($A$1:A1)
8         =SUM($A$1:A2)
7         =SUM($A$1:A3)
6         =SUM($A$1:A4)

2 questions:

1. Which method has better performance when the data set gets really big (say 100k rows)? 1st method seems to be having less overhead. Because when adding a new value in column A (Value), new cell in column B only needs to do "B(n-1)+A(n)". Where in 2nd method, is it smart enough to do similar? Or it will adds 100k rows from A1:A(n)?

2. What's the best way to calculate the cumulative values? I found 2nd method is more popular though I doubt its performance. The only upside for 2nd method I can see now is the formula in column B cells are more consistent. In 1st method, the 1st cell in column B has to be a determined in advance.

Answer 1

I don't have any tangible evidence, but I just tried it on my excel (2007 though) on all the rows possible 1,048,576 rows) and the SUM() formula tells me there is not enough resources to carry out the operations (after asking me if I really wanted to continue as I wouldn't be able to undo the operations) while your 1st method does it in about 14 seconds.

So, I believe you are right on the fact that the 1st method has less overheads.

The advantage perhaps of SUM() over the first method is that if you have a problematic value in a cell (such as a text value), SUM() won't throw an error and continue counting merrily (the text is treated as 0) whilst the first method will immediately throw #VALUE! and stop calculating properly in any other following cells in the column.