# How to exclude specific statuses when automating RYG balls?

Options
✭✭✭
edited 09/22/21

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!