Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get absolute values for a range of cells? | Excel Discussion (Misc queries) | |||
Checking ALL values in a range | Excel Discussion (Misc queries) | |||
How to move Y-axis values when X range is -a to +b | Charts and Charting in Excel | |||
Counting values within a Date Range | Excel Discussion (Misc queries) | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |