Thread: Array Error

Hi Everybody


I have this spreadsheet (attached for your convenience) that has a big array formula. It seems to work ok. However, when I use the same array formula as a part of an if conditon, it comes up with an error when the if condition is false. For example, in the attached spreadsheet, have a look at the formula in cell AA33 :-

=LARGE(OFFSET(INDIRECT("'DataFromXMLFiles'!"&ADDRESS((MATCH(CONCATENATE($B33,$C33,$D33,$A33),(CONCAT ENATE(INDEX(Table3[#Data],,1),INDEX(Table3[#Data],,2),INDEX(Table3[#Data],,3),INDEX(Table3[#Data],,6))),0)+ROW(Table3[#Data])-1),COLUMN(Table3[#Data]))),0,4,SUMPRODUCT(--(INDEX(Table3[#Data],,1)=$B33),--(INDEX(Table3[#Data],,2)=$C33),--(INDEX(Table3[#Data],,3)=$D33),--(INDEX(Table3[#Data],,6)=$A33)),1),AA$20)

This returns a numeric value as expected. No problems!

In the cells AB33 and AC33, the same formula has been entered as an if condition. In the cell AB33, the if condtion is false whilst in cell AC33 it is true. In AC33, the formula evaluates correctly to "Empty" whilst in AB33 is returns an error condition. Why?

These formulas are all array entered that is CSE confirmed.


Any suggestions, please?



Best regards



Deepak

Deepak,

Given your use of XL2007(+) [.xlsm and use of Table object] you should be using SUMIFS/COUNTIFS rather than SUMPRODUCT - this holds true for your Array also.

Y25:
=IFERROR(LARGE(OFFSET(INDEX(Table3[#Data],1,5),MATCH(CONCATENATE($B25,$C25,$D25,$A25),INDEX(CONCATENATE(INDEX(Table3[#Data],,1),INDEX(Table3[#Data],,2),INDEX(Table3[#Data],,3),INDEX(Table3[#Data],,6)),0),0)-1,0,COUNTIFS(INDEX(Table3[#Data],,1),$B25,INDEX(Table3[#Data],,2),$C25,INDEX(Table3[#Data],,3),$D25,INDEX(Table3[#Data],,6),$A25),1),Y$20),"")
confirmed with Enter
applied to matrix as appropriate

Note: I've not altered the syntax from your original too much but embedded an INDEX to remove need for Array entry (arguably less efficient but perhaps a little more robust)

You might want to consider adding a key to your Tables that concatenates A, B, C & F in each row to simplify the match process.

Your #N/As were the result of your concatenated MATCH failing - hence the outer IFERROR.

Belatedly I realise I should have also shown the embedded IF approach which would:

a) remove Volatility (no OFFSET etc...)
b) improve efficiency (exiting the LARGE upon first False)
c) shorten formula

Y25:
=IFERROR(LARGE(IF(Table3[CustomerState]=$B25,IF(Table3[FuelType]=$C25,IF(Table3[CustomerType]=$D25,IF(Table3[PeriodSpan]=$A25,Table3[Total])))),Y$20),"")
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
applied to matrix as appropriate

obviously Array entry is required ... note I reverted the INDEX to Table[Column]

Originally Posted by agarwaldvk

...you said that error #N/A in cell AB33 is because of the Match() function failing, that is not correct... Any ideas why? [it returns #N/A]

At work presently but will look later - I had not noticed the inconsistent formulas in your table.

Originally Posted by agarwaldvk

...isn't it true that the if condition is inherently more inefficient than an equivalent Index() function.

Not really. INDEX is a slow function per se though I confess it's still my "favourite" given it's amazing versatility. The embedded IF approach is pretty efficienct because the evaluation ends at the point of any condition failiing.

To retierate: if as implied efficiency is important then the approach you previously adopted should be avoid for those reasons outlined previously.

SUMPRODUCT and Arrays are inefficient and should be used in moderation and in my opinion you should look to avoid using in volatile context given the potential for significant calculation overhead.