MIN with zero values in the range

[Brenda Rueter]

How do I get the minimum number in a range EXCLUDING ZEROS? I have a range
and some cells in the range have a zero balance. MIN ordinarily would be
zero, but I want the next smaller number. So far I do not have a clue on
how to get there. TIA!

 

[Ken Wright]

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

array entered using CTRL+SHIFT+ENTER

 

[Ron Rosenfeld]

How do I get the minimum number in a range EXCLUDING ZEROS? I have a range
and some cells in the range have a zero balance. MIN ordinarily would be
zero, but I want the next smaller number. So far I do not have a clue on
how to get there. TIA!

Here's one way.

=LARGE(rng,COUNTIF(rng,">0"))

Substitute your range for rng in the formula.

Your description is a bit ambiguous. The above formula will give you the "next
smaller number" than 0.

If you want to "exclude zeros" than implies that some numbers may be less than
zero and you would want include those in the test.

Therefore you would need the formula:


=LARGE(rng,COUNTIF(rng,"<>0"))



--ron

 

[Brenda Rueter]

Thank you! That worked perfectly!!!

Here's one way.

=LARGE(rng,COUNTIF(rng,">0"))

Substitute your range for rng in the formula.

Your description is a bit ambiguous. The above formula will give you the "next
smaller number" than 0.

If you want to "exclude zeros" than implies that some numbers may be less than
zero and you would want include those in the test.

Therefore you would need the formula:


=LARGE(rng,COUNTIF(rng,"<>0"))



--ron

 

[Brenda Rueter]

Ken, I'm afraid I don't follow the "array" part and Ctrl+Shieft+Enter. Can
you elaborate? I also do not understand what the IF statement is doing in
this formula, but I would like to understand it.

 

[Ron Rosenfeld]

Thank you! That worked perfectly!!!

You're welcome. Glad to help
--ron