How to ignore #DIV/0! error

dbcooper88

New Member
Joined
Dec 3, 2016
Messages
25
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
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
Wrap IFERROR around it, to wit:

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

dbcooper88

New Member
Joined
Dec 3, 2016
Messages
25
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
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
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

New Member
Joined
Dec 3, 2016
Messages
25
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
Joined
Dec 3, 2016
Messages
25
=MINIFS(H13:H18,I13:I18,">0",I13:I18,">0") works for original set of data but not the others. Confused.
 
Last edited:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top