Workbook takes a long time to calculate

[Jacko]

Basically my question is whether Excel has any limits on the number of
formula it can perform before it starts to perform really badly? Here
is the background:

I have a spreadhseet that takes nearly an hour when I try to modify a
whole column due to the number of formula I have in there. I think it
is due to the sheer number of calculations that Excel has to do rather
than the CPU and RAM in my PC. Here are some of the basic specs of the
PCs I have tried the spreadsheet on (All running XP Pro):
- Dell Optiplex 260 with 512MB RAM
- Optiplex 745 with 2.3GHZ processor and 2GB RAM
- Dell Latitude D630 laptop with 2.3Ghz processor and 2GB RAM,
- Dell Precision desktop with 4GB RAM and a decent processor.

I have tried Excel 2002 on all PCs/laptops and MS Excel 2003 on the
Optiplex 745. I don't understand why it takes so long to perform these
calculations if my PC has a decent spec. When I move a columns from
one place to another column Excel just sits there saying calculating
cells 0% for a long time. Looks like it is recalculating all the
formulas.


Here are the characteristics for the spreadsheet:
- Size is 30MB total
- 2 Worksheets - 1 mainly for raw data and the other for calaculations
that reference the 1st spreadsheet

The 1st spreadsheet has the following:
- 40,000 rows
- The 1st 7 columns (A-G) contain text
- The next 12 columns (H-S) contain numbers with decimal places with
column after that (T) being a formula, which is the sum of the
previous 12 columns.
- The next 12 columns (U-AF) contain text
- The next 12 columns (AG-AU) contain numbers (with some having
decimal places).

The 2nd spreadsheet:
- 20,000 rows
- The 1st 5 columns (A-E) is text
- The next 12 columns have a fourmula for all rows which starts with
=SUMIF(Spreadsheet1!B:B,Spreadsheet2!A:A,Spreadsheet!H:H) for column F
and =SUMIF(Spreadsheet1!B:B,Spreadsheet2!A:A,Spreadsheet!I:I) for
column G and so on.

Any help will be greatly appreciated.

Steve

 

[Pete_UK]

Are you sure the second SUMIF term is Spreadsheet2!A:A ? Don't you
want to reference a single cell in Spreadsheet2 column A, and then sum
column I in Sheet1 if column B in Sheet1 matches it?

Though it is convenient at times to use full-column references, it
will be faster if you refer to Spreadsheet1!B1:B40000 instead of
Spreadsheet1!B:B.

So, perhaps your formula could become:

=SUMIF(Spreadsheet1!$B$1:$B$40000,$A1,Spreadsheet1!H$1:H$40000)

in F1, copied across and down. This will still take a long time to
calculate as you have so many rows.

Hope this helps.

Pete

 

[Jacko]

Thanks Pete, I'll give it a go. I might need to go over 40000 rows as
there maybe more to follow so I'll reference upto 50000. I understand
Excel can take 65000 rows but is there a general rule of thumb for how
many formulas Excel can take or does it depend on the complexity of
the formula as well as the number of formulas in the workbook? If not,
is it just trial and error on finding out when there is just too much
to cope with. It looks like it is definately not a hardware problem
and the spreadsheet is taking Excel to its limits.

 

[Pete_UK]

Just think what is happening in the single SUMIF formula I gave you:

Excel will look at every cell in the range B1:B40000 on Sheet1 to see
if it matches with cell A1 on sheet2, and if it does then it will add
the corresponding value from column H in Sheet1 to a running total -
so that is 40,000 comparisons and 40,000 potential additions for just
one fomula. You say that you have 12 columns of formulae in Sheet2 and
20,000 rows, so those operations for one formula are then carried out
240,000 times, so no wonder that it takes so long. By using a full-
column reference, there are 65536 cells that are examined, so cutting
this down to 40,000 (or 42,000) will cut the time by a third at least.

There are also many ways of achieving a particular result in Excel,
and some formulae will execute more quickly than others. Array
formulae in particular will take a long time to calculate with large
arrays. It might be that your sheet could be designed in a different
way and thus speed things up. Charles William's site has some very
useful comments on optimising calculation speed in Excel:

http://www.decisionmodels.com/

Hope this helps.

Pete

 

[Jacko]

Thanks for your thoughts on this. I can see how a formula can spiral
well out of control from adding more and more rows. I'll have a big
rethink how it is put together.

 

[Pete_UK]

Glad to be of help, Steve.

Pete