Calculation speed and workbook size

[Guest]

I have a spreadsheet that takes about 10-15 seconds to calculate on every
entry. It consists of multiple pages that are identical as far as the
formulas go, differing only in the data on each page. The formulas
accomplish auto-sorting among other things, and are intertwined and look to
constants set in other cells on the same page. At this point I don’t know
how to simplify them. The formulas reference only cells on the same page.
When I look at the System Monitor from Control Panel/Administrative
Tools/Performance, the processor is maxed out at 100%, but memory and
physical disk are loafing along at a low %.

Here’s my question. If I divide the workbook in two by making a copy and
deleting half the pages in each of the two versions, calculation speeds up
considerably, to just a couple of seconds.. What’s going on? If the
formulas only reference one page, why does the number of pages affect
calculation speed?

Thanks in advance for any information.

 

[Pete_UK]

Excel sets aside some memory to maintain a dependency table -
basically covering which cells are dependent on others for their
values. Up to 64k dependencies can be catered for, so for smallish
workbooks Excel can recalculate very quickly because it only needs to
calculate cells that are dependent.

On larger workbooks, however, if this table size is exceeded then
Excel needs to do a full recalculation, even if only a few cells
actually need it, so the time can seem excessive.

It might be that with you splitting your workbook you have brought the
dependency table in each to under the limits.

Another approach to speed things up is to set the calculation mode to
Manual and press F9 when you need to recalculate.

Hope this helps.

Pete

 

[Guest]

Just to clear up the dependency table issue and breaking up the file. The
dependency tree is at the XL level and not at the workbook level. By that I
mean XL maintains only one tree. There is not a seperate tree for each
workbook. 64k of dependencies for all open workbboks. So if you do break the
file up (assuming that is the issue) then make sure you only have one book
open at a time. Otherwise there is absolutely no benefit...

 

[David McRitchie]

Excel isn't really going to know that only one page is affected.
=sheet14!A1

Suggest looking over
http://www.mvps.org/dmcritchie/excel/slowresp.htm