Named Ranges & Calculation Times Query

[Mathew P Bennett]

Good Evening Guys, Excel 2007
I have a query about Named Ranges and the associated calculation speeds.
I have a wb which uses a master sheet which runs to over 35,000 rows & only
15 Columns. I run another ss which has 5 vlookup columns referencing to the
master sheet.
Now I have been trying to use Named Ranges to make the vlookup's easier.(and
faster)
I have saved the same wb as three seperate workbooks, and have found the
following:

WB1: Named Ranges using offset & counta, file size
6.73mb Calc time >5 mins
WB2: Named Ranges using index & counta, file size
6.87mb Calc time 13 secs
WB3: No Named Ranges - Just Absolute Cell Referencing, file size 8.08mb
Calc time 04 secs

Is someone able to explain this, as I was under the mpession that it was
always advisable to use named ranges.
Thank you in advance.
Mathew

 

[Bob Phillips]

It is advisable from a maintenance aspect, but not from a performance
aspect. Whereas Excel will only evaluate formulae in cells that have
changed, or have precedents that have changed, formulae in names are
evaluated every time a calculation kicks in. Hence a performance hit.

 

[Mathew P Bennett]

Thanks Bob for you input again.
So what do you recommend considering the number of rows?
My only concern is that I would like the ranges to update to the final row
in master, automatically, as this data comes from a refreshable database
msquery.
Cheers Bob,
Mathew

 

[Bob Phillips]

The solutions are usually muti-fold. Helper cells used to hold common
calculations often help, and sometimes it is by way of UDFs. It all depends
upon the spreadsheet, the data, and the formulae being used.

 

[T. Valko]

Also keep in mind that depending on the complexity of your file *it is not
unresaonable* for caclculation to take some amount of time!

See this for some ideas:

http://www.decisionmodels.com/

 

[Mathew P Bennett]

Thanks Guys, food for thought. Cheers Biff for the link. Have looked. Seems
encouraging and interesting, Cheers again, Mathew