# 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:

``````SWITCH({Probability}&{Impact},
"UnlikelyMinor","Green",
"UnlikelyModerate","Green",
"UnlikelySevere","Amber",
"PossibleMinor","Green",
"PossibleModerate","Amber",
"PossibleSevere","Red",
"LikelyMinor","Amber",
"LikelyModerate","Red",
"LikelySevere","Red",
"ERROR")
``````

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

Thank you Jim, that works a treat.

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