Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula recalculation speed
Very useful, thanks *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 formula recalculation | Excel Discussion (Misc queries) | |||
Improving Recalculation Speed for Complex Links | Excel Discussion (Misc queries) | |||
Formula Recalculation Error | Excel Worksheet Functions | |||
Recalculation of Formula. | Excel Discussion (Misc queries) | |||
Formula Recalculation after using AutoFilter | Excel Worksheet Functions |