Improving Calculation Performance in Excel–part 1
January 7, 2011 3 Comments
We can build sophisticated and CPU intensive models in Excel and some of them can take hours or even days to complete. We can speed up the model by upgrading our computer, overclocking our CPU or optimizing our models. Some of the optimization methods can be very simple and surprisingly effectively. Let me use an example to illustrate.
The following VBA routine runs some dump calculation for 100,000,000 times.
Option Explicit
Public Sub Main()
Dim startTime As Date
Dim endTime As Date
Dim run As Long
Dim runs As Long
Dim AnnIntRate As Double
Dim Results As Double
runs = 100000000
AnnIntRate = 0.05
startTime = Now
For run = 1 To runs
Results = run * (AnnIntRate ^ (1 / 365) )
Next
endTime = Now
MsgBox (startTime & endTime)
End Sub
It took 34 seconds to complete.
I could optimize the code and reduced the processing time to 8 seconds, i.e. roughly over 400% improvement. How?
Option Explicit
Public Sub Main()
Dim startTime As Date
Dim endTime As Date
Dim run As Long
Dim runs As Long
Dim AnnIntRate As Double
Dim DailyIntRate As Double
Dim Results As Double
runs = 100000000
AnnIntRate = 0.05
startTime = Now
DailyIntRate = AnnIntRate ^ (1 / 365)
For run = 1 To runs
Results = run * DailyIntRate
Next
endTime = Now
MsgBox (startTime & endTime)
End Sub
Power ^ and Division / require more CPU cycles than Multiply * . Since AnnualRate ^ (1 / 365) is a constant within the for loop, we can replace the Power and Division with a double variable DailyIntRate and a Multiply.
There are many ways that we can optimize our Excel models; why do we want to wait for hours if we can get it done within minutes!
My computer has a Intel Q9400 CPU inside and I am using Excel 2010 64 bit. You may find difference results on your computer because CPU can have much different architecture.
p.s. Thank squiggler to point out that my original post has a bug in it that didn’t return the same results.
- Improving Calculation Performance in Excel–part 1
- Improving Calculation Performance in Excel–Part 2
- Improving Calculation Performance in Excel–Part 3
- Improving Calculation Performance in Excel–Final
Andrew Chan is the owner and founder of ALG Inc.
We help you to make better and faster decisions!
About Andrew Chan
Go Beyond Excel 
With Excel reliant on FPU, there is hardly any difference between Divide and multiply :-
Public Sub Main()
Dim startTime As Double
Dim endTime As Double
Dim startTime1 As Double
Dim endTime1 As Double
Dim run As Long
Dim runs As Long
Dim Results As Double
runs = 1000000
startTime = Timer
For run = 1 To runs
Results = run ^ (1 / 365)
Next
endTime = Timer
startTime1 = Timer
For run = 1 To runs
Results = run ^ (1 * 2.73972602739726E-03)
Next
endTime1 = Timer
Debug.Print ((endTime – startTime))
Debug.Print ((endTime1 – startTime1))
End Sub
I used 1* to make it fair and 1/365 pre-calculated, most of the speed saving in the example above is by removing the ^ from the loop, and since 1^(1/365) is always 1 both examples calculate different results, therefore its not really an optimization!
startTime3 = Timer
DailyRate = 1/365
For run = 1 To runs
Results = run ^ (DailyRate)
Next
endTime3 = Timer
Would be an optimization of that routine, but not much of a saving!
Debug.Print ((endTime2 – startTime2))
Squiggler,
Thank you for finding the bug for me. You are right that it didn’t return the same results. Now I fixed it. But the point I want to make is still valid that we can improve Excel calculation performance with some simple but effective was.
I would do another blog to compare Division and Multiply but I believe you are right that we would not see as much improve as ^.
Thanks again.
Your sincerely,
Andrew Chan.
OK, Andrew, your creativity & wisdom has earned you my recommendation to all my business friends & clients for the coaching help most of us need to release the REAL power in Microsoft ‘Excel’ and Open Office ‘Spreadsheet’.