Formula Question - Change Status based on RYGG Symbols

Erik B

Hello,

I've been working on trying to perfect this formula but getting stuck with the roll-up into the Parent Tasks Status and what the best formula would be to use in this case. I am trying to do a couple of things.

• Health Indicator Balls / Health Status
  • Gray / Planned
  • Red / At Risk
  • Yellow / Delayed
  • Green / On Track or Complete

The Health Indicator Balls are using a formula that currently uses the date and % completed to change to the appropriate colored ball. I am attempting to create a formula so when the health indicators balls change for parent/child, then the health status also updates. It seems simple, but the way I currently have it set-up seems to be working fine for the children status, but not so much for the parent status.

My Health Ball Formula is:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 0.5, "Red", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 1.5, "Yellow", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 2.5, "Green", "Gray")))), IF([% Completed]@row >= 1, "Green", IF(OR([Actual Start Date]@row > TODAY(), ISBLANK([Actual Start Date]@row)), "Gray", IF([Expected Finish Date]@row < TODAY(), "Red", IF([Expected Finish Date]@row < WORKDAY(TODAY(), 3), "Yellow", "Green")))))

My Health Status Formula is:

=IF(COUNT(CHILDREN(Health@row)) > 0, IF(COUNTIF(CHILDREN(Health@row), "Gray") = COUNT(CHILDREN(Health@row)), "Planned", IF((COUNTIF(CHILDREN(Health@row), "Yellow") + (COUNTIF(CHILDREN(Health@row), "Green") * 2)) / COUNTIF(CHILDREN(Health@row), <>"Gray") <= 0.5, "At Risk", IF((COUNTIF(CHILDREN(Health@row), "Yellow") + (COUNTIF(CHILDREN(Health@row), "Green") * 2)) / COUNTIF(CHILDREN(Health@row), <>"Gray") <= 1.5, "Delayed", IF((COUNTIF(CHILDREN(Health@row), "Yellow") + (COUNTIF(CHILDREN(Health@row), "Green") * 2)) / COUNTIF(CHILDREN(Health@row), <>"Gray") <= 2.5, "Complete", "Planned")))), IF([% Completed]@row >= 1, "Complete", IF(ISBLANK([Actual Start Date]@row), "Planned", IF([Actual Start Date]@row > TODAY(), "On Track", IF([Expected Finish Date]@row < TODAY(), "At Risk", IF([Expected Finish Date]@row < WORKDAY(TODAY(), 3), "Delayed", "On Track"))))))

As you can see from the picture, the children tasks are all working as expected, but looking at row 36 and 37 for example. If row 37 is On Track, then row 36 should not say complete.

Hoping someone can help me figure this out, if so I would be greatly appreciative.

Sameer Karkhanis

Since your Health column is already handling the logic for RGYG lights for parent/children can't you can simply rely on those and use the basic IFs for the Status column depending on the Health symbol colors?

=IF([Health]@row = "Gray", "Planned", IF([Health]@row = "Red", "At Risk", IF([Health]@row = "Yellow", "Delayed", IF([% Completed]@row = 1, "Completed", "On Track")))) 

Sameer Karkhanis

Since your Health column is already handling the logic for RGYG lights for parent/children can't you can simply rely on those and use the basic IFs for the Status column depending on the Health symbol colors?

=IF([Health]@row = "Gray", "Planned", IF([Health]@row = "Red", "At Risk", IF([Health]@row = "Yellow", "Delayed", IF([% Completed]@row = 1, "Completed", "On Track")))) 

Erik B

@Sameer Karkhanis thank you very much! This solved my issue. Turns out I was overthinking it. Appreciate it.

Sameer Karkhanis

Glad it helped