IF Formula with multiple choices and values

I am trying to write a formula that will have the following conditions:

If the "Number of Affected Applications" equals 0 to 1, then the value in the CALC column would be "1"

If the "Number of Affected Applications" equals 2 to 4, then the value in the CALC column would be "2"

If the "Number of Affected Applications" equals 5 to 7, then the value in the CALC column would be "3"

If the "Number of Affected Applications" equals 8 to 9, then the value in the CALC column would be "4"

If the "Number of Affected Applications" equals 10 or more, then the value in the CALC column would be "5"


I had the formula working when it was only 1 condition, but adding these conditions, I can't figure out.

Best Answer

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Answer ✓

    @Katherine Simpson

    Good morning.

    Thank you for the clarification.

    Here is an updated formula:

    =IF([Number of Affected Applications]@row = "0 to 1", "1", IF([Number of Affected Applications]@row = "2 to 4", "2", IF([Number of Affected Applications]@row = "5 to 7", "3", IF([Number of Affected Applications]@row = "7 to 9", "4", "5"))))

    You do not need the "Error" statement in your formula above.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

Answers

  • Frank S.
    Frank S. ✭✭✭✭✭✭

    Greetings @Katherine Simpson,

    Here is an example of the formula:

    =IF(OR([Number of Affected Applications]@row >= 0, [Number of Affected Applications]@row >= 1), "1", IF(OR([Number of Affected Applications]@row >= 2, [Number of Affected Applications]@row >= 4), "2", IF(OR([Number of Affected Applications]@row >= 5, [Number of Affected Applications]@row >= 7), "3", IF(OR([Number of Affected Applications]@row >= 8, [Number of Affected Applications]@row >= 9), "4", "5"))

    So, if any of the four conditions are met, then the desired number otherwise 5.

    I hope this gets you on the right track.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭

    @Katherine Simpson

    If you are pulling both sets of data from the same sheet, the following would work:

    =IF([Number of Affected Applications]@row <= 1, 1, IF([Number of Affected Applications]@row <= 4, 2, IF([Number of Affected Applications]@row <= 7, 3, IF([Number of Affected Applications]@row <= 9, 4, 5))))

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • I should have stated that the "0 to 1" is an option, not numbers. How would that change the formula?

  • @Frank S. Can you please help me by modifying the formula you provided where the equals are text such as "0 to 1", etc. Not numbers?

  • @Frank S.

    I have this, but it isn't working.

    =IF([Number of Affected Applications]@row = "0 to 1", "1", "Error" IF([Number of Affected Applications]@row = "2 to 4", "2", "Error" IF([Number of Affected Applications]@row = "5 to 7", "3", "Error" IF([Number of Affected Applications]@row = "7 to 9", "4", "Error" IF([Number of Affected Applications]@row = "10 or more", "5", "Error")))))

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Answer ✓

    @Katherine Simpson

    Good morning.

    Thank you for the clarification.

    Here is an updated formula:

    =IF([Number of Affected Applications]@row = "0 to 1", "1", IF([Number of Affected Applications]@row = "2 to 4", "2", IF([Number of Affected Applications]@row = "5 to 7", "3", IF([Number of Affected Applications]@row = "7 to 9", "4", "5"))))

    You do not need the "Error" statement in your formula above.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • @Frank S.

    Thank you so much! That works perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!