We help IT Professionals succeed at work.
Get Started

Excel - Calculation Time - Defined Names

Alan
Alan asked
on
268 Views
Last Modified: 2012-05-11
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
CERTIFIED EXPERT
Top Expert 2008
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE