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

Question

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.

Solution 1

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")

Solution 2

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))