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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

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! 🙂
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!