M
MichaelC
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.
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.