CODEWITHSUNDEEP
performanceexcelexcel-formulaprocessing-efficiencyarray-formulas
user2140261
user2140261

Reputation: 8003

How to measure Excel Calculation times

For example how could I find what formula is faster? 

=SUMPRODUCT((Data!D:D="RC Corp")*(Data!AD:AD="Expected Allowances / Provisions"))

Vs

=COUNTIFS(Data!D:D,"RC Corp",Data!AD:AD,"Expected Allowances / Provisions")

Vs Concatinating the 2 fields into a new Column and doing

 Z1 = D1&AD1
=Countif(Data!Z:Z,"RC CorpExpected Allowances / Provisions")

Vs VBA

Dim i as integer
Dim Total as integer
Total = 0 
i=0
While i < 1000 
    IF Range("D"&i).Value = "RC Corp" AND Range("AD"&i).Value = "Expected Allowances / Provisions" Then
Total = Total + 1
End If 
Wend
Range("$A$1").Value = Total

Upvotes: 1

Views: 9501

Answers (2)

user2140261
user2140261

Reputation: 8003

Woking from evocandy's answer I came up with this basic code.

time1 = Timer
Range("A1").Calculate ' Or the cell containing the Formula I want. OR use Sheets("Sheet1").Calculate for the calculation including concated columns
time2 = Timer

CalculationTime = Time2-Time1

In order for this to work i had to Isolate sample data to new empty worksheets and disable the Automatic Refresh in Excel as to make sure it doesn't time any other Calculations.

Upvotes: 4

evoandy
evoandy

Reputation: 660

In VBA you can user Timer.

Put 

time1 = Timer
'Your code
time2 = timer

totaltime = time2-time1

Upvotes: 3

Related Questions