Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Slow Calculation speeds in big spreadsheet
I have a very large 22.4 spreadsheet which I use to manage risk in my trading
portfolio. It has become very slow - between 15 to 30 seconds to complete calculation of any change in input variable. It is 200 columns wide and 3,000 rows deep. I have tried to eliminate formulae which I believe need more time to compute: e.g COUNTIF functions. Can anyone let me know which the worst offenders are of the following functions: Conditional Formatting for colour coding (I know coloured cells use memory, but how bad are they?) Big nested IF functions - most containing the other functions listed here e.g IF(AVERAGE( OFFSET(X,-MIN(COUNTIF(),MIN(COUNTIF(),0,0)))) MIN() MAX() AVERAGE()etc OFFSET() RANK() SLOPE() MATCH() INDEX() SUMIF() I have tried to limit the number of cross sheet references to only one: a chart that plots two series of 3,000 values. I set the workbook Manual calculation to speed things up while I am working on the program. I have a number of simplistic macros which are neither elegant nor properly defined by "Dim" because I don't know how to decide which Dim to use. But the macros do disable screen-updating when running. I got rid of the special functions (I don't know what they are called) with curly brackets on either end which require hitting CTRL ALT Enter (or some such) when entering in the belief they were responsible for slowness. And finally, when I have two consecutive versions of the workbook open at the same time, I often get the error message that I have run out of memory. I use Windows XP and the computer is 2.08 GHz, 512 MB RAM. When I change a variable the Performance tab in Windows Task Manager shows CPU usage at 100%. Is there a way I can allocate more memory to my Excel workbook? I would very much appreciate any tips, help or assistance, and thank any kind soul in advance. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct formulas & slow response | Excel Worksheet Functions | |||
Spreadsheet Dilemma | Excel Discussion (Misc queries) | |||
Scan and copy cells from one spreadsheet to another. | Excel Discussion (Misc queries) | |||
Some exported records do not show on spreadsheet | Excel Worksheet Functions | |||
#REF error on linked spreadsheet | Links and Linking in Excel |