I want to optimise a Risk Register template so that the 9 potential status combinations from two columns (Probability and Impact) generates a third status in the RAG Rating column.

The two columns and their statuses are:

Probability - Unlikely, Possible, Likely

Impact - Low, Moderate, Severe

The formula column will show the RAG rating (Green, Amber or Red) and this is the formula I’ve been trying out:

IF(AND({Probability}=“Unlikely”,{Impact}=“Minor”,“Green”, IF(AND({Probability}=“Unlikely”,{Impact}=“Moderate”,“Green”,IF(AND({Probability}=“Unlikely”,{Impact}=“Severe”,“Amber”,IF(AND({Probability}=“Possible”,{Impact}=“Minor”,“Green”, IF(AND({Probability}=“Possible”,{Impact}=“Moderate”,“Amber”,IF(AND({Probability}=“Possible”,{Impact}=“Severe”,“Red”,IF(AND({Probability}=“Likely”,{Impact}=“Minor”,“Amber”, IF(AND({Probability}=“Likely”,{Impact}=“Moderate”,“Red”,IF(AND({Probability}=“Likely”,{Impact}=“Severe”,“Red”,))))))))))))))))))

Thus, when Probability is Unlikely and Impact is Minor, the RAG rating should be Green. But the formula is not working at all.

I also tried a variation of this solution: Formula for status columns field values to provide a summary of risk.

I changed all the statuses to Low, Medium, High in both columns, started with Low, Low = Green, Find Medium = Amber, Find High = Red, but again no luck. Any solutions to this would be most welcome. Thanks everyone.