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:
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.