Automated risk rating

Bec Sullivan

Hi All,

I am building a risks log and I am getting stuck building 'if and' statements to automate the rating. I have a drop-down column for Likelihood and a drop-down column for Impact, and based on these two fields, it would be great if the rating column showed Low, Medium, High, or Very High according to the matrix below. It seems to work when I have the 5 if and statements for one group (very unlikely as an example), but then as I add the next round (unlikely for example), I get an error.

I have also tried to use workflows for this, but the problem is that workflows only allow for or statements, which doesn’t suit this purpose.

Any ideas on multiple ‘if and’ statements? Thank you in advance

Gillian C

Hi @Bec Sullivan

Try this arrangement of nest IF OR AND

=IF(OR(AND(Likelihood@row = "VL", Impact@row = "S"), AND(Likelihood@row = "VL", Impact@row = "Maj"), AND(Likelihood@row = "L", Impact@row = "S"), AND(Likelihood@row = "L", Impact@row = "Maj"), AND(Likelihood@row = "P", Impact@row = "S")), "Very High", IF(OR(AND(Likelihood@row = "VL", Impact@row = "Mod"), AND(Likelihood@row = "L", Impact@row = "Mod"), AND(Likelihood@row = "P", Impact@row = "Maj"), AND(Likelihood@row = "U", Impact@row = "Maj"), AND(Likelihood@row = "U", Impact@row = "S"), AND(Likelihood@row = "VU", Impact@row = "S")), "High", IF(OR(AND(Likelihood@row = "VL", Impact@row = "Min"), AND(Likelihood@row = "VL", Impact@row = "I"), AND(Likelihood@row = "L", Impact@row = "Min"), AND(Likelihood@row = "L", Impact@row = "I"), AND(Likelihood@row = "P", Impact@row = "Min"), AND(Likelihood@row = "P", Impact@row = "Mod"), AND(Likelihood@row = "U", Impact@row = "Mod"), AND(Likelihood@row = "VU", Impact@row = "Maj")), "Medium", IF(OR(AND(Likelihood@row = "P", Impact@row = "I"), AND(Likelihood@row = "U", Impact@row = "I"), AND(Likelihood@row = "U", Impact@row = "Min"), AND(Likelihood@row = "VU", Impact@row = "I"), AND(Likelihood@row = "VU", Impact@row = "Min"), AND(Likelihood@row = "VU", Impact@row = "Mod")), "Low", ""))))

Where Likelihood and Impact are the column names and can be replaced by whatever you have named them and the same for the dropdown values I've shortened them rather than having to type out Very Likely etc.

Basically the nested IF formula above is saying:

IF you have a combination of 'Very Likely and Severe', or 'Very Likely and Major', or 'Likely and Severe', or 'Likely and Major', or 'Probable and Severe' the formula will return "Very High", if not and you have a combination of 'Very Likely and Moderate', or 'Likely and Moderate', or 'Possible and Major', or 'Unlikely and Major', or 'Unlikely and Severe', or 'Very Unlikely and Severe' the formula will return "High", if not <rest of the formula follows a repeat of this pattern until you run out of risk categories and the last nested false statement is "" to return a blank cell if you have none of the above combinations.

Hope that helps?

Gillian C

Hi @Bec Sullivan

Try this arrangement of nest IF OR AND

=IF(OR(AND(Likelihood@row = "VL", Impact@row = "S"), AND(Likelihood@row = "VL", Impact@row = "Maj"), AND(Likelihood@row = "L", Impact@row = "S"), AND(Likelihood@row = "L", Impact@row = "Maj"), AND(Likelihood@row = "P", Impact@row = "S")), "Very High", IF(OR(AND(Likelihood@row = "VL", Impact@row = "Mod"), AND(Likelihood@row = "L", Impact@row = "Mod"), AND(Likelihood@row = "P", Impact@row = "Maj"), AND(Likelihood@row = "U", Impact@row = "Maj"), AND(Likelihood@row = "U", Impact@row = "S"), AND(Likelihood@row = "VU", Impact@row = "S")), "High", IF(OR(AND(Likelihood@row = "VL", Impact@row = "Min"), AND(Likelihood@row = "VL", Impact@row = "I"), AND(Likelihood@row = "L", Impact@row = "Min"), AND(Likelihood@row = "L", Impact@row = "I"), AND(Likelihood@row = "P", Impact@row = "Min"), AND(Likelihood@row = "P", Impact@row = "Mod"), AND(Likelihood@row = "U", Impact@row = "Mod"), AND(Likelihood@row = "VU", Impact@row = "Maj")), "Medium", IF(OR(AND(Likelihood@row = "P", Impact@row = "I"), AND(Likelihood@row = "U", Impact@row = "I"), AND(Likelihood@row = "U", Impact@row = "Min"), AND(Likelihood@row = "VU", Impact@row = "I"), AND(Likelihood@row = "VU", Impact@row = "Min"), AND(Likelihood@row = "VU", Impact@row = "Mod")), "Low", ""))))

Where Likelihood and Impact are the column names and can be replaced by whatever you have named them and the same for the dropdown values I've shortened them rather than having to type out Very Likely etc.

Basically the nested IF formula above is saying:

IF you have a combination of 'Very Likely and Severe', or 'Very Likely and Major', or 'Likely and Severe', or 'Likely and Major', or 'Probable and Severe' the formula will return "Very High", if not and you have a combination of 'Very Likely and Moderate', or 'Likely and Moderate', or 'Possible and Major', or 'Unlikely and Major', or 'Unlikely and Severe', or 'Very Unlikely and Severe' the formula will return "High", if not <rest of the formula follows a repeat of this pattern until you run out of risk categories and the last nested false statement is "" to return a blank cell if you have none of the above combinations.

Hope that helps?

Bec Sullivan

Thanks Gillian - that works perfectly!