Defined names and calculation speed

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I noticed this statement on ....
http://www.decisionmodels.com/calcsecrets.htmdefined

"....defined Names are recalculated each time a formula that directly refers
to the name is recalculated"

It seems that giving a range a defined name can slow down calculation.
I realise that naming ranges makes it easier to understand formulas, but do
they really slow down calculations?
Rob
 
Hi Rob,

Using defined names makes Excel do extra work, so yes they do slow Excel
down a small amount, but generally you will not notice the effect unless you
have names that contain very time-expensive formulae and you have a lot of
references to them.

There is a sample problem, FXLSample.zip, on my downloads page that
demonstrates the effect: optimising the sample workbook but still including
dynamic range names the calculation time was reduced from 42 seconds to 0.9
seconds. Removing the names reduced the calculation time from 0.9 seconds to
0.1 seconds.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Your statement, Charles, "but generally you will not notice the effect"
is a little confusing as if the speed went from .9 down to .1 by removing
names seems to indicate that it calculates 9 times faster. What would have
happened if you had removed the names before optimising?
Rob
 
Back
Top