Column Formula for Health with check for Parent
I am using the following formula to drive the RYG ball color in a Health column.
The color reflected in the Health column is driven by a combination of "Complete" (checked boxes) and "Due Date" data.
GREEN = Complete and/or Due Date = T+2
YELLOW = Incomplete and Due Date = T or T+1
RED = Incomplete and Due Date = T-1 or DNE
Here is the formula I am using:
=IF(ISBLANK([Due Date]@row), "", IF(Complete@row = 1, "Green", IF([Due Date]@row > TODAY(1), "Green", IF(OR([Due Date]@row = TODAY(), [Due Date]@row = TODAY(1)), "Yellow", IF([Due Date]@row < TODAY(), "Red")))))
I'd like to be able to convert this to a Column Formula, but want to consider creating an "average" health when a hierarchy comes into play.
I am using this formula to calculate the average health of the child rows that are using the aforementioned health formula:
=IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))
Is there a way that I can combine the two formulas (or create a new formula) for the entire column that will incorporate an initial check to see if the row is a parent and if so, calculate the average of it's below children? Would this work if multiple hierarchies are added to a sheet? I've included an image for reference.
Any help would be greatly appreciated!
Thank you,
Rachel
Best Answers
-
Hi Rachel,
I added an IF at the beginning to check if the row is a parent row (count of children>0). If you chose to, you could put the parent formula in a helper column (like a checkbox column).
The formula below will work if multiple hierarchies exist - in your screenshot example, the Milestone parent (row3) would 'average' the 3 task children. The Project 'parent' would average the child Milestone (row2) and the parent Milestone (row3) since both row2 and row3 are children to row1.
I didn't do anything else with your formulas other than combine them as written.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow")), IF(ISBLANK([Due Date]@row), "", IF(Complete@row = 1, "Green", IF([Due Date]@row > TODAY(1), "Green", IF(OR([Due Date]@row = TODAY(), [Due Date]@row = TODAY(1)), "Yellow", IF([Due Date]@row < TODAY(), "Red"))))))
Is this what you needed?
Kelly
-
Hey
Try this
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNT(CHILDREN([Task Name]@row)) <> COUNTIFS(CHILDREN(), ISBLANK(@cell)), IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))), IF(ISBLANK([Due Date]@row), "", IF(Complete@row = 1, "Green", IF([Due Date]@row > TODAY(1), "Green", IF(OR([Due Date]@row = TODAY(), [Due Date]@row = TODAY(1)), "Yellow", IF([Due Date]@row < TODAY(), "Red"))))))
-
Sorry for the delay in getting back to you @KDM , I missed your update.
I do see the missing [Task Name]@row in the Count equation in the first one, but I used the long equation when testing.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNT(CHILDREN([Task Name]@row)) <> COUNTIFS(CHILDREN(), ISBLANK(@cell)), IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))), IF(ISBLANK([Due Date]@row), "", IF(Complete@row = 1, "Green", IF([Due Date]@row > TODAY(1), "Green", IF(OR([Due Date]@row = TODAY(), [Due Date]@row = TODAY(1)), "Yellow", IF([Due Date]@row < TODAY(), "Red"))))))
This still seems to remove the health from all rows. The children health is no longer calculating based on due date and completeness; all cells are now blank.
Any other thoughts?
Answers
-
Hi Rachel,
I added an IF at the beginning to check if the row is a parent row (count of children>0). If you chose to, you could put the parent formula in a helper column (like a checkbox column).
The formula below will work if multiple hierarchies exist - in your screenshot example, the Milestone parent (row3) would 'average' the 3 task children. The Project 'parent' would average the child Milestone (row2) and the parent Milestone (row3) since both row2 and row3 are children to row1.
I didn't do anything else with your formulas other than combine them as written.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow")), IF(ISBLANK([Due Date]@row), "", IF(Complete@row = 1, "Green", IF([Due Date]@row > TODAY(1), "Green", IF(OR([Due Date]@row = TODAY(), [Due Date]@row = TODAY(1)), "Yellow", IF([Due Date]@row < TODAY(), "Red"))))))
Is this what you needed?
Kelly
-
Kelly,
Thank you so much for your explanation. I wanted to make sure I used the proper format when combining the two statements. One follow-up question: when there is no health data for the children, I want the parent to be blank. Currently this is showing as yellow:
If there is at least 1 child row with health, I want the parent to still take the "average," but if all children have the health cell as blank, I'd like the parent to be blank. Would I use an IF(ISBLANK) type statement for that? I'm new to formula writing, so apologies if this is super simple.
Really appreciate your help!
Thank you,
Rachel
-
Hey Rachel,
Sorry - a busy day of meetings.
This is what I came up with - test it to see if it's doing what you want it to do. The question I mulled all day was your "all children have the health cell as blank". We needed to figure out how to determine the 'all' part. This is what I've come up with so far.
IF(COUNT(CHILDREN()) <> COUNTIFS(CHILDREN(), ISBLANK(@cell))
My thought was this could be inserted right after the 1st if that checks if the row is a parent row. Written this way should mean the answer, when true, flows right into the existing formula. But I don't think it's quite right. I wanted to let you know I hadn't forgotten you - still working it.
Kelly
-
Hey
Try this
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNT(CHILDREN([Task Name]@row)) <> COUNTIFS(CHILDREN(), ISBLANK(@cell)), IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))), IF(ISBLANK([Due Date]@row), "", IF(Complete@row = 1, "Green", IF([Due Date]@row > TODAY(1), "Green", IF(OR([Due Date]@row = TODAY(), [Due Date]@row = TODAY(1)), "Yellow", IF([Due Date]@row < TODAY(), "Red"))))))
-
Good morning @KDM ,
Thank you so much for working through this with me. Unfortunately, I was unable to get this to function properly. The parent health did change to blank when I added the new column formula, however, the child health is not calculating correctly anymore - all cells are now blank regardless of due date and complete checked/unchecked.
Any thoughts?
Thank you,
Rachel
-
Hmmm. Are you using the formula I sent last (the long equation)- or did you type in my first suggestion? I caught a mistake in the first one. If using that one, add the [Task Name]@row to the Count. See it?
-
Sorry for the delay in getting back to you @KDM , I missed your update.
I do see the missing [Task Name]@row in the Count equation in the first one, but I used the long equation when testing.
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNT(CHILDREN([Task Name]@row)) <> COUNTIFS(CHILDREN(), ISBLANK(@cell)), IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))), IF(ISBLANK([Due Date]@row), "", IF(Complete@row = 1, "Green", IF([Due Date]@row > TODAY(1), "Green", IF(OR([Due Date]@row = TODAY(), [Due Date]@row = TODAY(1)), "Yellow", IF([Due Date]@row < TODAY(), "Red"))))))
This still seems to remove the health from all rows. The children health is no longer calculating based on due date and completeness; all cells are now blank.
Any other thoughts?
-
Your screenshot shows the Due Date blank. The dates are filled out in that column, correct?
-
My apologies @KDM, you are absolutely right that this is working correctly. I added a new column for Start Date next to my Due Date field and got them switched up when I was testing this (which is obviously why I was getting blanks in my cells). So sorry for missing that. You have been extremely helpful, thank you so much!
-
Phew! Glad it was that. I don't know how many times I've done something similar.
Always happy to help.
Kelly
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives