Nested IF - I am not getting a return value for the last IF statement in this formula.

All of the other IF statements return a value but the last one returns a blank. No error, just a blank. What is missing?

=IF([Goal Criteria]@row = "Solution is unclear; problems expected", 0, IF([Goal Criteria]@row = "Solution is known; some problems expected", 0.33, IF([Goal Criteria]@row = "More than 1 approach is available", 0.66, IF([Goal Criteria]@row = "Solution is well defined; no problems expected", 1))))

Thanks in advance for your help!

LaMima

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    Hi @LaMima Gilbert-Lewis,

    It looks like your formula is working fine:

    The reason "Anything else" (or any other undefined value) will give a blank is that you haven't defined a false result in your last IF section. For example if you wanted "No result" to appear instead:

    =IF([Goal Criteria]@row = "Solution is unclear; problems expected", 0, IF([Goal Criteria]@row = "Solution is known; some problems expected", 0.33, IF([Goal Criteria]@row = "More than 1 approach is available", 0.66, IF([Goal Criteria]@row = "Solution is well defined; no problems expected", 1, "No result"))))

    Gives:

    You can obviously use a different value or phrase there instead.

    Hope this helps, but if I've misunderstood something or you have any problems/questions, just post! πŸ™‚

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    Hi @LaMima Gilbert-Lewis,

    It looks like your formula is working fine:

    The reason "Anything else" (or any other undefined value) will give a blank is that you haven't defined a false result in your last IF section. For example if you wanted "No result" to appear instead:

    =IF([Goal Criteria]@row = "Solution is unclear; problems expected", 0, IF([Goal Criteria]@row = "Solution is known; some problems expected", 0.33, IF([Goal Criteria]@row = "More than 1 approach is available", 0.66, IF([Goal Criteria]@row = "Solution is well defined; no problems expected", 1, "No result"))))

    Gives:

    You can obviously use a different value or phrase there instead.

    Hope this helps, but if I've misunderstood something or you have any problems/questions, just post! πŸ™‚

  • LaMima Gilbert-Lewis
    LaMima Gilbert-Lewis ✭✭✭

    Ahhh, that makes sense. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!