Nested IF Statement 3 Conditions, Need Help Fixing

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

 

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

 

[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)))

 

[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?

 

[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!

 

[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

 

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

 

[Brad]

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