# Nested IF or IF(AND formula needed please

1. ## Nested IF or IF(AND formula needed please

Here's what I've come up with and I hope I am close as it's great fun to try to work this out for myself. The first section on it's own works just fine but not when I've added the second IF(AND

=IF(AND(I4>0,I4<9),"5.0-5.5","0"),IF(AND(I4=9,I4<12),"5.6-6","0"),IF(AND(I4=12,I4<15),"6.1-6.5","0")

My requirement is:
If I4 is greater than 0 but less than or = to 8 then G4 will show 5.0-5.5
If I4 is greater than 8 but less than 12 then G4 will show 5.6-6
If I4 is greater than 11 but less than 15 then G4 will show 6.1-6.5

If there is nothing in I4 then G4 will show 0

Attached is my spreadsheet unlocked (hopefully).  Register To Reply

2. ## Re: Nested IF or IF(AND formula needed please

May be this....... ``Please Login or Register  to view this content.``  Register To Reply

3. ## Re: Nested IF or IF(AND formula needed please

hi rwalker. maybe:
=IF(AND(I4>0,I4<9),"5.0-5.5",IF(AND(I4=9,I4<12),"5.6-6",IF(AND(I4=12,I4<15),"6.1-6.5","0")))

if you were building the formula parts by parts, you notice that the screen tip is always bold when you are filling in the argument:
=IF(logical_test,[value_if_true],[value_if_false])

so when you did up to this portion:
=IF(AND(I4>0,I4<9),"5.0-5.5","0")

did you notice that you have ended the formula? that's why Excel couldn't evaluate anymore & gave you a VALUE error. so IF the 1st statement is TRUE, return "5.0-5.5". if it's FALSE, do another IF statement:
=IF(AND(I4>0,I4<9),"5.0-5.5",IF(AND(I4=9,I4<12),"5.6-6",

and continue on...

you could actually shorten it further because the IF formula eliminates from the left to the right. that means IF I4 is 1-8, you would have got your answer & the IF formula stops calculating. IF it fails, you no longer have to test for 1-8:
=IF(OR(I4>=15,I4<=0),"0",IF(I4<9,"5.0-5.5",IF(I4<12,"5.6-6","6.1-6.5")))  Register To Reply

4. ## Re: Nested IF or IF(AND formula needed please

Oh, Thank you both so much. It's such fun learning excel and trying to find answers. For a start that's great that I had it sort of right but had closed the first section. And a bonus to shorten it up - I've learned!
Cheers  Register To Reply