Nested IF question

[Handsome Rob]

Sorry, lots of searching but can't find the solution. I get nested If functions, I'm trying to come up with this.

A1
If cell A2 is <6, assign 0.
If cell A2 is 6-19, assign 0.5.
If cell A2 is 20-40, assign 0.75.
If cell A2 is >40, assign 1.25.

I've come up with this:
=IF(A2<6,0,IF(A2>5,0.5,IF(A2>19,0.75,IF(A2>40,1.25))))

And it works, however it won't go above the second nested IF formula. If I put say '20', in to A2, it still returns 0.5.

Kind of scratching my head.

Thanks.

[ConneXionLost]

How about a different approach:

Please Login or Register  to view this content.

Cheers,

[daddylonglegs]

...just to explain why your version doesn't work....

The IFs are evaluated in order so with this

=IF(A2<6,0,IF(A2>5,0.5,IF(A2>19,0.75,IF(A2>40,1.25))))

the first IF assigns zero if A2 < 6, the second IF assigns 0.5 if A2 > 5.......that's it. If A2 doesn't fall in to that first category it will fall into the second - the final two IFs are never evaluated (anything > 19 or > 40 has already been gobbled up by the > 5 category). To make it work the way you want you need to go from low to high or high to low, e.g.

=IF(A2>40,1.25,IF(A2>19,0.75,IF(A2>5,0.5,0)))

Note: I didn't need the final IF because the first 3 cover all numbers > 5....so everything else falls into <=5, I don't need to check for that.....

[Handsome Rob]

Thanks much folks. I used the first reply but thanks for the explanation.