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
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 150 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!