IF(AND Nested Formulas
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 RiskLikely, Quite Likely, Unlikely]@row="U",[Impact of RiskMinor, Visible, Interruption]@row="M"),"5",IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="U",[Impact of RiskMinor, Visible, Interruption]@row="V"),"5",IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="U",[Impact of RiskMinor, Visible, Interruption]@row="I"),"3",IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="QL",[Impact of RiskMinor, Visible, Interruption]@row="M"),"5",IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="QL",[Impact of RiskMinor, Visible, Interruption]@row="V"),"4",IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="QL",[Impact of RiskMinor, Visible, Interruption]@row="I"),"2",IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="L",[Impact of RiskMinor, Visible, Interruption]@row="M"),"4",IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="L",[Impact of RiskMinor, Visible, Interruption]@row="V"),"3",IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="L",[Impact of RiskMinor, Visible, Interruption]@row="I"),"1","MISSINGVALUE")))))))))
This is the breakdown of the formula.
=
IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="U",[Impact of RiskMinor, Visible, Interruption]@row="M"),"5",
IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="U",[Impact of RiskMinor, Visible, Interruption]@row="V"),"5",
IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="U",[Impact of RiskMinor, Visible, Interruption]@row="I"),"3",
IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="QL",[Impact of RiskMinor, Visible, Interruption]@row="M"),"5",
IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="QL",[Impact of RiskMinor, Visible, Interruption]@row="V"),"4",
IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="QL",[Impact of RiskMinor, Visible, Interruption]@row="I"),"2",
IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="L",[Impact of RiskMinor, Visible, Interruption]@row="M"),"4",
IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="L",[Impact of RiskMinor, Visible, Interruption]@row="V"),"3",
IF(AND([Likelihood of RiskLikely, Quite Likely, Unlikely]@row="L",[Impact of RiskMinor, Visible, Interruption]@row="I"),"1",
"MISSINGVALUE")))))))))
Best Answer

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

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 RiskLikely, Quite Likely, Unlikely]@row="U",[Impact of RiskMinor, 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 
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!

No problem! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
Check out the Formula Handbook template!