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 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
-
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 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
-
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! 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!