Min function but ignore zero values

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have been going crazy trying to find a solution to this,
hope someone can help. I am trying to look at a series of
cells for the minimum numeric value but would like to
ignore any cells that have a zero value. Any help would be
appreciated.

Mike
 
=MIN(IF(Range,Range))

which you need to confirm with control+shift+enter, not just with enter.
 
Mike
One way:

=AVERAGE(IF(A2:A10,A2:A10))

The formula is an array formula and must be entered with
<Shift><Ctrl><Enter> also if edited later. If done correctly,
Excel will display the formula in the formula bar enclosed
in curly brackets { }. Don't enter these brackets yourself.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Of course I meant MIN instead of AVERAGE. Sorry.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
I get the #value! error code when entering the array
function this way.
Click to expand...

You must have text in the range. Make the test explicit. If you want the
minimum positive number, use

=MIN(IF(Range>0,Range))

If you want the minimum number possibly including negative numbers, use

=MIN(IF(Range<>0,Range))
 
Thanks, it worked great. I also found that by creating a
entry validation that only allowed numeric entries I could
avoid the error code.

Mike
 
Back
Top