Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Brenda Rueter
 
Posts: n/a
Default 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!


  #2   Report Post  
Ken Wright
 
Posts: n/a
Default

=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!




  #3   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #4   Report Post  
Brenda Rueter
 
Posts: n/a
Default

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



  #5   Report Post  
Brenda Rueter
 
Posts: n/a
Default

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!








  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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

Thank you! That worked perfectly!!!


You're welcome. Glad to help
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I get absolute values for a range of cells? Terry Excel Discussion (Misc queries) 3 March 2nd 05 04:54 PM
Checking ALL values in a range nospaminlich Excel Discussion (Misc queries) 13 February 10th 05 10:29 AM
How to move Y-axis values when X range is -a to +b [email protected] Charts and Charting in Excel 4 February 1st 05 12:54 AM
Counting values within a Date Range Jana Excel Discussion (Misc queries) 7 December 9th 04 11:18 PM
Can you average data in 1 column based on a range of values in another? kman24 Excel Worksheet Functions 2 November 17th 04 03:09 PM


All times are GMT +1. The time now is 01:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"