Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if/and formula
I need help with a simple task, but I'm stumped! The formula below works,
except when the value in colmn H is 0. 1. or 2. Unfortunately, those values can be in that column and I can't figure out how to write the formula the way it really needs to be, which is basically that I want it to return the value "2500" only if the the value in column H is BETWEEN 3 and 25. Please help!!! =IF(AND(H3=3,H3<=25),100*H3,2500) Thanks... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if/and formula
Clarification: the question above should read as follows...
I can't figure out how to write the formula the way it really needs to be, which is basically that I want it to return the value of "100*H3" OR "2500" only if the value in column H is BETWEEN 3 and 25 -- if the value is 0, 1, or 2 I need a zero or a null. Thanks again! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if/and formula
"kimkay" wrote:
I need help with a simple task, but I'm stumped! The formula below works, except when the value in colmn H is 0. 1. or 2. Unfortunately, those values can be in that column and I can't figure out how to write the formula the way it really needs to be, which is basically that I want it to return the value "2500" only if the the value in column H is BETWEEN 3 and 25. Please help!!! =IF(AND(H3=3,H3<=25),100*H3,2500) Maybe just flip the condition if TRUE/FALSE around ?, viz: =IF(AND(H3=3,H3<=25),2500,100*H3) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if/and formula
Your formula is correct- almost- in that in properly checks for the
value in H3. The way your formula is written, when H3 is between 3 and 25, the result will be 100*H3 (this is the value returned when the logical value is TRUE). When H3 is not between 3 and 25 the formula returns 2500. So you just need to reverse your formula: =IF(AND(H3=3,H3<=25),2500,100*H3) .... assuming you want the FALSE value to return 100*H3. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if/and formula
"kimkay" wrote:
Clarification: the question above should read as follows... I can't figure out how to write the formula the way it really needs to be, which is basically that I want it to return the value of "100*H3" OR "2500" only if the value in column H is BETWEEN 3 and 25 -- if the value is 0, 1, or 2 I need a zero or a null. 2nd guess, maybe you mean something like ..: =IF(OR(H3={0,1,2}),0,IF(AND(H3=3,H3<=25),MAX(2500 ,100*H3),"H3 is out-of-range")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if/and formula
2nd guess, maybe you mean something like ..:
=IF(OR(H3={0,1,2}),0,IF(AND(H3=3,H3<=25),MAX(2500 ,100*H3),"H3 is out-of-range")) Okay, Max, this one almost works -- if I enter the number 2 in H3, I get a result of zero, which is correct. If I enter the number 26, I get the "H3 is out-of-range" message, which is also correct, sort of (if I replace it with "2500" it will achieve my goal, which is to have any value greater than 25 in H3 to return a value of 2500). However, if I enter anything in between 3 and 25, I get 2500, which isn't what I need. I need the 100*H3 calculation to happen on those number instead of the formula always returning the value of 2500. Any ideas? I've tried manipulating that part of the formula without any luck. Thanks! Kim |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if/and formula
.... assuming you want the FALSE value to return 100*H3.
Almost but not quite, Dave! I wanted the true value to return the 100*H3, and the false value was working for any number greater than 25 in H3, I was just having trouble with those pesky number less than 3. Max -- I tweaked your suggestion more and got it to work! Here's the formula that did the trick: =IF(OR(H3={0,1,2}),0,IF(AND(H3=3,H3<=25),(100*H3) ,2500)) Many thanks!! This saved me a ton of work and a lot of head-scratching. Kim |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if/and formula
"kimkay" wrote:
2nd guess, maybe you mean something like ..: =IF(OR(H3={0,1,2}),0,IF(AND(H3=3,H3<=25),MAX(2500 ,100*H3),"H3 is out-of-range")) Okay, Max, this one almost works -- if I enter the number 2 in H3, I get a result of zero, which is correct. If I enter the number 26, I get the "H3 is out-of-range" message, which is also correct, sort of (if I replace it with "2500" it will achieve my goal, which is to have any value greater than 25 in H3 to return a value of 2500). However, if I enter anything in between 3 and 25, I get 2500, which isn't what I need. I need the 100*H3 calculation to happen on those number instead of the formula always returning the value of 2500. Believe this should do it: =IF(OR(H3={0,1,2}),0,IF(AND(H3=3,H3<=25),100*H3,2 500)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with if/and formula
"kimkay" wrote:
.. Max -- I tweaked your suggestion more and got it to work! Here's the formula that did the trick: =IF(OR(H3={0,1,2}),0,IF(AND(H3=3,H3<=25),(100*H3) ,2500)) Many thanks!! This saved me a ton of work and a lot of head-scratching. Glad to hear that, Kim ! Pl disregard my response in the other branch .. (didn't know you had a message for me over here <g) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|