Where do I insert ISERROR?

[ACurtis802]

Hi everyone,

I am using an array formula like this in cells A2:H100 of a worksheet:

=INDEX('Source Data'!$A$2:$Q$5000,SMALL(IF(('Source Data'!$I$2:$I$5000>=$O$1)*('Source Data'!$I$2:$I$5000<=$P$1)*('Source Data'!$M$2:$M$5000>0),ROW('Source Data'!$A$2:$Q$5000)-ROW('Source Data'!$A$2)+1,ROW($Q$5000)+1),ROW()-1),1)

The function must be present in all 99 rows to accommodate potential data, but there is rarely enough data to populate the entire sheet, leaving #REF! errors in some cells.

Putting aside that using this many array formulae is sloppy programming, how do I craft the function above to return no value if the function evaluates to an #REF! error?

Thanks and best regards,
Anthony

[shg]

If you use ISERROR, you double the processing load of the formula. The general form is

=if(iserror(bigLongExpression), "", bigLongExpression)

Suggest you instead use conditional formatting with ISERROR to make the font the same color as the cell when the formula returns an error, so it's not apparent.

[JBeaucaire]

Rather than evaluate every formula twice with an IF(ISERROR() approach, let's use the Excel 2007 approach of IFERROR.

Change your existing formula to:

=IFERROR(INDEX('Source Data'!$A$2:$Q$5000,SMALL(IF(('Source Data'!$I$2:$I$5000>=$O$1)*('Source Data'!$I$2:$I$5000<=$P$1)*('Source Data'!$M$2:$M$5000>0),ROW('Source Data'!$A$2:$Q$5000)-ROW('Source Data'!$A$2)+1,ROW($Q$5000)+1),ROW()-1),1),"")

If you're NOT using Excel 2007, we can still use this, but you have to add the IFERROR function to your sheet. Here's the code:

Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
    If IsArray(ToEvaluate) Then
        IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
    Else
        IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
    End If
End Function

To add it to your sheet, press Alt-F11
Click Insert>Module
Paste in the code above
Alt-F11 to close the editor
Save your sheet

Now the =IFERROR(FormulaToUse,DoThisIfErrorOccurs) format will work.

[ACurtis802]

Thank you, shg and JBeaucaire. This gives me two workable options.

Best regards,
ACurtis802

[shg]

You're welcome.

To be clear, the conditional formatting formula you need is just =iserror(me), where me is the address if the cell in which it appears. You don't need to reevaluate the big expression.