Project Health Formula #2

NCharleb

Team,

I think I accepted the answer too quickly to my question Project Health Formula answered by Kelly 12.1.2021.

Previous to the formula below first child row below was complete and second child row was cancelled (blank) making the parent row '"green". I thought the parent row should have read 'blue'

Now, when I change the second child row to force a green health status the Parent row shows complete. In this case it is not true. The parent row would be green - yes?

=IF(Status@row = "Complete", "Blue", IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), IF(OR(Status@row = "On Hold", Status@row = "Cancelled", Status@row = "Not Started", Status@row = "On Hold"), "", IF(OR(ISBLANK(Status@row), ISBLANK([Target End Date]@row)), "", IF(AND([Target End Date]@row <= TODAY(), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Red", IF(AND([Target End Date]@row <= TODAY(7), OR(Status@row <> "On Hold", Status@row <> "Cancelled", Status@row <> "Not Started", Status@row <> "Complete")), "Yellow", "Green"))))))

Kelly Moore

Hey @NCharleb

An IF statement moves along until it comes across a True statement. In the last post, we moved the Complete = Blue to the front of the statement to say, whenever, regardless if Child or Parent, the Status = Complete, the Health will turn Blue. The order of the clauses dictate the behavior of your result.

This means, as written, if you force the Parent row status to 'Complete', as written the formula will always force the Health balls to blue. Help me understand the criteria you're looking at and your expected color, and we'll work to get the formula correct for you. It sounds as though there might be an 'AND' that is not accounted for.

If I have a long, nested IF that I'm troubleshooting, I will sometimes paste it so I can read it more easily. Here is your formula, remembering that the formula will move along, exactly in this order, to completion. The formula looks for a Complete Status. The formula then asks if it is a parent row - and it goes through conditions. If none of those are true, it moves to Child row behaviors.

Kelly

NCharleb

Hi Kelly,

thank you so much for your patience. I've been told to hold off on this until the new year when we get the leads together for review. In that meeting I will 'hopefull' receive a consensus on a number of things including this formula. Can we pause for now and I will get back to you?

Kelly Moore

Absolutely. Just come back to this post and shout out when you're ready.