Array Function recalculate much slower in 2003 vs. 2000

[Terry]

I have a workbook with 40 cells on one sheet containing
array functions that sum over a 15,000 row table in
another sheet, like:

{=sum(if(range1='w',if(range2='x',if(range3='y',if
(range4='z',range5)))))}

Originally developed in Excel 2000, the first time I
opened the workbook in Excel 2003 it took 2 hours to open
using 100% CPU. After the initial opening calculation I
can save the workbook and subsequent openings are not a
problem.

I understand E-2003 calculates the entire workbook the
first time it opens from a previous version, which
explains why it is slow once and returns to normal after
that. However, a few issues remain:

1. After the initial open/save on XP/2003 I have tried
opening this workbook on 2 other XP/Office 2003 machines.
On one it opens fine, on the other the workbook goes
through the 2 hour calculation. How does Excel determine
if a file was created with a different version, and how
would I find what is different on the "slow opening" XP
machine that makes it (apparently) recalculate the
workbook?

2. Every week I update the data in the 15,000 row table,
which should cause the whole workbook to recalculate,
since everything in the book depends on this data. This
update only takes 10 minutes (Excel 2000 or 2003). But if
I force a calculation of the whole workbook (control-alt-
f9) it takes 2 hours in 2003 but only 10 minutes in Excel
2000. Why does 2003 take so much longer to recalculate
than 2000 does?

 

[Charles Williams]

Hi Terry,

I can only guess: but it sounds to me like you have hit the downside of the
global calculation that was introduced in excel 2002. Most of the the time
this change speeds up recalcs, but it can sometimes slow down the initial
determination of the dependency chain sequence.

10 minutes is too long anyway, let alone 2 hours: some speed-up tips ...

If you can sort the 15000 row table then you can almost certainly speed it
up substantially by having the array formulae only operating on a subset of
rows. See the SUMIF example on my website
http://www.DecisionModels.com/optspeedk.htm

alternatively with only 40 cells you may be able to sensibly use DSUM
instead of the array formulae: that would also be a lot faster.

Its also usually possible (although not neccessarily simple/obvious) to
bypass the Excel2002/2003 global calculation problem by resetting the
initial calculation sequence using Range.calculate

How does Excel determine
if a file was created with a different version, and how
would I find what is different on the "slow opening" XP
machine that makes it (apparently) recalculate the
workbook?

It internally saves a value that says which version last calculated the
workbook: so you should be OK if you calculate it using Excel2003 and then
save it: it will then know that it was last recalculated by Excel 2003.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com