Health Status/Rating calculated by Removing Specific Task names

Options

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

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!