Min Excluding Value

  • Thread starter Thread starter kazoo
  • Start date Start date
K

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!
 
Use the array formula:

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

enter it with CNTRL-SHFT-ENTER instead of ENTER
 
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
 
=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)
 
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
 
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
 
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)
 
This seems to work.
Thanks!

Ron Coderre said:
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)
Click to expand...
 
You're welcome!
--------------------------

Regards,

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