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

Find more posts tagged with

Accepted answers

Kelly Moore

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 Moore

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"))))))

Rachel Gadzicki

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.

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?

All comments

Kelly Moore

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).

I didn't do anything else with your formulas other than combine them as written.

Is this what you needed?

Kelly

Rachel Gadzicki

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

Kelly Moore

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

Kelly Moore

Hey

Try this

Rachel Gadzicki

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

Kelly Moore

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?

Rachel Gadzicki

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.

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?

Kelly Moore

Your screenshot shows the Due Date blank. The dates are filled out in that column, correct?

Rachel Gadzicki

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!

Kelly Moore

Phew! Glad it was that. I don't know how many times I've done something similar.

Always happy to help.

Kelly

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.