Advice on the best way to calculate overall rating in risk and issue log

Emily S

Hi Smartsheet Community,

Looking for advice on the best way to create an issue and risk log.

I have a column called impact which has 5 options in the drop list and another column which is called priority which has 5 different options in a drop-down list. I would then like the 3rd column to calculate the overall rating based on what is selected in columns 1 and 2. For example, if the impact is insignificant but the priority is High what would the rating equal out of the following options: low, medium, high, critical.

I thought about using a formula or the automation function... but not sure the best approach.

Thanks

Sing C

Hi @Emily S,

We have something similar for our risk log; two columns Likelihood and Impact, with a third column being Severity being derived from a combination of the first two columns.

• Likelihood: Improbable, Possible, Probable
• Impact: Acceptable, Tolerable, Undesirable, Intolerable

And we have a formula for calculating Severity, which can be one of the following: Low, Medium, High, Very High

Once we defined the list of values for each of the three columns, we mapped out the combinations of Likelihood and Impact and made a decision about the Severity level that we wanted each combination to lead to. This was purely a business decision. We started with the 'extremes', Improbable Likelihood and Acceptable Impact = Low; Probable Likelihood and Intolerable Impact = Very High and all other combinations fell in between.

Happy to provide the formula if that will help.

Let me know if that helps.

Thanks,

Sing

Emily S

Hi Sing,

Thanks so much for your reply. Yes, that helps and if you could provide me with the formula you used that would be great!

Thanks,

Emily

Sing C

Hi @Emily S,

Of course. Here is the formula we have:

=IFERROR(IF(Summary@row = 0, IF(AND(OR(Likelihood@row = "Improbable", Likelihood@row = "Possible"), Impact@row = "Acceptable"), "Low", IF(AND(Likelihood@row = "Probable", Impact@row = "Acceptable"), "Medium", IF(AND(OR(Likelihood@row = "Improbable", Likelihood@row = "Possible"), Impact@row = "Tolerable"), "Medium", IF(AND(Likelihood@row = "Probable", Impact@row = "Tolerable"), "High", IF(AND(Likelihood@row = "Improbable", Impact@row = "Undesirable"), "Medium", IF(AND(OR(Likelihood@row = "Probable", Likelihood@row = "Possible"), Impact@row = "Undesirable"), "High", IF(AND(Likelihood@row = "Improbable", Impact@row = "Intolerable"), "High", "Very High"))))))), ""), "")

Let me know if that helps.

Thanks,

Sing