Hi - I am using health status which is determined by project status which is in turn determined by a planned vs actual task progress.
I need the the health status to roll up to the parent row since I can't get an accurate weighted planned % complete in the parent row.
I am using the following formula in child tasks health status:
Delayed = yellow
Off Track or Overdue = Red
Completed or In Progress = Green
On Hold or Not Started = Gray
=IF(Status@row = "Delayed", "Yellow", IF(OR(Status@row = "Off Track", Status@row = "Overdue"), "Red", IF(OR(Status@row = "Completed", Status@row = "In Progress"), "Green", IF(OR(Status@row = "On hold", Status@row = "Not Started"), "Gray"))))
I also have a formula for parent rows:
More than 1 red = red
1 red = yellow
more than 1 yellow = yellow
Else = green
=IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green")))
What I need is a formula that puts this all together so that it can be used in project plan template. I also have column to look for hierarchy for a variety of reasons and not sure if that is a good data point to look for. I have tried several variations with my latest being the following but it does not work.
=IF(Hierarchy@row > 0, (COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green", IF(Status@row = "Delayed", "Yellow", IF(OR(Status@row = "Off Track", Status@row = "Overdue"), "Red", IF(OR(Status@row = "Completed", Status@row = "In Progress"), "Green", IF(OR(Status@row = "On hold", Status@row = "Not Started"), "Gray")))))
