# Microsoft Excel cumulative calculation performance

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.

-

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.
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.