Project Health Formula #2
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"))))))
Answers
-
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
-
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?
-
Absolutely. Just come back to this post and shout out when you're ready.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!