Improving Calculation Performance in Excel–Part 2
January 8, 2011 3 Comments
In my last blog, I demonstrated how removed unnecessary calculation can significantly improve VBA performance, i.e. over 400%. I want to show you how similar performance gain could be achieved in Excel worksheet as well.
Below is a very simple worksheet; anyone knows Excel can do it. What is the big deal?
Here is the formula in column C.
Let me add another column which would return identical results.
What formula is in column D?
Which formula would you use? It may not be matter for only 12 cells but if you apply column C and D to 1 million rows, then the difference can be huge. Column C refers to n cells and add n-1 times; where column D only refer to 2 cells and add 1 time for each row. I let you to do the maths to calculate how many cells that Excel needs to refer and add for column C if there is 1 million rows.
Again, it is something simple and effective. When we design our model, we must always have performance in mind! And if you are not sure which one performs faster, then time it.
- 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 
This may help you in deciding which formulas are faster!
http://darreneves.wordpress.com/2011/01/09/timing-a-worksheet/
Hi Darren,
Thank you and I am also planning to discuss briefly about timing in a later blog. Thank you for your information.
Pingback: Improving Calculation Performance in Excel–Final « Technologies and your business