Health Status/Rating calculated by Removing Specific Task names


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!





Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!