Risk Level Formula

Hazel A.

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!

 

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.

Hi Mike,

Thanks for the correction! Formula works now