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
OzGrid Business Applications
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)
Aladin
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.
Aladin's formula, {=(A1:A5>0)*MIN(A1:A5)},
returns 0 if there's a zero in your list.
Clarification...
Posted by Mark W. on March 06, 2001 8:15 AM
Aladin's formula, {=(A1:A5>0)*MIN(A1:A5)},
returns 0 if there's more than one zero
in your list.
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)
Aladin
Re: Here something that should work
Posted by Mark W. on March 06, 2001 2:20 PM
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.
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))
Aladin
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.
Aladin
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.
Aladin
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.