Named Ranges & Calculation Times Query
 

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

Named Ranges & Calculation Times Query
 

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.

--
__________________________________
HTH

Bob

"Mathew P Bennett" wrote in message
...
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

Named Ranges & Calculation Times Query
 

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" wrote in message
...
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.

--
__________________________________
HTH

Bob

"Mathew P Bennett" wrote in message
...
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

Named Ranges & Calculation Times Query
 

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.

--
__________________________________
HTH

Bob

"Mathew P Bennett" wrote in message
...
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" wrote in message
...
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.

--
__________________________________
HTH

Bob

"Mathew P Bennett" wrote in message
...
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

Named Ranges & Calculation Times Query
 

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/

--
Biff
Microsoft Excel MVP


"Mathew P Bennett" wrote in message
...
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" wrote in message
...
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.

--
__________________________________
HTH

Bob

"Mathew P Bennett" wrote in message
...
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

Named Ranges & Calculation Times Query
 

Thanks Guys, food for thought. Cheers Biff for the link. Have looked. Seems
encouraging and interesting, Cheers again, Mathew
"T. Valko" wrote in message
...
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/

--
Biff
Microsoft Excel MVP


"Mathew P Bennett" wrote in message
...
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" wrote in message
...
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.

--
__________________________________
HTH

Bob

"Mathew P Bennett" wrote in message
...
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