I need help with an IF(AND..IF(AND.. formula that I'm getting an #UNPARSEABLE error on.
I'm trying to replicate a risk rating matrix (image included) by taking the data in 2 cells (Risk Likelihood & Risk Impact) to auto-populate a third cell (Risk Rating). Below is the formula that I am using.
=IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Insignificant"), " Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Insignificant"), "Moderate", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Minor"), " Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Minor"), "Low", IF(AND(([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Minor"), "Moderate", IF(AND(([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Moderate"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Moderate"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Moderate"), "Moderate", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Moderate"), "High", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Moderate"), "High", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Major"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Major"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Major"), "High", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Major"), "High", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Major"), "Extreme", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Catastrophic"), "Moderate", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Catastrophic"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Catastrophic"), "High", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Catastrophic"), "Extreme", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Catastrophic"), "Extreme"))))))))))))))))))))))))))
Any ideas where I'm going wrong?
Or if there is a shorter, easier way to do this, I would welcome that too!
Best Answer
-
Works perfectly now. Thank you Dan, greatly appreciated 🤩
Answers
-
You had a few extra ( in two spots.
Here is the fixed version. Hope this helps.
=IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Insignificant"), " Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Insignificant"), "Low", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Insignificant"), "Moderate", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Minor"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Minor"), "Low", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Minor"), "Moderate", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Moderate"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Moderate"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Moderate"), "Moderate", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Moderate"), "High", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Moderate"), "High", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Major"), "Low", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Major"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Major"), "High", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Major"), "High", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Major"), "Extreme", IF(AND([Risk Likelihood]@row = "Rare", [Risk Impact]@row = "Catastrophic"), "Moderate", IF(AND([Risk Likelihood]@row = "Unlikely", [Risk Impact]@row = "Catastrophic"), "Moderate", IF(AND([Risk Likelihood]@row = "Possible", [Risk Impact]@row = "Catastrophic"), "High", IF(AND([Risk Likelihood]@row = "Likely", [Risk Impact]@row = "Catastrophic"), "Extreme", IF(AND([Risk Likelihood]@row = "Almost Certain", [Risk Impact]@row = "Catastrophic"), "Extreme")))))))))))))))))))))))))
-
Works perfectly now. Thank you Dan, greatly appreciated 🤩
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!