MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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?


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


OzGrid Business Applications

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)

Aladin

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.

Aladin's formula, {=(A1:A5>0)*MIN(A1:A5)},
returns 0 if there's a zero in your list.

Posted by Mark W. on March 06, 2001 8:15 AM

Clarification...

Aladin's formula, {=(A1:A5>0)*MIN(A1:A5)},
returns 0 if there's more than one zero
in your list.

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

Here something that should work

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)

Aladin

Posted by Mark W. on March 06, 2001 2:20 PM

Re: Here something that should work

Aladin, =SMALL(A1:A10,COUNTIF(A1:A10,0)+1),
doesn't work if there's both a zero and a
negative value in cells A1:A10.

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

Re: Here something that should work

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))

Aladin


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.

Aladin

Posted by Mark W. on March 07, 2001 5:35 AM

Brainteaser? What about my original recommendation!

{=MIN(IF(A1:A5=0,"",A1:A5))}

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

Re: Brainteaser? What about my original recommendation!

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

Aladin

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

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


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

Posted by Mark W. on March 07, 2001 8:12 AM

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

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