Minimum values

Posted by Henry Collet on January 23, 2002 1:02 PM

I some data and I want to find the smallest value, without having to resort to rearranging the data. The thing is, I want to ignore all instances of 0, and want the smallest next one. ie if my data was 0,1,2,3,0,4,0,4,5,6,7,8,9 then the smallest would be 1.

Help please...

Henry

Posted by IML on January 23, 2002 1:09 PM

Try
=MIN(IF($A$1:$M$1,$A$1:$M$1)) to exclude zeros or
=MIN(IF($A$1:$M$1>0,$A$1:$M$1)) to exlude zeros and negatives.

For these array formulas to work, have control and shift depressed before you hit enter.

good luck

Posted by Aladin Akyurek on January 23, 2002 1:11 PM

Array-enter

=MIN(IF(A1:A6,A1:A6))

You need to hit control+shift+enter at the same time, not just enter in order to array-enter a formula.

==========

Posted by Mark W. on January 23, 2002 1:12 PM

{=MIN(IF(A1:A13,A1:A13))}

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.