CODEWITHSUNDEEP
excelconstraintsarray-formulas
Michael Elkin
Michael Elkin

Reputation: 221

Excel min value greater than x returns 0 if no value found?

I am using the following formula: =MIN(IF(A1:A5>B1,A1:A5)) use Ctrl-Shift-Enter My value for B1 is 10 and my array is {1,5,4,2,7} so in this case no value is greater than 10. The problem is that excel returns 0 as the result of the empty set which is a problem as 0 is not greater than 10. In this case, I can test if the result 0 is greater than 10 and see that the result is invalid, however, if B1 is -10 for an array of {-15,-24,-11,-37-60} than the 0 seems like a valid value when no correct value exists.

So anybody know of how I can find the min or max value of a set with constraints, but return either an error or something distinct if the solution set is empty?

Thank you.

Upvotes: 2

Views: 12910

Answers (2)

barry houdini
barry houdini

Reputation: 46341

Try using SMALL instead of MIN, i.e.

=SMALL(IF(A1:A5>B1,A1:A5),1)

Unlike MIN the SMALL function will return an error [#NUM!] for your example

....or if you want a text value instead of an error then use IFERROR function, too, i.e.

=IFERROR(SMALL(IF(A1:A5>B1,A1:A5),1),"None")

Upvotes: 4

atk
atk

Reputation: 9314

Your IF statement will return False if none of the numbers in the range are greater than 10. It appears that MIN is converting False to numeric (0). You need to add behavior to handle the False.

If you know that all valid values must be >=0, then you could use the "else" section of the IF formula to return -1.

MIN(IF(A1:A5>B1,A1:A5,-1))

Upvotes: 1

Related Questions