Column Formula for Health with check for Parent

Rachel Gadzicki
Rachel Gadzicki ✭✭
in Smartsheet Basics

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

smartsheet question.PNG


· Share on FacebookShare on Twitter

Best Answers

  • Rachel Gadzicki
    Rachel Gadzicki ✭✭
    Answer ✓

    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?

    · Share on FacebookShare on Twitter

Answers

  • Rachel Gadzicki
    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:

    smartsheet 1.PNG

    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

    · Share on FacebookShare on Twitter
  • Kelly Moore
    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

    · Share on FacebookShare on Twitter
  • Rachel Gadzicki
    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

    · Share on FacebookShare on Twitter
  • Kelly Moore
    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?

    · Share on FacebookShare on Twitter
  • Rachel Gadzicki
    Rachel Gadzicki ✭✭
    Answer ✓

    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?

    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

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

    · Share on FacebookShare on Twitter
  • Rachel Gadzicki
    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!

    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

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

    Always happy to help.

    Kelly

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video