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

Options
✭✭

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

• ✭✭✭✭✭✭
edited 10/07/22
Options

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

Sing Chen

Process Architect, Dayforce

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Sing Chen

Process Architect, Dayforce