 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:2B0DF04113DB497994819A491AB14734@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, arrayentered
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
3897 Views
(page loaded in 0.003 seconds)
