Improving Calculation Performance in Excel–Part 2

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?

image

Here is the formula in column C.

image

Let me add another column which would return identical results.

image

What formula is in column D?

image

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.

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!

3 Responses to Improving Calculation Performance in Excel–Part 2

  1. Pingback: Improving Calculation Performance in Excel–Final « Technologies and your business

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: