| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very large spreadsheet
I use a large spreadsheet with over 60,000 rows and 30 columns with formulas
such as Vlookup to track and update inventory for my business. The spreadsheet is taking a long time to calculate, eventually it will complete the task but it just seems to take an exceedingly amount of time every time I make a change. I have 2GB memory and a P4 processor 4.3 Gh or so. What is the correct hardware to use with such big spreadsheets? should I get a workstation with a bunch of memory and dual core processor? Would that solve the issue or will the spreadsheet still be sticky ? -- DJ |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Very large spreadsheet
Unless you are using XL 2007, a dual core processor will not affect
performance. Likely you have a lot of volatile functions that are recalculated every time you do something in XL. If you want some hints as to how to optimize performance, see this white paper: http://msdn2.microsoft.com/en-us/library/aa730921.aspx If you don't want to take the time to read through it, then post back with examples of your most common formulas; perhaps someone can suggest more efficient formulas that accomplish the same thing. Dave -- Brevity is the soul of wit. "DJ" wrote: I use a large spreadsheet with over 60,000 rows and 30 columns with formulas such as Vlookup to track and update inventory for my business. The spreadsheet is taking a long time to calculate, eventually it will complete the task but it just seems to take an exceedingly amount of time every time I make a change. I have 2GB memory and a P4 processor 4.3 Gh or so. What is the correct hardware to use with such big spreadsheets? should I get a workstation with a bunch of memory and dual core processor? Would that solve the issue or will the spreadsheet still be sticky ? -- DJ |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Very large spreadsheet
Check out this link.
http://www.decisionmodels.com/index.htm Throwing hardware at it will not help. Depending you your version of XL the memeory that XL can address is limited. From your description it sounds like your application might be better suited to a database than a spread sheet. Note since you are over 65,536 formulas smart calc will be disabled for this workbook and all of the funcitons will act as if they are volatile and will recalc with every calculation... Nothing you can do about that. -- HTH... Jim Thomlinson "DJ" wrote: I use a large spreadsheet with over 60,000 rows and 30 columns with formulas such as Vlookup to track and update inventory for my business. The spreadsheet is taking a long time to calculate, eventually it will complete the task but it just seems to take an exceedingly amount of time every time I make a change. I have 2GB memory and a P4 processor 4.3 Gh or so. What is the correct hardware to use with such big spreadsheets? should I get a workstation with a bunch of memory and dual core processor? Would that solve the issue or will the spreadsheet still be sticky ? -- DJ |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Very large spreadsheet
Based on the description ALL of the functions will end up being vlaitile. As
soon as a spreadsheet has more than 65,536 dependancies Smart Calc no longer functions and all of the functions become essentially volatile... -- HTH... Jim Thomlinson "Dave F" wrote: Unless you are using XL 2007, a dual core processor will not affect performance. Likely you have a lot of volatile functions that are recalculated every time you do something in XL. If you want some hints as to how to optimize performance, see this white paper: http://msdn2.microsoft.com/en-us/library/aa730921.aspx If you don't want to take the time to read through it, then post back with examples of your most common formulas; perhaps someone can suggest more efficient formulas that accomplish the same thing. Dave -- Brevity is the soul of wit. "DJ" wrote: I use a large spreadsheet with over 60,000 rows and 30 columns with formulas such as Vlookup to track and update inventory for my business. The spreadsheet is taking a long time to calculate, eventually it will complete the task but it just seems to take an exceedingly amount of time every time I make a change. I have 2GB memory and a P4 processor 4.3 Gh or so. What is the correct hardware to use with such big spreadsheets? should I get a workstation with a bunch of memory and dual core processor? Would that solve the issue or will the spreadsheet still be sticky ? -- DJ |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Very large spreadsheet
Excellent point.
-- Brevity is the soul of wit. "Jim Thomlinson" wrote: Based on the description ALL of the functions will end up being vlaitile. As soon as a spreadsheet has more than 65,536 dependancies Smart Calc no longer functions and all of the functions become essentially volatile... -- HTH... Jim Thomlinson "Dave F" wrote: Unless you are using XL 2007, a dual core processor will not affect performance. Likely you have a lot of volatile functions that are recalculated every time you do something in XL. If you want some hints as to how to optimize performance, see this white paper: http://msdn2.microsoft.com/en-us/library/aa730921.aspx If you don't want to take the time to read through it, then post back with examples of your most common formulas; perhaps someone can suggest more efficient formulas that accomplish the same thing. Dave -- Brevity is the soul of wit. "DJ" wrote: I use a large spreadsheet with over 60,000 rows and 30 columns with formulas such as Vlookup to track and update inventory for my business. The spreadsheet is taking a long time to calculate, eventually it will complete the task but it just seems to take an exceedingly amount of time every time I make a change. I have 2GB memory and a P4 processor 4.3 Gh or so. What is the correct hardware to use with such big spreadsheets? should I get a workstation with a bunch of memory and dual core processor? Would that solve the issue or will the spreadsheet still be sticky ? -- DJ |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Very large spreadsheet
Is there any historical data that could be converted from dynamic
formulas to static text? For instance, if a particular number is the result of a formula and that number will never change because it occurs in the past, copy that cell and paste it over itself as a value. Additionally, if historical data needs to be a formula but is relevant only to a particular timeframe, you could save that timeframe as a separate file and refer to it as needed, instead of maintaining it in the current file. That would reduce the current files demand for resources. Dave O |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Large spreadsheet - Charts don't refresh | Charts and Charting in Excel | |||
| Working spreadsheet highlighting function for Excel 2007 | Excel Worksheet Functions | |||
| Generating Simple Reports From A Master Spreadsheet | Excel Worksheet Functions | |||
| Spreadsheet Security | Excel Discussion (Misc queries) | |||
| Big problem with large spreadsheet. | Excel Discussion (Misc queries) | |||
Linear Mode
