SOLVED

Problem with nested IF formula #Excel #2018

Copper Contributor

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.

7 Replies
best response confirmed by Laurie McDowell (Copper Contributor)
Solution

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.

or use Sergei's answer which is even better..

Hi Sergei. This worked like a charm! Thanks so much!

Thank you Phillip. This works also. I appreciate the explanation. I have a better understanding now of how such statements work.

It is very efficient. I appreciate both answers!

You are welcome

1 best response

Accepted Solutions
best response confirmed by Laurie McDowell (Copper Contributor)
Solution

Hi Laurie,

 

You may use

=LOOKUP(P12,{0,5,10,15,21},{"0-4","5-9","10-14","15-19","20+"})

View solution in original post