Min Excluding Value
 

Is it possible to have a formula with a standard Excel function that gives the
Min value in a column excluding the number -273?

Example:

Col A
Row1 4.3
Row2 5.7
Row3 4.2
Row4 -273
Row5 3.9
Row6 -273

The formula should return a min of 3.9 for A1:A6

Thanks!

Min Excluding Value
 

Use the array formula:

=MIN(IF(B1:B6<-273,B1:B6))

enter it with CNTRL-SHFT-ENTER instead of ENTER
--
Gary''s Student - gsnu200771


"kazoo" wrote:

Is it possible to have a formula with a standard Excel function that gives the
Min value in a column excluding the number -273?

Example:

Col A
Row1 4.3
Row2 5.7
Row3 4.2
Row4 -273
Row5 3.9
Row6 -273

The formula should return a min of 3.9 for A1:A6

Thanks!

Min Excluding Value
 

Is there more to your question than your posted? By this I mean, are you
interested in excluding specifically the number -273 (that is, -272 and -274
are OK?), or are you looking to exclude **all** negative values (as your
example could be interpreted as asking), or perhaps something else?

Rick


"kazoo" wrote in message
...
Is it possible to have a formula with a standard Excel function that gives
the
Min value in a column excluding the number -273?

Example:

Col A
Row1 4.3
Row2 5.7
Row3 4.2
Row4 -273
Row5 3.9
Row6 -273

The formula should return a min of 3.9 for A1:A6

Thanks!

Min Excluding Value
 

=MIN(IF(A1:A6<-273,A1:A6))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

But maybe you really want

=MIN(IF(A1:A60,A1:A6))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"kazoo" wrote in message
...
Is it possible to have a formula with a standard Excel function that gives
the
Min value in a column excluding the number -273?

Example:

Col A
Row1 4.3
Row2 5.7
Row3 4.2
Row4 -273
Row5 3.9
Row6 -273

The formula should return a min of 3.9 for A1:A6

Thanks!

Min Excluding Value
 

My bet is the OP is talking temperatures so -274 doesn't exist and he's
trying to exclude absolute zero but then maybe not

"Rick Rothstein (MVP - VB)" wrote:

Is there more to your question than your posted? By this I mean, are you
interested in excluding specifically the number -273 (that is, -272 and -274
are OK?), or are you looking to exclude **all** negative values (as your
example could be interpreted as asking), or perhaps something else?

Rick


"kazoo" wrote in message
...
Is it possible to have a formula with a standard Excel function that gives
the
Min value in a column excluding the number -273?

Example:

Col A
Row1 4.3
Row2 5.7
Row3 4.2
Row4 -273
Row5 3.9
Row6 -273

The formula should return a min of 3.9 for A1:A6

Thanks!

Min Excluding Value
 

It works great with the numbers I provided above. But, how does this work if
have blank cells interspersed? It seems to still work if one of the values
is negative and there are blank cells, but if they are all positive, it
doesn't work.

For example:
Col A
Row1 4.3
Row2 5.7
Row3
Row4 -273
Row5 3.9
Row6 -273

"Gary''s Student" wrote:

Use the array formula:

=MIN(IF(B1:B6<-273,B1:B6))

enter it with CNTRL-SHFT-ENTER instead of ENTER
--
Gary''s Student - gsnu200771


"kazoo" wrote:

Is it possible to have a formula with a standard Excel function that gives the
Min value in a column excluding the number -273?

Example:

Col A
Row1 4.3
Row2 5.7
Row3 4.2
Row4 -273
Row5 3.9
Row6 -273

The formula should return a min of 3.9 for A1:A6

Thanks!

Min Excluding Value
 

Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER):

=MIN(IF(ISNUMBER(A1:A10)*(A1:A10<-273),A1:A10))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kazoo" wrote in message
...
Is it possible to have a formula with a standard Excel function that gives
the
Min value in a column excluding the number -273?

Example:

Col A
Row1 4.3
Row2 5.7
Row3 4.2
Row4 -273
Row5 3.9
Row6 -273

The formula should return a min of 3.9 for A1:A6

Thanks!

Min Excluding Value
 

This seems to work.
Thanks!

"Ron Coderre" wrote:

Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER):

=MIN(IF(ISNUMBER(A1:A10)*(A1:A10<-273),A1:A10))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kazoo" wrote in message
...
Is it possible to have a formula with a standard Excel function that gives
the
Min value in a column excluding the number -273?

Example:

Col A
Row1 4.3
Row2 5.7
Row3 4.2
Row4 -273
Row5 3.9
Row6 -273

The formula should return a min of 3.9 for A1:A6

Thanks!

Min Excluding Value
 

You're welcome!
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kazoo" wrote in message
...
This seems to work.
Thanks!

"Ron Coderre" wrote:

Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER):

=MIN(IF(ISNUMBER(A1:A10)*(A1:A10<-273),A1:A10))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"kazoo" wrote in message
...
Is it possible to have a formula with a standard Excel function that
gives
the
Min value in a column excluding the number -273?

Example:

Col A
Row1 4.3
Row2 5.7
Row3 4.2
Row4 -273
Row5 3.9
Row6 -273

The formula should return a min of 3.9 for A1:A6

Thanks!