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

[ADProgrammes (Trevor)]

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.

[JCorrell (Jim - The Monday Man)]

@ADProgrammes

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

[ADProgrammes (Trevor)]

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.

[JCorrell (Jim - The Monday Man)]

@ADProgrammes

You’re welcome.

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

---

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