Excel - Calculation Time - Defined Names

Alan
Alan used Ask the Experts™
on
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!$A:$A,0)

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
It will definitely not be identical because you have replace a formula with a constant. The reason you are using a formula in a defined name is you have a lot of other formulas that depend on that value. But therein lies the rub: each dependent formula is forcing the defined name formula to be recalculated. The solution is somewhere in the middle. Excel does not know how to integrate a defined name formula into it's dependency tables in such a way that efficiencies are gained when using it more than once. But Excel does know how to integrate into those tables if you put the formula in a cell. So put MATCH("EoF",Sheet1!$A:$A,0) into a cell on a hidden worksheet and then you should see the same performance improvement that you see when you change it to a constant.

Kevin
AlanConsultant

Author

Commented:
Hi Kevin,

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.
Top Expert 2008

Commented:
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
AlanConsultant

Author

Commented:
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.
AlanConsultant

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial