Forum Discussion
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 the formula I wrote:
=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+",""))))))))
I am getting the 'there's a problem with this formula' message. When I click OK to check where the problem lies, the cursor points to the place after the second closing paragraph. I know the number of opening and closing paragraphs match. I can't figure out what's wrong. Any help is appreciated.
Hi Laurie,
You may use
=LOOKUP(P12,{0,5,10,15,21},{"0-4","5-9","10-14","15-19","20+"})
7 Replies
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.
or use Sergei's answer which is even better..
It is very efficient. I appreciate both answers!
Hi Laurie,
You may use
=LOOKUP(P12,{0,5,10,15,21},{"0-4","5-9","10-14","15-19","20+"})Hi Sergei. This worked like a charm! Thanks so much!
You are welcome
