# Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

## Ignore cell with 0 in Min formula

Posted by Tina Leese on March 06, 2001 6:46 AM
How do you get a MIN formula to exclude a cell with a blank or a zero sum?

## Re: Ignore cell with 0 in Min formula

Posted by Dave Hawley on March 06, 2001 6:54 AM

Hi Tina

You can use a Array formula for this:
=MIN(IF(A1:A100>0,A1:A100))

Any array formula must be entered by pushing Ctrl+Shift+Enter.

If you follow my link to my web site you will see a link to "Array Formulas". There are some examples here as well as important rules for arrays.

Dave

## Re: Ignore cell with 0 in Min formula

Posted by Aladin Akyurek on March 06, 2001 7:52 AM

You can also use the following array formula, which you enter by hitting CONTROL+SHIFT+ENTER:

=(A1:A8>0)*MIN(A1:A8)

## Re: Ignore cell with 0 in Min formula

Posted by Mark W. on March 06, 2001 8:09 AM
Tina, use {=MIN(IF(A1:A5=0,"",A1:A5))}.

Dave's formula, {=MIN(IF(A1:A5>0,A1:A5))}, won't
work if there are negative numbers in your list.

returns 0 if there's a zero in your list.

## Clarification...

Posted by Mark W. on March 06, 2001 8:15 AM
returns 0 if there's more than one zero

## Here something that should work

Posted by Aladin Akyurek on March 06, 2001 9:56 AM

Mark: Thanks for pointing out the flawed logic.

Here is a better formula:

If only lowest of non-zero numbers is desired, use:

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

If only lowest of positive (>0) numbers is desired, use:

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

## Re: Here something that should work

Posted by Mark W. on March 06, 2001 2:20 PM
doesn't work if there's both a zero and a
negative value in cells A1:A10.

## Re: Here something that should work

Posted by Aladin Akyurek on March 06, 2001 11:07 PM

Yeah.

For the lowest of the non-zero values, make that:

=IF(COUNTIF(A1:A10,"<0")=1,MIN(A1:A10),SMALL(A1:A10,COUNTIF(A1:A10,0)+COUNTIF(A1:A10,"<0")+1))

## RECAP (Re: Ignore cell with 0 in Min formula)

Posted by Aladin Akyurek on March 06, 2001 11:19 PM

Tina,

You gave us a brainteaser to tackle with, I must admit.

I believe you can choose from the following set of formulas.

If you want the lowest of the non-zero values, use:

=IF(COUNTIF(A1:A10,"<0")>=1,MIN(A1:A10),SMALL(A1:A10,COUNTIF(A1:A10,0)+COUNTIF(A1:A10,"<0")+1))

If you want only the lowest of the positive (>0) values, use:

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

This is it, I hope.

## Brainteaser? What about my original recommendation!

Posted by Mark W. on March 07, 2001 5:35 AM
{=MIN(IF(A1:A5=0,"",A1:A5))}

## Re: Brainteaser? What about my original recommendation!

Posted by Aladin Akyurek on March 07, 2001 7:01 AM

Sorry Mark, I completely missed it. Incredible, but true. I only saw there the holes you pointed out in other formulas.

## Re: Brainteaser? It seems so or merely an academic issue

Posted by Aladin Akyurek on March 07, 2001 7:13 AM

Mark: Yours returns 0, when every cell contains nothing but 0 or all cells are blank, mine return #NUM!. How about that?

## Re: Brainteaser? It seems so or merely an academic issue

Posted by Mark W. on March 07, 2001 8:12 AM
Neither behavior violates the specifications of
the original request. Technically,
{=MIN(IF(A1:A5=0,"",A1:A5))} does "...exclude a
cell with a blank or a zero sum." The 0 that's
returned in your case is not based on any cell
value, but rather on the documented behavior of
the MIN() function ("If the arguments contain no
numbers, MIN returns 0"). I would argue that
such a list wouldn't constitute a valid list
because the exclusion requirements would make it
a non-list anyway. It's all a question of
semantics... : )

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.