Help with Health color formula
I need help with my Health column formulas. I cannot figure out why it's not working. What am I missing?
Available health options: Red, Yellow, Green, Gray
Health formula:
=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", "Green")), IF(AND(ISBLANK([End Date]@row), ISBLANK(Status@row)), " ", IF(OR(Status@row = "Complete", [End Date]@row >= TODAY(7)), "Green", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Canceled", [End Date]@row <= TODAY(7), [End Date]@row >= TODAY(1)), "Yellow", IF(AND(Status@row <> "Complete", Status@row <> "On Hold", Status@row <> "Canceled", [End Date]@row <= TODAY(-1)), "Red", IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray"))))))
What I'm looking for: I want the parent health formula to roll up the overall health. As you can see in my example, the Health is Red, even though everything should be considered Completed (including canceled activities).
Answers
-
If you want to roll up the over all status. How about assigning a point total to each of the colors. Then taking an average of the health scores. giving the parent row a health score based off of the average.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
There is alot going on in your formula. Is the parent status only dependent on the status of the children? I would assume one formula would be used to assign status of child then this would roll up into a parent status. Then you wouldn't need [End Date] at all and you'll have a much simpler formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!