Improving Calculation Performance in Excel–Final

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.

How can we determine what method is faster?  We can use timer in Excel VBA but what about Excel functions?   There is a very useful set of macros that we can find in a Microsoft MSDN article, Excel 2010 Performance: Improving Calculation Performance written by Charles Williams, Decision Models Limited.  This article also discuss many great tips about performance tuning in Excel and you may want to visit Decision Models’ website because Charles has a wonderful profiling tools, FastExcel, to find and measure timing and memory bottlenecks in our workbook.

Let’s go back to the example in Improving Calculation Performance in Excel–Part 2 and illustrate how this macros can help us.

  • Select the range that we like to measure the performance, i.e. C2..C13

image

  • Press Alt+F8
  • Select RangeTime and click Run button

image

  • Now, we can see how long Excel took to perform the calculation.

image

  • And let’s repeat it on Col D

image

Only 12% gain?  Not a lot!  Well, it is because we only sums up 12 records.  Let’s see the performance gain when we have 1000 records.  It is 2.4 times faster!

image

image

What if we push further and review 5,000 records, then you can see the HUGE difference, i.e. 12 times faster!

image

image

How about the maximum limit of Excel?  1,048575 records.  Col D returned the results in less than 2 seconds.  How about Col C?  I went out to do 30 minutes jogging, came back to have a quick shower and breakfast… Excel is still very busy doing calculation, so I stopped it after an hour.

image

Col C is not scalable nor sustainable.  Initially, it may work and give us the correct results but as we have more data, the processing time would increase exponentially.  The fix (Col D) is very simple and effective that any Excel user can understand.

There may be time that we have to involve an IT consultant who can optimize our Excel model.  But if we do our homework then we may find ways to significant improve the performance of our Excel models.

Wish you all the success in performance tuning.  But if you need any help then please don’t hesitate to contact me at 416-804-6878 or chan_a@algconsultings.com

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

 

 

 

 

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: