Nested IF Statement 3 Conditions, Need Help Fixing

F

f252863

I am trying to set it up so that depending on the value of W2 it will
return .00075, .005, or .003:

=IF(AND(W2>1095=TRUE,W2<1461=TRUE),
0.00075),IF(AND(W2>1500=TRUE,W2<2000=TRUE),
0.005),IF(AND(W2>2500=TRUE,W2<3000=TRUE),0.003)

I get a #VALUE! error.

Thanks!!

What this does is takes a value in another cell that represents the
difference between two dates and then calculates the payment ratio.
I'd love to incorporate the entire formula into one cell but the task
seems daunting so I use a helper column.
 
B

Brad

modifing your formul
=IF(AND(W2>1095,W2<1461),0.00075,IF(AND(W2>1500,W2<2000),0.005,IF(AND(W2>2500,W2<3000),0.003,"")))

There are alot of numbers that you don't specify what the value should be.

The above equation will make the cell blank for these other numbers...
 
J

Joel

You don't need True or false as part of the AND. You were getting
TRUE=FALSE. Also your If's need to be nested, I changed where the parethesis
are located

=IF(AND(W2>1095,W2<1461),0.00075,IF(AND(W2>1500,W2<2000),0.005,IF(AND(W2>2500,W2<3000),0.003)))
 
G

Gaurav

=IF(AND(W2>1095,W2<1461),0.00075,IF(AND(W2>1500,W2<2000),0.005,IF(AND(W2>2500,W2<3000),0.003,"")))

BUT....what happens if W2 is between 1461 and 1500? what happens if W2 is
between 2000 and 2500?
 
F

f252863

You guys are terrific, I post and within minutes have two responses.
Solutions worked perfectly, thanks for taking the time I really
appreciate it!
 
P

PCLIVE

If those are the only three possible conditions, then you can simplify a
little:

=IF(AND(W2>1095,W2<1461),0.00075,IF(AND(W2>1500,W2<2000),0.005,0.003))


However, if W2 can fall out side of the ranges you specified, then you
should specify the final FALSE condition:

=IF(AND(W2>1095,W2<1461),0.00075,IF(AND(W2>1500,W2<2000),0.005,IF(AND(W2>2500,W2<3000),0.003,"")))

HTH,
Paul
 
D

David Biddulph

Your syntax is wrong. Look at the placing of your parentheses.

Try
=IF(AND(W2>1095=TRUE,W2<1461=TRUE),
0.00075,IF(AND(W2>1500=TRUE,W2<2000=TRUE),
0.005,IF(AND(W2>2500=TRUE,W2<3000=TRUE),0.003)))

Also, you haven't defined a result for all cases, so you could use
=IF(AND(W2>1095=TRUE,W2<1461=TRUE),
0.00075,IF(AND(W2>1500=TRUE,W2<2000=TRUE),
0.005,IF(AND(W2>2500=TRUE,W2<3000=TRUE),0.003,"undefined output")))
 
B

Brad

What would be nice would be for you to mark that our answers "answered" your
question - I would appreciate it....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top