How to ignore #DIV/0! error

[dbcooper88]

Array formula is {=AVERAGE(IF(ISNUMBER(MATCH(I13:I18,SMALL(I13:I18,{1}),0)),(H13:H18)))}. Want to ignore the #DIV/0! error and use the cell in column H corresponding to the I column having the #DIV/0! error.

TIA

 

[joeu2004]

Wrap IFERROR around it, to wit:

=IFERROR(AVERAGE(IF(ISNUMBER(MATCH(I13:I18,SMALL(I13:I18,{1}),0)),(H13:H18))), "")

 

[dbcooper88]

Wrap IFERROR around it, to wit:

=IFERROR(AVERAGE(IF(ISNUMBER(MATCH(I13:I18,SMALL(I13:I18,{1}),0)),(H13:H18))), "")

Dont want result to be blank. Want result to be the corresponding cell. Error is in column I but their is a number in the corresponding column H which is what I want as the output.

 

[joeu2004]

Sorry, I misread your requirement. Perhaps:

=AVERAGE(IF(ISERROR(I13:I18), H13:H18, IF(ISNUMBER(MATCH(I13:I18,SMALL(I13:I18,{1}),0)), H13:H18)))

But in case, I think it would be prudent to wrap the AVERAGE expression with IFERROR in case none of the rows meets your requirements.

PS.... I believe the second parameter of SMALL should be a single value (1), not an array ({1}). Also, I believe SMALL(I13:I18,1) is the same as MIN(I13:I18).

 

[dbcooper88]

Thanks. Now what if I want to use the minimum value in column I above 0. Seems like I need to use MINIFS but cant get it to work. Came up with MINIFS(I13:I18,I13:I18,">0"), Further I dont think I need the AVERAGE function either as Im not looking for an average. To be clear I want the value in column H corresponding to the minimum value above 0 from column I.

 

[dbcooper88]

=MINIFS(H13:H18,I13:I18,">0",I13:I18,">0") works for original set of data but not the others. Confused.