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