Single-cell Array Formula Speed Comparison

[jmwbowen]

Good evening everyone,

I have a workbook that uses a lot of array formulas (no way around this), and, after adding dynamic array formula worksheet hyperlinks to the mix, the workbook slowed down enough for me to notice the delay in calculation on my Mac Mini (quad-core i7, SSD, and 16Gb RAM). Being that the end user of this workbook uses a much slower computer, I have to speed the formulas up.

Looking at one set of array formulas, I noticed that I had inadvertently used two different array formulas to accomplish the same thing:

Formula #1:

{=IFERROR(INDEX(COLUMN_Name,SMALL(IF((COLUMN_Position="EMT")*(COLUMN_TeamAssigned=M4)*(RIGHT(COLUMN_BOGStatus,1)="1"),ROW(COLUMN_Position)),1)),"None")}

Uses SUMPRODUCT type calculation to find the first cell in "COLUMN_Name" that matches multiple criteria.

Formula #2:

{=IFERROR(INDEX(COLUMN_Name,SMALL(IF(COLUMN_Position="EMT",IF(COLUMN_TeamAssigned=M4,IF(RIGHT(COLUMN_BOGStatus,1)="1",ROW(COLUMN_BOGStatus)))),1)),"None")}

Uses nested IF statements to find the first cell in "COLUMN_Name" that matches multiple criteria.

For reference, each "COLUMN_xxx" named range is a dynamic named range that refers to a different single column of about 1000 rows each. Each dynamic named range uses an INDEX/MATCH formula to make it dynamic, not an INDIRECT or OFFSET formula, so they should be non-volatile.

Which of the two above array formulas is technically faster?

 

[Jerry Sullivan]

Hi jmwbowen,

There are many people more knowledgeable on this topic than me, but since you haven't had any replies I'll offer some thoughts...

The primary difference between the two forms is the use of IF functions versus taking the product of the 1's and 0's that come from the 3 comparisons.

While it might seem like there's an opportunity for array formula with the nested IF's to be more efficient by not continuing to evaluate expressions once the first comparison returns False, I don't think that this provides any benefit in your Array formula.

If we compare these two Non-Array formulas, the first one using the IF functions should be faster in cases where one of the earlier comparisons returns False.

=IF(B4="EMT",IF(C4=M4,IF(RIGHT(D4,1)="1","Yes")))

=IF((B4="EMT")*(C4=M4)*(RIGHT(D4,1)="1"),"Yes")

For your Array formula however; I believe that Excel still calculates the value of each comparison, then applies the IF function to the resulting Arrays.

The Evaluate Formula tool shows that at some point in Excel's evaluation the formula will be reduced to something like this...

=IFERROR(INDEX($A:$A1000,SMALL(IF({FALSE;TRUE;FALSE,.....,TRUE} , IF({TRUE;FALSE;FALSE,.....,TRUE}, IF({FALSE;TRUE;FALSE,.....FALSE} , {1,2,3....,1000}))),1)),"None")

If that's what really happens "under the hood" then any difference between those two array formulas should be negligible.

If speed of calculation is a priority consider exploring....
1. The use of helper cells to eliminate the need for Array formulas
2. Using a UDF (VBA User Defined Function)
3. Using a query (as Fazza suggested in your earlier thread).