Minimum Value Excluding Zero ("MinIf" function)

Ever confronted trying to find the minimum value excluding zero? Specially using downloaded metered data, there are times when you want to get the lowest value (with the exception of power interruption in which case it is zero). So how do we go about it in excel?

Here's the formula!

Assuming data is in cells E11-E18, then:

=MIN(IF(E11:E18>0,E11:E18,FALSE))

Basically, this formula says that, get minimum value IF the number in the array is greater than zero. If it is zero or below, do not consider it.

IMPORTANT NOTE: then instead of using only the enter key, use CTL-SHIFT-ENTER. This will convert the formula into an ARRAY FORMULA which will make this work. You can see if it is succesfully converted to array formula if you see a bracket enclosing the formula.

This formula can also be used with different conditions. This is one "MIN IF" formula.

Hope this helps! Note: You can also use this concept to create a "MAX IF" formula.

Tweet