Excel - Calculation Time - Defined Names
Hi All,
I am trying to improve the calculation time on a workbook that has been around for a long time.
I am doing this 'incrementally' by looking at various sheets and have come across one that is taking longer than I would expect.
I have distilled the issue down and have attached a sample workbook where I have recreated the situation.
Essentially, it boils down to a defined name that is used to determine the 'End of File' of a data range, and is then used in various places to create dynamic ranges for other formulae.
I have designed the attached workbook to take a non-trivial amount of time to calculate (I'm not looking for a discussion on the merits of SUMIF vs SUMPRODUCT vs Arrays etc).
I have included a very simple timer in a module to assist.
On my machine, if I run the FullCalc() sub as is, it takes about 36 seconds (this is not the fasted machine, but it doesn't matter for this purpose).
If I then change the defined name:
Bottom=MATCH("EoF",Sheet1!
to be:
Bottom = 10001
When I re-run the calc timer, I get about 13 seconds.
So it takes between 2.5 and 3 times longer with the formula in the defined name.
My expectation was that it would be almost identical, since Excel only needs to evaluate 'Bottom' once (and gets the value '10001') so the times would be very similar.
However, clearly that is not the case.
As far as I know, MATCH is not a volatile function, so that shoudn't be a factor?
So, why does it take so much longer with the formula in the defined name?
Any comments or thoughts appreciated.
Thanks,
Alan.
CalcTimer.xls
I am trying to improve the calculation time on a workbook that has been around for a long time.
I am doing this 'incrementally' by looking at various sheets and have come across one that is taking longer than I would expect.
I have distilled the issue down and have attached a sample workbook where I have recreated the situation.
Essentially, it boils down to a defined name that is used to determine the 'End of File' of a data range, and is then used in various places to create dynamic ranges for other formulae.
I have designed the attached workbook to take a non-trivial amount of time to calculate (I'm not looking for a discussion on the merits of SUMIF vs SUMPRODUCT vs Arrays etc).
I have included a very simple timer in a module to assist.
On my machine, if I run the FullCalc() sub as is, it takes about 36 seconds (this is not the fasted machine, but it doesn't matter for this purpose).
If I then change the defined name:
Bottom=MATCH("EoF",Sheet1!
to be:
Bottom = 10001
When I re-run the calc timer, I get about 13 seconds.
So it takes between 2.5 and 3 times longer with the formula in the defined name.
My expectation was that it would be almost identical, since Excel only needs to evaluate 'Bottom' once (and gets the value '10001') so the times would be very similar.
However, clearly that is not the case.
As far as I know, MATCH is not a volatile function, so that shoudn't be a factor?
So, why does it take so much longer with the formula in the defined name?
Any comments or thoughts appreciated.
Thanks,
Alan.
CalcTimer.xls
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Yes, I would think so. You would still have a level of separation but the MATCH would only be calculated when needed (once) per calculation cycle.
Kevin
Kevin
ASKER
Okay - I guess the only way to be sure is to try it and see.
I'll fork the workbook, and see if it works, and I'll post back here.
Thanks,
Alan.
I'll fork the workbook, and see if it works, and I'll post back here.
Thanks,
Alan.
ASKER
I just tried it on the one defined name I believe to be the worst offender, and it improved performance from 85 seconds to 58 seconds. I'm off to hunt for others!
Thanks,
Alan.
Thanks,
Alan.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
If I moved those calcs into worksheet cells, and then kept the defined names, but just referred them directly to those cells, would I still get the same performance (or very close)?
I ask because the actual workbook has a lot of defined names, and they are used throughout the workbook in a very structured way, which is quite effective in 'self-documenting' many of the calculations (worked for me to follow it along at least!)
Thanks,
Alan.