MIN function
Posted by Rob on December 14, 2001 8:53 AM
How do I get a MIN value of specific cells (not a range) without counting the zero values.
Ex. I want the MIN value of c3,g3,k3,l3,q3 but do not want the zero values in any of those cells to be counted.
Posted by Mark W. on December 14, 2001 9:17 AM
Here's a way (there might be an easier one)...
{=MIN(IF(COLUMN(A3:Q3)={0,0,3,0,0,0,7,0,0,0,11,12,0,0,0,0,17},A3:Q3))}
Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.
Posted by Mark W. on December 14, 2001 9:21 AM
Oops! Forgot to add the non-zero test! Use this...
{=MIN(IF((COLUMN(A3:Q3)={0,0,3,0,0,0,7,0,0,0,11,12,0,0,0,0,17})*A3:Q3,A3:Q3))}
Posted by Aladin Akyurek on December 14, 2001 9:58 AM
Re: Oops! Forgot to add the non-zero test! Use this...
Mark --
It needs also an ISNUMBER test in case there are formulas returning "" in between columns of interest in row 3:
=MIN(IF((COLUMN(A3:Q3)={0,0,3,0,0,0,7,0,0,0,11,12,0,0,0,0,17})*ISNUMBER(A3:Q3),A3:Q3))
Aladin
Posted by Mark W. on December 14, 2001 10:03 AM
Ahh! To true!!! Did I overcomplicate this thing? (nt)
Posted by Aladin Akyurek on December 14, 2001 10:05 AM
Explain the logic of it to Rob will you? [NT]
