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!

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!

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

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!

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!

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!

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!

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!