Risk Level Formula

Hazel A.
Hazel A.
edited 12/09/19 in Smartsheet Basics

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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    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")))))))))

  • Hazel A.
    Hazel A.
    edited 03/19/18

    Hi Mike,

    Thanks for the correction! Formula works now :)