Help with Health color formula

DVELASQUEZ

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).

Mark.poole

@DVELASQUEZ

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.

sfcrews

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.