How to exclude specific statuses when automating RYG balls?

Rashaun G

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.

Kelly Moore

Hey @Rashaun G

Here's one approach

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

The IF/AND has the same syntax as the IF/OR.

https://help.smartsheet.com/function/or

Kelly Moore

Hey @Rashaun G

Here's one approach

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

The IF/AND has the same syntax as the IF/OR.

https://help.smartsheet.com/function/or

Rashaun G

Thank you so much Kelly!!!! That did the trick and I like how that formula is set up including an automation for when the task is completed. I also got better insight into using "" to enter a blank/nothing.

Many thanks again.

Rashaun