Making a spreadsheet calculate faster

[EricK]

I've "inherited" a very complicated spreadsheet at work, which runs quite
slowly. I'm going to try to speed it up a bit. Here are some things I've
thought of so far, but I'm not sure if they're going to work.

1. In some places there are complicated formulae depending on many input
cells. But if one of those cells is zero then the formula will calculate to
zero as well. Would it speed things up to replace "=formula" with
"=if(a1=0,0,formula). In other words, does the IF function calculate both
parts following the condition (in which case this would just slow it down
further) or does it only calculate the relevant part in which case this might
save a lot of calculation time.

2. Imagine the first 1000 rows column A has various numbers which will
always be integers in the range 1-10 and column B has a complex formula which
depends on the figure in the nieghboring cell in column A and also on other
fixed cells in the spreadsheet. Would it speed up the spreadsheet to have a
small ten row table somewhere in the spreadsheet with the complex forumla
worked out for numbers 1-10 and use and replace the formula in column B with
a VLOOKUP? In other words, how quick is a simple VLOOKUP compared to a
complex arithmetic calculation (eg "=a1*(1+$V$3)^(($g$7-$g$4)/365)")?

Are there any other good tips for speeding up spreadsheets?

Thanks,

Eric

 

[Niek Otten]

Hi Eric,

To learn all about Excel's performance and what to do to improve that; visit Charles Williams' site:

www.decisionmodels.com

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| I've "inherited" a very complicated spreadsheet at work, which runs quite
| slowly. I'm going to try to speed it up a bit. Here are some things I've
| thought of so far, but I'm not sure if they're going to work.
|
| 1. In some places there are complicated formulae depending on many input
| cells. But if one of those cells is zero then the formula will calculate to
| zero as well. Would it speed things up to replace "=formula" with
| "=if(a1=0,0,formula). In other words, does the IF function calculate both
| parts following the condition (in which case this would just slow it down
| further) or does it only calculate the relevant part in which case this might
| save a lot of calculation time.
|
| 2. Imagine the first 1000 rows column A has various numbers which will
| always be integers in the range 1-10 and column B has a complex formula which
| depends on the figure in the nieghboring cell in column A and also on other
| fixed cells in the spreadsheet. Would it speed up the spreadsheet to have a
| small ten row table somewhere in the spreadsheet with the complex forumla
| worked out for numbers 1-10 and use and replace the formula in column B with
| a VLOOKUP? In other words, how quick is a simple VLOOKUP compared to a
| complex arithmetic calculation (eg "=a1*(1+$V$3)^(($g$7-$g$4)/365)")?
|
| Are there any other good tips for speeding up spreadsheets?
|
| Thanks,
|
| Eric

 

[T. Valko]

Question 1: Yes, it would be more efficient. If the conditional test of the
IF function is TRUE it doesn't continue to evaluate the value_if_false. For
example:

=if(a1=0,0,formula)

If A1 = 0 it doesn't continue to calculate "formula".

Question 2: Yes, that will also help. Using a combination of INDEX/MATCH is
more efficient than VLOOKUP.

Visit this site for lots of info on efficiency:

http://www.decisionmodels.com/

 

[Jim Thomlinson]

Check out this link... There is great info on optimizind speed and memory and
calculations.

http://www.decisionmodels.com/index.htm

 

[EricK]

Thanks to everyone.