Using two text status columns to determine a third text status column

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.


This should do it for you:


Jim - The Monday Man
:magic_wand: Column Magic :sparkles:- the magical columns toolbox app

Thank you Jim, that works a treat. :smiling_face_with_three_hearts:

I’ve also experimented with swapping “green” etc with colour emojis for greater impact. Is there a way to set the whole colour of the cell in a formula? I’ve used the colour formatting tool, but the colours are very faint to the point of being ineffective, and lack the impact of other status columns. Thanks again.

1 Like


You’re welcome.

To maximize visibility, I would probably combine color emoji blocks with conditional coloring.

Jim - The Monday Man
:magic_wand: Column Magic :sparkles:- the magical columns toolbox app

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.