Forum Discussion
Solved
Problem with nested IF formula #Excel #2018
Greetings, I am trying to create a formula that will check the value in the adjacent cell to determine if it falls within a specified range, and if so, return text for the range, i.e, 5-9. This is t...
Hi Laurie,
You may use
=LOOKUP(P12,{0,5,10,15,21},{"0-4","5-9","10-14","15-19","20+"})
You haven't closed the ANDs
you formula should be:
=IF(P12<5,"0-4",IF(AND(P12>4,P12<10),"5-9",IF(AND(P12>9,P12<15),"10-14",IF(AND(P12>14,P12<20),"15-19",IF(P12>=20,"20+","")))))
You actually don't really need the ANDs at all.. this will do the same thing:
=IF(P12<5,"0-4",IF(P12<10,"5-9",IF(P12<15,"10-14",IF(P12<20,"15-19","20+"))))
it works because excel will stop at the first TRUE condition. If P12 is 9 for example, you dont need the AND to check if its '>4', if it less less than 5 the first IF would have been true.
Thank you Phillip. This works also. I appreciate the explanation. I have a better understanding now of how such statements work.