Friday, September 19, 2008

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:


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.


Trevor said...

This helped a lot, but how do you perform this function on multiple tabs? For example:

=MIN(TabA!A1, TabB!A1, TabC!A1)

I still want to return the lowest number from each of these cells excluding zero.


Related Posts Plugin for WordPress, Blogger...