Forum Discussion

Laurie McDowell's avatar
Laurie McDowell
Copper Contributor
Aug 16, 2018
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...
excel
Formulas and Functions
Nested If statement
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 16, 2018

    Hi Laurie,

     

    You may use

    =LOOKUP(P12,{0,5,10,15,21},{"0-4","5-9","10-14","15-19","20+"})
Philip West's avatar
Philip West
Iron Contributor
Aug 16, 2018

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.

  • Laurie McDowell's avatar
    Laurie McDowell
    Copper Contributor
    Aug 17, 2018

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

  • Philip West's avatar
    Philip West
    Iron Contributor
    Aug 16, 2018

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

    • Laurie McDowell's avatar
      Laurie McDowell
      Copper Contributor
      Aug 17, 2018

      It is very efficient. I appreciate both answers!

Resources