Formula recalculation speed
 

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

Formula recalculation speed
 

"Steve Slack" wrote in message
om...
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

Formula recalculation speed
 

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.





Steve Slack wrote:

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

--

Dave Peterson

Formula recalculation speed
 

Very useful, thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!