Is there any way to directly compare for calculation efficiency side by side two different formulas for achieving the same goal in excel?

Say I want to know which is more processor intensive (or if there's no difference) between, say,:

=countif(a1,b1) vs. =a1=b1


=vlookup(a1,B:C,2,0) vs. =index(C:C,match(A1,B:B,0)

how would I go about establishing this? Other than just copying the formula down 20 000 rows, and setting a stopwatch as I hit f9.

  • Set a VBA timer and execute them via VBA – Raystafarian Aug 19 '15 at 18:52

Something like this should work:

Public Function speedtest()
t = Now()
For i = 1 To 200000
    'c = Application.WorksheetFunction.CountIf(ActiveSheet.Range("A1"), ActiveSheet.Range("B1"))
    c = (ActiveSheet.Range("A1") = ActiveSheet.Range("B1"))
Next i
t1 = Now()
dt = Format(t1 - t, "ss")
ms = MsgBox(dt & " seconds", vbOKOnly)
End Function

The formula to be tested is inside the for loop. One is commented using ', so you can comment and un-comment to test one or the other

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.