# How to ignore #DIV/0! error

#### dbcooper88

##### New Member
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

##### Well-known Member
Wrap IFERROR around it, to wit:

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

#### dbcooper88

##### New Member
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

##### Well-known Member

=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

##### New Member
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

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

Last edited:

1,082,380
Messages
5,365,117
Members
400,824
Latest member
Themilkybarkid

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...