MIN with zero values in the range
 

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!

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

array entered using CTRL+SHIFT+ENTER

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Brenda Rueter" wrote in message
...
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!

On Tue, 15 Mar 2005 13:10:07 -0500, "Brenda Rueter"
wrote:

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

Thank you! That worked perfectly!!!

"Ron Rosenfeld" wrote in message
...
On Tue, 15 Mar 2005 13:10:07 -0500, "Brenda Rueter"
wrote:

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

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.

"Ken Wright" wrote in message
...
=MIN(IF(A1:A100,A1:A100))

array entered using CTRL+SHIFT+ENTER

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--

"Brenda Rueter" wrote in message
...
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!

On Tue, 15 Mar 2005 13:41:03 -0500, "Brenda Rueter"
wrote:

Thank you! That worked perfectly!!!

You're welcome. Glad to help
--ron