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

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.