Conditional minimums

A friend asked me the other day how to return the lowest positive value from a row of data in Excel. Given that there’s no MINIF function, at first I struggled.

Then I re-phrased the question: how do you return the Nth lowest value in a range? Ah, we can do that with the little-known SMALL function. And can we find N? Sure we can, using the COUNTIF function.

So, the following formula gets you what you want:

=SMALL(A1:Z1,COUNTIF(A1:Z1,"<=0")+1)

In English, this reads: From the range A1:Z1, count the number of non-positive values, add 1 (the result being N). Now return the Nth lowest value.

• Tweet