1
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:
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 fromA1:A(n)
?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.