troubleshooting Question

Excel - Calculation Time - Defined Names

Avatar of Alan
AlanFlag for New Zealand asked on
Microsoft Excel
5 Comments1 Solution270 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros