Average Health (RYG) Column Formula
Hello!
I am working on a column formula (Column Type: Symbol, Symbol Value: RYG light) for a project plan sheet that will calculate task health based on a number of factors. The goal is to use this column formula in place of user intervention and to streamline health calculation across all projects based on set criteria. The formula is as follows:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNT(CHILDREN([Task Name]@row)) <> COUNTIFS(CHILDREN(), ISBLANK(@cell)), IF(OR(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green")), "Red", IF(COUNTIF(CHILDREN(), "Red") > 0, "Yellow", IF(OR(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow")), "Green", "Yellow")))), IF(ISBLANK([End Date]@row), "", IF(Status@row = "Complete", "Green", IF([End Date]@row < TODAY(), "Red", IF([At Risk]@row = 1, "Yellow", IF([End Date]@row > TODAY(), "Green", IF(OR([End Date]@row = TODAY(), [End Date]@row = TODAY(1)), "Yellow")))))))
It is designed to first check if the row is a parent row and calculate an "average" health based on its children. This allows us to add multiple hierarchies without having to worry about creating separate formulas for parent and child rows. If the row is not a parent row, the health is calculated based on date, task status, and risk status.
This formula has generally been successful, but I am running into a few issues with parent task health. There are two examples where I think the health is calculating incorrectly. I am unable to determine why. Below is a screenshot with examples of where I think the health is calculating incorrectly (see rows "Phase 3" and "Phase 4"); in both cases, I'm expecting the health to be yellow.
Phase 3: I'm expecting this to be yellow. I would assume this hits the IF(COUNTIF(CHILDREN(), "Red") > 0, "Yellow" condition because it does not meet the criteria of "Count Red Children > Yellow Children or Count Red Children > Green Children".
Phase 4: I'm expecting this to be yellow. I thought this would hit the "Otherwise Yellow" criteria in the IF(OR(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow")), "Green", "Yellow" condition. I don't see where this would meet any of the previous criteria.
Any insight would be greatly appreciated.
Best Answer
-
My apologies for not finishing out my last post. I am working with another user in another post where we are discussing the same exact logic.
I use if there is at least one red then red, if there is at least one yellow, then yellow, otherwise green. The only time it will ever show green on a parent row is if ALL child rows are green.
=IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", "Green"))
In response to your other quick question at the bottom of your last comment... Yes. Your IF/OR combo will account for that very specific scenario, but what about 3 green, 4 yellow, and 3 red? I would still want Red on my parent row so that I can see that I have children that are red, but the IF/OR wouldn't flag as true to output a red.
Or maybe 11 tasks. 1 green, 5 yellow, and 5 red. I feel like being able to flag that parent row as red would be pretty important since there are so many reds, but the IF/OR wouldn't catch that either.
Answers
-
Phase 3: It is triggering on this section to output the "Red":
COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"
Phase 4: It is triggering on this section to output the "Green":
COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")
Keep in mind... The fact that in Phase 3 there are no yellows does not mean it will skip over that argument. It means it will output a count of 0. Since there is one red (which is greater than zero), it is going to trigger "true" and output the "Red".
Same thought process for Phase 4. The 1 green is greater than the 0 reds, so it is going to output that green.
-
Thank you @Paul Newcome! This makes sense, and I wasn't considering the 0 count. Appreciate the explanation.
I am struggling to find the correct order/combination of conditions to get this functioning the way I expect.
Essentially, I want to adjust the parent formula to meet the following criteria:
-If the majority of children = Red, parent = Red
-If # Red children > 0, parent = Yellow (unless it meets the above condition) - I want this to cover the scenario where there are no yellow children, but at least 1 red.
-If the majority of children = Yellow, parent = Yellow
-If the majority of children = Green, parent = Green (unless there is at least one red, then parent = Yellow)
I have tried adding conditions and reordering things, but I'm still missing something. Do you have any suggestions on how to implement this? Or maybe there is a better approach I'm not considering.
-
What is your definitely of "Majority"?
Lets say you have 10 tasks.
3 are Green
3 are Yellow
4 are Red
Technically the majority is not red because the count of reds is not greater than 5.
Generally speaking, I always suggest a different logic for "rollups" up colors. If there is even one red, I suggest the parent row being red so that you know there is definitely something that requires your immediate attention within that section of tasks.
Lets go back to the previous example of 10 tasks, but this time we have
6 Green
4 Red
This would display as yellow. Seeing yellow on the parent row could be misleading because it doesn't tell you that almost half of your tasks are red. This wouldn't even trigger red until you hit 6 red and 4 green because even a 50/50 split still isn't a majority.
So my suggestion is:
1+ red(s) = red
1+ yellow(s) = yellow
Only green when all green.
-
Thanks for the insight, @Paul Newcome.
What if we switched around the conditions to do the following:
- IF(COUNTIF(CHILDREN(), "Red") > 0, "Red" - This would cover the "if there is even one red" scenario.
- IF(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), "Yellow", "Green") - This would cover the "more yellow than green = yellow" and "all green = green", right?
If that does work, I'm still messing up the syntax somewhere when I put it all together, because I keep getting "column formula syntax isn't quite right" error.
Regarding your "1+ red(s), yellow(s)" suggestion, can you clarify what you mean by this?
Also, quick other question, just for reference in writing future formulas - regarding your first example,
"10 tasks: 3 Green, 3 Yellow, 4 Red"
Wouldn't the IF(OR(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green")), "Red" address this scenario or am I missing where the sum comes in? I understand that this won't address the other situations you've described, just curious.
-
My apologies for not finishing out my last post. I am working with another user in another post where we are discussing the same exact logic.
I use if there is at least one red then red, if there is at least one yellow, then yellow, otherwise green. The only time it will ever show green on a parent row is if ALL child rows are green.
=IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", "Green"))
In response to your other quick question at the bottom of your last comment... Yes. Your IF/OR combo will account for that very specific scenario, but what about 3 green, 4 yellow, and 3 red? I would still want Red on my parent row so that I can see that I have children that are red, but the IF/OR wouldn't flag as true to output a red.
Or maybe 11 tasks. 1 green, 5 yellow, and 5 red. I feel like being able to flag that parent row as red would be pretty important since there are so many reds, but the IF/OR wouldn't catch that either.
-
@Paul Newcome apologies for not getting back to you sooner. This formula works perfectly. I agree with your logic and appreciate the help!
Help Article Resources
Categories
Check out the Formula Handbook template!