Why doesn't my formula work?

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

Best Answers

  • Robyn Scott
    Answer ✓

    Thank you so much. It works!

Answers

  • Robyn Scott
    Answer ✓

    Thank you so much. It works!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!