conditional MIN

[wwbwb]

How can I find the lowest number, not equal to 0, in a column. I have a column, I9:I60. I need to find the smallest number, but not any zeros.

 

[BuddieB]

Try this:

=SMALL(A11:A18,COUNT(A11:A18)-COUNTIF(A11:A18,">0")+1)

edit: sorry about that last one if you used it. try this.

 

[just_jon]

Not the best--

=MIN(IF(I9:I60<>0,I9:I60))

Array-entered w/ Control+Shift+Enter

 

[Brian from Maui]

Array entered,

=MIN(IF(F2:F5>0,F2:F5))

Adjust range

 

[Aladin Akyurek]

ADVERTISEMENT

How can I find the lowest number, not equal to 0, in a column. I have a column, I9:I60. I need to find the smallest number, but not any zeros.

If I9:I60 is not supposed to house any neg values, then either:

=MIN(IF(I9:I60>0,I9:i60))

which must be confirmed with control+shift+enter instead of just enter.

or:

=SMALL(I9:I60,COUNTIF(I9:I60,0)+1)

Otherwise:

=MIN(IF((I9:I60<>0),I9:i60))

which is also to be confirmed with control+shift+enter.

 

[BuddieB]

I knew Aladin would come up with a much more concise formula.

Nice work as always Aladin.

 

[wwbwb]

thank you both. They both work fine.

ray: