Min Excluding Value

[kazoo]

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!

 

[Gary''s Student]

Use the array formula:

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

enter it with CNTRL-SHFT-ENTER instead of ENTER

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bob Phillips]

=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:A6>0,A1:A6))

--
---
HTH

Bob


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

 

[Mike H]

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

 

[kazoo]

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

 

[Ron Coderre]

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]

This seems to work.
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)

 

[Ron Coderre]

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

Regards,

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