Worksheet speed with array formula

[Guest]

I have recently included an array formula (SUMPRODUCT) in my worksheet. It
works very well, doing what I wanted, but it has significantly slowed the
worksheet down. I have changed the calculation to manual rather than
automatic but it means that I have to remember to recalculate - frustrating.
Is there anyway that I can get around this hugely reduced calculation speed?

 

[Niek Otten]

Impossible to say without knowing your formulas; post them if you want any comments.
But yes, in general, array formulas are slow. No wonder; they do a lot of work!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have recently included an array formula (SUMPRODUCT) in my worksheet. It
| works very well, doing what I wanted, but it has significantly slowed the
| worksheet down. I have changed the calculation to manual rather than
| automatic but it means that I have to remember to recalculate - frustrating.
| Is there anyway that I can get around this hugely reduced calculation speed?

 

[Guest]

You may want to look at this paper:
http://msdn2.microsoft.com/en-us/library/aa730921.aspx

It discusses optimization for XL workbooks. It addresses issues with Xl
2007, however, many of the discussions are also relevant to earlier versions
of XL.

Dave