Why doesn't my formula work?

Robyn Scott

I have created a sheet using the Risk Assessment Matrix template as a guide. I want a formula that auto populates the risk rating based on likelihood and severity.

=IF(AND(OR(Likelihood@row = "Possible", Likelihood@row = "Unlikely", Likelihood@row = "Rare"), Severity@row = "Minimal", Severity@row = "Minor"), "Low", IF(AND(OR(Likelihood@row = "Likely", Likelihood@row = "Possible", Likelihood@row = "Unlikely", Likelihood@row = "Rare"), Severity@row = "Minimal", Severity@row = "Minor", Severity@row = "Moderate"), "Medium", IF(AND(OR(Likelihood@row = "Almost Certain", Likelihood@row = "Likely", Likelihood@row = "Possible", Likelihood@row = "Unlikely", Likelihood@row = "Rare"), Severity@row = "Minimal", Severity@row = "Minor"), "High", "Extreme")))

this is the formula that I have tried and it just returns everything as Extreme

I think there is an issue with the severity having more than one option. I am making this assumption on the basis of the template formula having multiple likelihood options and only one severity option. I tried taking out the multiple severity options and then the formula worked so I assume I have to put something in to recognise that the severity can be either option I just don't understand what. Everything I have tried comes up unparseable!

Happy for any advice.

Regards,

Robyn

Purnima Gore

Hi @Robyn Scott

I had a look and I think part of the problem is there is an OR missing and you are duplicating the combinations.

=IF(AND(OR(Severity@row = "Minimal", Severity@row = "Minor"),

OR(Likelihood@row = "Possible", Likelihood@row = "Rare", Likelihood@row = "Unlikely")),

"Low",  "Extreme"

is the basic formula for the first set of possibilities

For the one for Medium you have written this

IF(AND(OR(Likelihood@row = "Likely", Likelihood@row = "Possible", Likelihood@row = "Unlikely", Likelihood@row = "Rare"),

Severity@row = "Minimal", Severity@row = "Minor", Severity@row = "Moderate"), "Medium",

Hope that helps

ATB

Purnima

Robyn Scott

Thank you so much. It works!

Purnima Gore

Hi @Robyn Scott

I had a look and I think part of the problem is there is an OR missing and you are duplicating the combinations.

=IF(AND(OR(Severity@row = "Minimal", Severity@row = "Minor"),

OR(Likelihood@row = "Possible", Likelihood@row = "Rare", Likelihood@row = "Unlikely")),

"Low",  "Extreme"

is the basic formula for the first set of possibilities

For the one for Medium you have written this

IF(AND(OR(Likelihood@row = "Likely", Likelihood@row = "Possible", Likelihood@row = "Unlikely", Likelihood@row = "Rare"),

Severity@row = "Minimal", Severity@row = "Minor", Severity@row = "Moderate"), "Medium",

Hope that helps

ATB

Purnima

Robyn Scott

Thank you so much. It works!