Nested If & And error

Hello,

I'm relatively new to Smartsheet and am having an issue building a nested if/and function for one of my grids.

I have 2 columns ("Effort" & "Impact") and am trying to get 4 unique values based on the numbers -

If Effort@row >12.5 and Impact@row<45 show "Kill"

If Effort@row>12.5 and Impact@row>45 show "Challenge"

If Effort@row<12.5 and Impact@row<45 show "Possible"

If Effort@row<12.5 and Impact@row>45 show "Implement"


Any guidance on how to build this out?

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @JBS27

    You've done a good job so far! It's now just about tweaking the structure of your statements.

    For example, for this statement

    If Effort@row >12.5 and Impact@row<45 show "Kill"

    use this:

    =IF(AND(Effort@row >12.5, Impact@row < 45), "Kill",


    Full formula:

    =IF(AND(Effort@row >12.5, Impact@row < 45), "Kill", IF(AND(Effort@row > 12.5, Impact@row > 45), "Challenge", IF(AND(Effort@row < 12.5, Impact@row < 45), "Possible", IF(AND(Effort@row < 12.5, Impact@row > 45), "Implement"))))

    We could tweak it a bit to make it more concise, but the above formula is a direct translation of your four statements so it makes the most logical sense for me.

    Let me know if I can clarify anything further!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @JBS27

    You've done a good job so far! It's now just about tweaking the structure of your statements.

    For example, for this statement

    If Effort@row >12.5 and Impact@row<45 show "Kill"

    use this:

    =IF(AND(Effort@row >12.5, Impact@row < 45), "Kill",


    Full formula:

    =IF(AND(Effort@row >12.5, Impact@row < 45), "Kill", IF(AND(Effort@row > 12.5, Impact@row > 45), "Challenge", IF(AND(Effort@row < 12.5, Impact@row < 45), "Possible", IF(AND(Effort@row < 12.5, Impact@row > 45), "Implement"))))

    We could tweak it a bit to make it more concise, but the above formula is a direct translation of your four statements so it makes the most logical sense for me.

    Let me know if I can clarify anything further!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!