Formula recalculation speed

[Steve Slack]

I have a really large spreadsheet with loads of formulas (lookups and
vlookups plus mathematical functions) But it takes forever to
recalculate cells whenever I cut and paste or change the contents of
any cell within the workbook...
Are there any formulas that are particularly slow and if so, are there
better ways to perform the same task?
Thanks

 

[MOSHE]

You could try turning off automatic calculation
Tools/Options/Calculation
Set Calculate option to "Manual" or "Automatic except Tables"
and then press F9 to force calculations when needed.

I hate using this option , but sometimes there's no choice.

Good Luck
Moshe

 

[Paul]

I have a really large spreadsheet with loads of formulas (lookups and
vlookups plus mathematical functions) But it takes forever to
recalculate cells whenever I cut and paste or change the contents of
any cell within the workbook...
Are there any formulas that are particularly slow and if so, are there
better ways to perform the same task?
Thanks

There is some good information here that may help you:
http://www.decisionmodels.com/optspeedd.htm

 

[Dave Peterson]

The workbooks that I've seen that are slow recalculating usually have tons of
=vlookups() in them.

In fact, a lot of these have =vlookup() in consecutive columns. The only
difference between the formulas is the column that's returned.

=if(iserror(vlookup(...)),"Missing",if(vlookup(...)="","",vlookup(...)))

And this type of formula could evaluate up to 3 times.

If you have a few columns like this and lots of rows that contain them, you
might want to dedicate a column to look for the match (say column J):

=match(a1,sheet2!a:a,0)

Would return either a number if a match was found or an error (#n/a) if no
match.


Then if column k were to return the value in column B, you could do:

=if(iserror(j1),"missing",index(sheet2!b:b,j1))

And drag down. (You could add that other check if you wanted.)

So you're looking for the match only once per row. And if it's not found, you
don't even go looking for more.