How to exclude specific statuses when automating RYG balls?

Options
Rashaun G
Rashaun G ✭✭✭
edited 09/22/21 in Formulas and Functions

Hello,

I am trying to automate row-level RYG balls based on date, percent complete, and excluding specific statuses from the automation. I am able to automate the RYG balls based on date and percent complete using the following formula:

=IF([Start Date]@row > TODAY(), "", IF([% Complete]@row < 0.5, "Red", IF([% Complete]@row < 0.75, "Yellow", "Green")))

However, I am unable to rework the formula to show no RYB ball when the status is either "On Hold" or "Cancelled."

I have tried using the following formula for example, and various iterations thereof including IF/ANDs, to no avail:

=IF([Start Date]@row > TODAY(), "", IF([% Complete]@row < 0.5, "Red", IF([% Complete]@row < 0.75, "Yellow", "Green", Status:Status, NOT(CONTAINS("On Hold", @cell), Status:Status, NOT(CONTAINS("Cancelled", @cell))))

I think I am on the right track with NOT/CONTAINS but, wonder if I am missing something in implementing its use.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!