CODEWITHSUNDEEP
excel
Alex664
Alex664

Reputation: 215

Excel formula that omits 0's and #N/A to find Min

Is there a function I can put in excel that will find the minimum value for the range "F3:G36" and ignore any cells that contain "#N/A" or 0? I'm basically looking for the 2nd lowest number in the range while omitting the #N/A's in my search. I have tried different combinations of Min(If(Isnumber(etc...) but it only returns #N/A as a value. I cannot change the vlookup formulas in the cells.

Thank you

Upvotes: 1

Views: 5497

Answers (1)

rwilson
rwilson

Reputation: 2135

Enter with CTRL + SHIFT + ENTER:

=SMALL(IF(ISNUMBER(F3:G36),IF(F3:G36>0,F3:G36)),2)

This will return the 2nd lowest number and avoid errors and 0s.

Upvotes: 2

Related Questions