| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Query and expanding named ranges | Excel Discussion (Misc queries) | |||
| Importing Excel named ranges using MS Query | Excel Discussion (Misc queries) | |||
| Calculation of named cell ranges | Excel Discussion (Misc queries) | |||
| Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
| Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Linear Mode
