CODEWITHSUNDEEP
performanceexcel-formulaexcel-2010
Stan
Stan

Reputation: 38255

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.

Upvotes: 0

Views: 1039

Answers (1)

MikeD
MikeD

Reputation: 8941

number sequence 9, 8, 7, 6, -9, -8, -7, -6; workbook set to manual calculation, triggered by following code:

Sub ManualCalc()
Dim R As Range

    Set R = Selection
    [F1] = Now()
    R.Worksheet.Calculate
    [F2] = Now()
    [F3] = ([F2] - [F1]) * 86400

End Sub

At 4096 rows calculation time is not measurable for both variants (0 seconds), at 65536 rows your 1st method is still not measurable, your 2nd method takes a bit less than 8 seconds on my laptop (Dell Latitude E6420, Win7, Office2010 - average of 3 measurements each). So for high number of rows I would therefore prefer method 1.

Regarding your Q1 ... yes it would add 100k sums of ever growing ranges ... Excel is not supposed to be smart, it's supposed to calculate whatever you ask it to calculate. If it did, it would interpret the intention of a set of formulas at runtime which I'd regard as very dangerous!

Upvotes: 1

Related Questions