Multiple IF AND statements a cell

Options

Looking to take a screening sub-score that could range from 0 to 100 and assign another score to that based on ranges of the sub-score.

If subscore is greater than 0 and <=20, return "0"

If subscore is greater than 20 and <=40, return "1"

If subscore is greater than 40 and <= 60, return "2"

etc...

How do I write the formula for that? I can make each work individually but cannot successfully tie them all together in a single formula.


Thanks

Best Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Mike Ferringer ,

    Try this:

    =IF(AND([sub-score column name]@row>0, [sub-score column name]@row<=20),0,IF([sub-score column name]@row<=40,1,IF([sub-score column name]@row<=60,2,IF([sub-score column name]@row<=80,3,IF([sub-score column name]@row<=100,4,"")))))

    This translates to:

    If the sub-score is greater than 0 and less than or equal to 20, show 0. Otherwise, if it's less than or equal to 40, show 1. Otherwise, if it's less than or equal to 60, show 2. Otherwise, if it's less than or equal to 80, show 3. Otherwise, if it's less than or equal to 100, show 4. Otherwise (which really only leaves values less than or equal to zero, or more than 100), leave blank.


    Hope this helps! Let me know if it works.


    Best,

    Heather

  • Mike Ferringer
    Mike Ferringer ✭✭✭✭✭
    Answer ✓
    Options

    Worked perfectly Heather! Thank you for the quick reply!

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Mike Ferringer ,

    Try this:

    =IF(AND([sub-score column name]@row>0, [sub-score column name]@row<=20),0,IF([sub-score column name]@row<=40,1,IF([sub-score column name]@row<=60,2,IF([sub-score column name]@row<=80,3,IF([sub-score column name]@row<=100,4,"")))))

    This translates to:

    If the sub-score is greater than 0 and less than or equal to 20, show 0. Otherwise, if it's less than or equal to 40, show 1. Otherwise, if it's less than or equal to 60, show 2. Otherwise, if it's less than or equal to 80, show 3. Otherwise, if it's less than or equal to 100, show 4. Otherwise (which really only leaves values less than or equal to zero, or more than 100), leave blank.


    Hope this helps! Let me know if it works.


    Best,

    Heather

  • Mike Ferringer
    Mike Ferringer ✭✭✭✭✭
    Answer ✓
    Options

    Worked perfectly Heather! Thank you for the quick reply!

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    @Mike Ferringer Great! Glad it worked. Have a wonderful day.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!