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

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

Answers

  • Sing C
    Sing C ✭✭✭✭✭✭
    edited 10/07/22

    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, Ceridian

    LinkedIn

  • Emily S
    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
    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

    Sing Chen

    Process Architect, Ceridian

    LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!