How do I find the minimum value in a range while ignoring zeros?

  • Follow


-- How do I find the minimum value in a range while ignoring any zeros in 
that range using Excel 2007?

0
Reply Utf 4/6/2010 2:44:01 AM

If the numbers are *always* positive..

Array entered**:

..=MIN(IF(A1:A10>0,A1:A10))

Or, normally entered:

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

If there might be negative numbers...

Array entered**:

=MIN(IF(A1:A10<>0,A1:A10))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

-- 
Biff
Microsoft Excel MVP


"Ted B." <TedB@discussions.microsoft.com> wrote in message 
news:2B0DF041-13DB-4979-9481-9A491AB14734@microsoft.com...
>
> -- How do I find the minimum value in a range while ignoring any zeros in
> that range using Excel 2007?
> 


0
Reply T 4/6/2010 2:57:08 AM

hi
try this.....
=small(A1:A50,countif(A1:A50,0)+1)

regards
FSt1

"Ted B." wrote:

> 
> -- How do I find the minimum value in a range while ignoring any zeros in 
> that range using Excel 2007?
> 
1
Reply Utf 4/6/2010 3:21:01 AM

You could use a conditional MIN, something like this in say B2, array-entered 
ie press CTRL+SHIFT+ENTER to confirm the formula (instead of just pressing 
ENTER):
=MIN(IF(A2:A10>0,A2:A10))
Success? hit the YES below
-- 
Max
Singapore
--- 
"Ted B." wrote:
> -- How do I find the minimum value in a range while ignoring any zeros in 
> that range using Excel 2007?
> 
0
Reply Utf 4/6/2010 3:22:01 AM

hi
forgot to mention.
adjust ranges to suit your data.

Regards
FSt1

"Ted B." wrote:

> 
> -- How do I find the minimum value in a range while ignoring any zeros in 
> that range using Excel 2007?
> 
0
Reply Utf 4/6/2010 3:22:03 AM

4 Replies
4740 Views

(page loaded in 0.079 seconds)


Reply: