IF(AND Nested Formulas

Options

I need to each row to display a 'Score' based on results from:

Likelihood of Risk - Likely, Quite Likely, Unlikely

Impact of Risk - Minor, Visible, Interruption

I am receiving the following message with the below formula - #UNPARSEABLE

Any ideas?


=IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"5",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"3",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"4",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"2",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"4",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"3",IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"1","MISSINGVALUE")))))))))


This is the breakdown of the formula.

=

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"5",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"3",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"4",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="QL",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"2",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"4",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="V"),"3",

IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="L",[Impact of Risk-Minor, Visible, Interruption]@row="I"),"1",

"MISSINGVALUE")))))))))

Best Answer

  • Christal
    Christal
    Answer ✓
    Options

    Thanks! It was the space that was throwing off the formula.

    I just decided to take out the extra verbiage and spacing in the column names to simplify.

    Thanks again for your help!

Answers

  • Genevieve P.
    Options

    Hi @Christal

    I've tested your formula and the structure is correct, but it looks like you have the column names written slightly differently.

    In your screen capture, it looks like there is a space after the - between  Risk- Likely and Risk- Minor, however your formula there isn't a space:

    IF(AND([Likelihood of Risk-Likely, Quite Likely, Unlikely]@row="U",[Impact of Risk-Minor, Visible, Interruption]@row="M"),"5",

    Column names will need to be identical, including spaces, in order for the formula to know what column to look at. Try adding a space after the - and see if this resolves the formula!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Christal
    Christal
    Answer ✓
    Options

    Thanks! It was the space that was throwing off the formula.

    I just decided to take out the extra verbiage and spacing in the column names to simplify.

    Thanks again for your help!

  • Genevieve P.
    Options

    No problem! 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!