Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excluding #DIV/0! in further calculations | Excel Worksheet Functions | |||
Averages excluding #N/A | Excel Discussion (Misc queries) | |||
excluding #N/A | Excel Discussion (Misc queries) | |||
Adding excluding zero | Excel Worksheet Functions | |||
MIN excluding 0s | Excel Worksheet Functions |