Thread: A range inside a nested IF

Hey, I'm looking to insert a range of numbers inside a nested IF statement but it's not working so well. Here's an example:

=IF(A1=1:3, B1=-5, IF(A1=4:6, B1=-4, IF(A1=7:9, B1=-3)))

I think the problem is the 1:3, 4:6 and 7:9. I don't want to have to create If 1 Then -5, If 2 Then -5, If 3 Then -5, If 4 Then -4, etc. Anyone know of a better way?

use this in B1:
=INT((A1-1)/3)-5

Hey thanks for the quick reply. The system I used there was a quick example. The actual one is:

1 = -5
2-3 = -4
4-5 = -3
6-7 = -2
etc

how about:
=INT(A1/2)-5

Man I don't know how you figure that out so easily but thank you!

edit: actually now that I think about it the answer is so obvious. Still, it would've taken me much longer to get there. Thanks again!