MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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]