Risk Level Formula
Hello,
I am trying to calculate risk level automatically (by displaying RYG balls) based on the following:
Risk Level is Green If I get one of the following combinations:
 Impact = Low AND Probability = Not Likely
 Impact = Low AND Probability = Likely
 Impact = Med AND Probability = Not Likely
Risk Level is Yellow If I get one of the following combinations:
 Impact = Low AND Probability = Very Likely
 Impact = Med AND Probability = Likely
 Impact = High AND Probability = Not Likely
Risk Level is Red If I get one of the following combinations:
 Impact = Med AND Probability = Very Likely
 Impact = High AND Probability = Likely
 Impact = High AND Probability = Very Likely
I tried inputting the formula below. Not really sure how to close an IF AND Formula, I also think my logic is wrong in some parts. I got "Unparseable" as an error.
=IF(AND(Impact2 = "Low", Probability2 = "Not Likely", Level2 = "Green", IF(AND(Impact2 = "Low", Probability2 = "Likely", Level2 = "Green", IF(AND(Impact2 = "Low", Probability2 = "Very Likely", Level2 = "Green", IF(AND(Impact2 = "Med", Probability2 = "Not Likely", Level2 = "Green", IF(AND(Impact 2 = "Med", Probability2 = "Likely", Level2 = "Yellow", IF(AND(Impact2 = "Med", Probability2 = "Very Likely", Level2 = "Red", IF(AND(Impact2 = "High", Probability2 = "Not Likely", Level2 = "Yellow", IF(AND(Impact2 = "High", Probability2 = "Likely", Level2 = "Red", IF(AND(Impact2 = "High", Probability2 = "Very Likely", Level2 = "Red", "Yellow")))))))))))
I have one column for Impact, another column for probability, and a third column for Risk Level.
Thank you in advance for the help!
Comments

You're close... but if you put this formula in the level 2 column cell then you don't need to state Level 2 =...
Also.. An AND statement needs to be closed within the IF statement it resides. Try this.
=IF(AND(Impact2 = "Low", Probability2 = "Not Likely"), "Green", IF(AND(Impact2 = "Low", Probability2 = "Likely"), "Green", IF(AND(Impact2 = "Low", Probability2 = "Very Likely"), "Green", IF(AND(Impact2 = "Med", Probability2 = "Not Likely"), "Green", IF(AND(Impact 2 = "Med", Probability2 = "Likely"), "Yellow", IF(AND(Impact2 = "Med", Probability2 = "Very Likely"), "Red", IF(AND(Impact2 = "High", Probability2 = "Not Likely"), "Yellow", IF(AND(Impact2 = "High", Probability2 = "Likely"), "Red", IF(AND(Impact2 = "High", Probability2 = "Very Likely"), "Red", "Yellow")))))))))

Hi Mike,
Thanks for the correction! Formula works now
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives