Speeding Up Array Formulas

Question

Hi,

I have a workbook which is about 6mb - but it contains lots of array formulas, dynamic named ranges, dynamic charts and drop down boxes which allow users to interact with the data.

Everything in it works - but it is incredibly slow!  I have added macros so that when drop down selection are made it only recalculates the parts of the file it needs to, to refresh the data (rather than it all recalculating) - but it is still really slow and sometime it just hangs and doesnt refresh until I hit escape.

I know it is difficult to judge without seeing the workbook (but I wouldnt be able to share it due to the data in contains), but are their any principles that I could apply to the model which might help me speed it up?

Any suggestions would be greatfully received.

Thanks.

Elizabeth.

Answer 1

{=SUM(IF('C-By Organisation by Test'!$B$1:$B$60000=O3,IF('C-By Organisation by Test'!$D$1:$D$60000=R3,IF('C-By Organisation by Test'!$AB$1:$AB$60000=S3,'C-By Organisation by Test'!$F$1:$F$60000,0),0)))}

As mentioned, if you do not have to provide for Excel 97-2003 compatibility, converting that formula to SUMIFS() will greatly decrease the processing time. Something like,

=SUMIFS('C-By Organisation by Test'!$F$1:$F$60000,'C-By Organisation by Test'!$AB$1:$AB$60000,S3,'C-By Organisation by Test'!$D$1:$D$60000,R3,'C-By Organisation by Test'!$B$1:$B$60000,O3)

I ran some speed tests by re-arranging the high probability matches and the low probability matches in your SUM(IF(IF(IF()))) array formula and compared to an equivalent  SUMPRODUCT(), and while the SUMPRODUCT() was marginally quicker on a regular basis, there was not enough of an appreciable difference to warrant reformulating an entire worksheet.

Of course, reducing the rows 1:60000 as much as reasonably possible would also help.

SUMIFS function

Answer 2

Liz wrote:

An example of one of the array formulas that I use is:

{=SUM(IF('C-By Organisation by Test'!$B$1:$B$60000=O3,IF('C-By Organisation by Test'!$D$1:$D$60000=R3,IF('C-By Organisation by Test'!$AB$1:$AB$60000=S3,'C-By Organisation by Test'!$F$1:$F$60000,0),0)))}

[....]

Maybe there is a different way of doing these formulas....but i have always just used nested ifs.

You might have done that in XL2003.  But even in XL2003, you can avoid the array-entered formula by using SUMPRODUCT.  For example:

=SUMPRODUCT(('C-By Organisation by Test'!$B$1:$B$60000=O3)

*('C-By Organisation by Test'!$D$1:$D$60000=R3)

*('C-By Organisation by Test'!$AB$1:$AB$60000=S3),

'C-By Organisation by Test'!$F$1:$F$60000)

It is unclear whether that is more, less or equally efficient.  But since array-entered formulas are error-prone (you might press just Enter and get some GIGO result), I try to avoid them.

However, if XL2003 compatibility is not an issue, you can use SUMIFS in XL2007 and later.  For example:

=SUMIFS('C-By Organisation by Test'!$F$1:$F$60000,

'C-By Organisation by Test'!$B$1:$B$60000, O3,

'C-By Organisation by Test'!$D$1:$D$60000, R3,

'C-By Organisation by Test'!$AB$1:$AB$60000, S3)

That might be more efficient, especially if you have less than 60,000 rows of numeric data in column F.

Answer 3

Elizabeth wrote:

I have a workbook which is about 6mb - but it contains lots of array formulas, dynamic named ranges, dynamic charts and drop down boxes which allow users to interact with the data.  Everything in it works - but it is incredibly slow!

[....]

I know it is difficult to judge without seeing the workbook (but I wouldnt be able to share it due to the data in contains), but are their any principles that I could apply to the model which might help me speed it up?

As you say, it is "difficult" (nearly impossible) to offer concrete suggestions without seeing workbook.

But a very likely source of problems is the increasingly common, but ill-advised use of whole-column ranges of the form A:A in array expressions; for example, COUNTA(Sheet1!$A:$A).

In XL2007 and later, that results in processing 1M cells; and it might result in creating temporary arrays of 1M entries, e.g. SUMPRODUCT(--(A:A<>0)).

Generally, it is better to use a reasonably restricted range, e.g. A2:A10000.

Note:  But INDEX(A:A,...) is okay because the entire column range is not actually accessed.

Answer 4

If you have not read this site it gives a lot of good info on speeding things up.

http://www.decisionmodels.com/index.htm

With respect to dynamic named ranges there are 2 ways to do them. One uses offset which is volatile the other uses index which is not.

With a variable list in A2:A?? and nothing in A1 these to formulas are equivalent for a dynamic named range

• =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
• =Sheet1!$A$2:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A)+1)

The second formula is not volatile.

Answer 5

... but are their any principles that I could apply to the model which might help me speed it up?

Perhaps you can provide examples of the more often used array formulas in the hope of getting a more efficient model?