Help with if/and formula

[kimkay]

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...

[kimkay]

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!

[Max]

"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
---

[kimkay]

> 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

[Max]

"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,2500))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

"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
---

[Dave O]

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.

[kimkay]

.... 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

[Max]

"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
---