Health Status/Rating calculated by Removing Specific Task names

blee · 04/05/23

Hello Smartsheet Community,

My team and I are looking to improve a formula that helps to roll-up a score/health status of all of items within an activity plan, which is our RAID Log (Risk, Action, Issue, and Decision). We have a formula that calculates an aggregation/overall rating of all of the risks we have identified, with health status of 'Red', 'Yellow', and 'Green' based on a set criteria.

For help to the rest our staff, we provision this RAID Log for new projects with example risks, to provide a starting basis for them to fill it out. We set this risks with a certain score/health status and I want to remove these "<<Example>>" items from the overall rating calculation. I am thinking of adding a portion to the formula to calculate all of the risks IF(NOT(CONTAINS("<<Example>>").

Can anyone please suggest how to place this into the following formula we have? Is this possible? Below:

=IF(OR(AND(NOT(CONTAINS("Raid", Category@row)), [Item Name]@row <> "Overall Activity Risk"), AND(CONTAINS("Raid", Category@row), [Item Type]@row <> "Risk")), "", IF(Level@row = 0, "Risk Rating", IF(OR([Probability Rating]@row * [Impact Rating]@row >= 2.4, AND([Item Name]@row = "Overall Activity Risk", [Activity Risk]# >= 2.4)), "Red", IF(OR([Probability Rating]@row * [Impact Rating]@row > 0.9, AND([Item Name]@row = "Overall Activity Risk", [Activity Risk]# > 0.9)), "Yellow", "Green"))))

Thank you for the support!

Regards,

Ben

Paul Newcome · 04/06/23

Are you able to provide some screenshots for context?

blee · 04/06/23

https://community.smartsheet.com/discussion/comment/370636#Comment_370636

Thanks for reaching out @Paul Newcome! Yes, I can remove some project specific data and share them here.

The top image shows the items I am interested in, highlighted by the yellow lines. The second image displays the cell formula I am trying to manipulate ('Risk Rating' column, Row 27). I want to add a NOT(CONTAINS( function to this formula so that my example Risks, Action, Issues, and Decisions that contain <<Example>> text in the Item Description, do not have their Risk Scores counted towards the overall rating contained in the cell I stated above.

Is this possible to add a portion of the formula? I cannot nest this function in the IF statement already there, so I want to make sure my syntax works.

Thanks for the help!

blee · 04/07/23

https://community.smartsheet.com/discussion/comment/370636#Comment_370636

Thanks for reaching out @Paul Newcome! Yes, I have removed project specific data. Please see below:

The top image shows which columns, with yellow highlights, I am interested in. We have some hidden columns to the left that include other uses in this formula, like a 'Category' column. I am looking at the formula in the bottom image, that calculates for us all of the lines that have 'Risk' as the Item Type, which is generated from the Impact and Prob Rating we give to each identified risk. This creates score and we have assigned a 'Red', 'Yellow', or 'Green' indicator according to certain thresholds.

Is it possible to exclude items as risks, when the contain the text "<<Example>>" in the Description column, from the overall Risk Rating in "Risk Rating" column, Row 27? I am thinking about including a IF(NOT(CONTAINS or DISTINCT function to this formula but need help in nesting it.

Thanks for the support!

Paul Newcome · 04/07/23

Are you trying to just exclude it from the overall counts, or are you wanting the [Risk Rating] to be blank on rows that have <<Example>> in them?

blee · 04/07/23

https://community.smartsheet.com/discussion/comment/370811#Comment_370811

I am hoping to exclude them from the Overall Risk Rating which is captured in Row 27

blee · 04/10/23

Any note for follow up here @Paul Newcome? Any help you can provide is most appreciated!

blee · 04/10/23

This has been solved - thanks Smartsheet Community!

Health Status/Rating calculated by Removing Specific Task names

Answers

Help Article Resources

Categories