Project Health Formula Help

Jessica Takata

Hello,

I've been struggling with this formula. I borrowed parts from other project tracking templates, but I can't seem to get everything to work together; I keep getting error messages. I'm looking to:

• Project Health turns "Green" for any row that has a Status of "Complete" or if the Target End Date is 1 week out or further
• Project Health turns "Yellow" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Target End Date is within the next 7 days
• Project Health turns "Gray" for any row that has a Status of "On Hold" or "Canceled"
• Project Health turns "Red" for any row that does not have a Status of "Complete", "On Hold", or "Canceled", and the Target End Date is within the next 3 days or is in the past
• Project Health is “blank” for any row where the Status and Target End Date cells are empty
• Additionally, if the row has a hierarchy of “1” it should show the average health of its children tasks.

=IF(Hierarchy@row = 1, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Red") = 0, COUNTIF(CHILDREN(), "Yellow") > 0), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Yellow") = 0, COUNTIF(CHILDREN(), "Green") > 0), "Green", "Gray"))),=IF(Status@row = "Complete", "Green", IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray",IF(ISBLANK(Status@row),””,IF(ISBLANK([Target End Date]),””, IF([Target End Date]@row< TODAY(3), "Red", IF([Target End Date]@row <= TODAY(7), "Yellow", "Green"))))

TIA for any help – I’m so formula illiterate.

Thank you! 

Paul Newcome

This should fix that...

=IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "", [Target End Date]@row <> ""), IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green")> 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row<= TODAY(3), "Red", IF([Target End Date]@row<= TODAY(7), "Yellow", "Green")))))))

Paul Newcome

You are actually pretty close. Lets give this a whirl...

=IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green")> 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row<= TODAY(3), "Red", IF([Target End Date]@row<= TODAY(7), "Yellow", "Green")))))

Jessica Takata

Thank you, Paul! I'm no longer getting the error message and it appears to be working as intended. :)

One last question: is there a way to tell the cell to appear "blank" if the Status and Target End Date cells are empty? It's just so I can copy down the formula for future entries without having a column of red circles.

Thank you!

Paul Newcome

That is tucked away on the child row portion which should be feeding up to the parent rows. Is that not working?

Jessica Takata

It doesn't look like it, but I could be doing something incorrectly? I added the formula to the first cell under the Health column, and then I copied it down the column. The health circle in the blank rows is appearing as red...

Paul Newcome

Try this one...

=IF(AND(Status@row <> "", [Target End Date]@row <> ""), IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green")> 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row<= TODAY(3), "Red", IF([Target End Date]@row<= TODAY(7), "Yellow", "Green"))))))

Jessica Takata

That fixed the blank cell issue, thank you! I did notice that the cells with a Status of "Complete" but no Target End Date appear blank now?

I'm sorry - I feel like it's so close now...

Thank you for the help!

Paul Newcome

This should fix that...

=IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "", [Target End Date]@row <> ""), IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green")> 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row<= TODAY(3), "Red", IF([Target End Date]@row<= TODAY(7), "Yellow", "Green")))))))

Jessica Takata

For some reason, the On Hold and Canceled statuses were affected (not showing Gray anymore), but I just added that part to the beginning, and that seemed to solve the issue.

I ended up with this, and it appears to be working!

=IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF(AND(Status@row <> "", [Target End Date]@row <> ""), IF(Hierarchy@row = 1, IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Green") > 0, "Green", "Gray"))), IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(Status@row = "Complete", "Green", IF([Target End Date]@row <= TODAY(3), "Red", IF([Target End Date]@row <= TODAY(7), "Yellow", "Green"))))))))

Thank you very much for all of the help!

Paul Newcome

Glad you were able to get it working. Happy to help. 👍️