Improving Calculation Performance in Excel

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.

 http://algconsultings.wordpress.com/2011/01/07/improving-calculation-performance-in-excel/

Andrew Chan.

ALG - Better and Faster Decisions

http://www.algconsultings.com

Comments
chanpangchi's picture

Part 2

chanpangchi | | Permalink

 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.

http://algconsultings.wordpress.com/2011/01/08/improving-calculation-performance-in-excelpart-2/

-- Regards, 

Andrew Chan.

chanpangchi's picture

Improving Calculation Performance in Excel–Part 3

chanpangchi | | Permalink

Who doesn’t use array in Excel VBA can skip this article.  But I guess most of us use array in VBA but not all of us understand that different array manipulation methods can impact the calculation performance.  Below is another dump calculation that tried to sum the total for each row in 3 different approaches.  The first one is my favorite, short and simple and I would try to avoid the last one because it would take longer time to type the code.

http://algconsultings.wordpress.com/2011/01/09/improving-calculation-performance-in-excelpart-3/

-- Regards,

Andrew Chan.

Resources

paulwakefield1 | | Permalink

Interesting articles - thank you.

I feel Decision Models FastExcel is well worth a mention at this point (usual disclaimers: no connection, etc. etc.). http://www.decisionmodels.com/index.htm  My only criticism is that the website is difficult to navigate.

Not only a very useful product for identifying calculation bottlenecks but it also comes with an excellent pdf booklet on "Optimising Excel Calculations and Memory". (There are also a number of other functions and features including Name manager which I have found particularly useful for Xl2003 and earlier).

chanpangchi's picture

Great suggestion!

chanpangchi | | Permalink

Hi Paul,

I would be discussing their RangeTimer today.

-- Regards,

Andrew Chan.

chanpangchi's picture

Improving Calculation Performance in Excel–Final

chanpangchi | | Permalink

There would be no final for performance tuning, as we always want faster models and I have enough tips to cover up to part 99.  But let’s stop here and I wish we learn the lesson that performance should be considered in the design on every Excel model.  Performance does not come by accident; we have to carefully craft it.  No one want to spend more than 10 seconds for the results and don’t even mention hours or days.  We don’t always need a faster machine and sometime even a faster machine won’t help a lot.  There are many useful resources that we can Google about and we would find most of them involve common sense.  We don’t have to have a PhD in computer science to understand the concepts; mainly remove unnecessary calculation and use a faster method if available.

http://algconsultings.wordpress.com/2011/01/10/improving-calculation-performance-in-excelfinal/

-- Regards,

Andrew Chan.

chan_a@algconsultings.com

http://ca.linkedin.com/in/alginc

http://www.algconsultings.com/

Add comment
Log in or register to post comments
Group: ExcelZone
A gathering place for the Excel community to explore new ideas and techniques and a forum to debate product features and best practices.