RYGB Balls Formula
I'm using Smarthseet to manage a project work plan. I have used the formula below to automate RYGB balls.
=IF(AND([Target End Date]@row < TODAY(), NOT(ISBLANK([Target End Date]@row)), NOT(Status@row = "Complete"), NOT(ISBLANK(Status@row)), NOT(Counter@row > "0")), "Red", IF(AND([Target Start Date]@row < TODAY(), NOT(ISBLANK(Status@row)), NOT(Counter@row > "0")), IF(OR([% Complete]@row = "0", ISBLANK([% Complete]@row)), "Yellow", IF(AND([% Complete]@row > "0", [% Complete]@row < "1", NOT(ISBLANK(Status@row)), NOT(Counter@row > "0")), "Blue", IF(OR([% Complete]@row = "1", NOT(ISBLANK(Status@row)), Status@row = "Complete", NOT(Counter@row > "0")), "Green")))))
The fırmual is working well; however, I cannot see the green ball for one activity that I have completed before its Target start date. Any help?
Answers
-
I notice that you have quotation marks around the 0s and 1s in your formula. Can you clarify what the "Counter" column is used for?
If you're looking for numerical values, such as a percent in the percent complete column, or numbers in the Counter column, you'll actually want to add in the numbers without any quotes. Adding quotes to the formula turns the value into a text string instead of a number. See this Help Article: Create and Edit Formulas in Smartsheet
Try this:
=IF(AND([Target End Date]@row < TODAY(), NOT(ISBLANK([Target End Date]@row)), NOT(Status@row = "Complete"), NOT(ISBLANK(Status@row)), NOT(Counter@row > 0)), "Red", IF(AND([Target Start Date]@row < TODAY(), NOT(ISBLANK(Status@row)), NOT(Counter@row > 0)), IF(OR([% Complete]@row = 0, ISBLANK([% Complete]@row)), "Yellow", IF(AND([% Complete]@row > 0, [% Complete]@row < 1, NOT(ISBLANK(Status@row)), NOT(Counter@row > 0)), "Blue", IF(OR([% Complete]@row = 1, NOT(ISBLANK(Status@row)), Status@row = "Complete", NOT(Counter@row > 0)), "Green")))))
Did that work for you?
Cheers!
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!