Project Health calculation with parent/child rows

Hoping to get some help with calculating health of a project. Here are my parameters:

Green: Status column is "Complete" or "In Process" or the Finish date is 7 days out or further.

Yellow: Status column is not "Complete", "On Hold" or "Cancelled", and the Finish is up to 1 day in the past.

Red: Status column is not "Complete", "On Hold" or "Cancelled", and the Finish was more than 1 day in the past.

Gray: Status column is "On Hold" or "Cancelled"

Blank: Health column is blank if the Status column and Target Finish columns are both empty.

If it's a parent row it shows a red health if any of the children rows under it have a red health, yellow health if any of the children rows show yellow (but not a red), and green if all children are green.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Here is my take on it:


    =IF(COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))> 0, IF(COUNTIFS(CHILDREN(), @cell = "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), @cell = "Yellow")> 0, "Yellow", "Green")), IF(OR(Status@row = "On Hold", Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Complete", [Finish Date]@row> TODAY(7)), "Green", IF([Finish Date]@row<= TODAY(-2), "Red", "Yellow"))))


    You should be able to apply this as a column formula, and it should work on both parent and child rows.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Have you been able to successfully create the individual formulas (one for child rows and one for parent rows) and just need help combining them, or do you need help with creating the individual pieces?

  • I need help with creating the individual pieces.

  • ro.fei
    ro.fei ✭✭✭✭✭
    edited 02/22/23

    Hey @Kristin G

    Let's see if I can help you out with these. You're going to want to use nested IF statements with multiple conditions inside some OR & AND statements.

    First, I want to clarify what your conditions are. Here is what I interpreted:


    ------- Conditions -------

    Green when:

    • Status is "Complete" (regardless of Finish Date)
    • Status is "In Process" & Finish Date is 7+ days in the future
    • Status is blank & Finish Date is 7+ days in the future

    Yellow when:

    • Status is "In Process" & Finish Date is this week, today, or up to 1 day in the past
    • Status is blank & Finish Date is this week, today, or up to 1 day in the past

    Red when:

    • Status is "In Process" & Finish Date is more than 1 day in the past
    • Status is blank & Finish Date is more than 1 day in the past

    Gray when:

    • Status is "On Hold" (regardless of Finish Date)
    • Status is "Canceled" (regardless of Finish Date)

    Blank when:

    • Both Status & Finish Date are blank

    Those are the conditions I interpreted, & I believe they cover all situations. Let me know if my assumptions are wrong so I can help you further with the exact conditions.


    ------- Health Formula -------

    Here's the formula I came up with. Note that my column names might be different than yours & you'll need to make sure they're accurate to your sheet. In the Health column, you'll want a formula similar to the following:

    =IF(AND(ISBLANK(Status@row), ISBLANK([Finish Date]@row)), "", IF(OR(Status@row = "On Hold", Status@row = "Canceled"), "Gray", IF(OR(Status@row = "Complete", AND([Finish Date]@row - TODAY() >= 7, OR(Status@row = "In Process", ISBLANK(Status@row)))), "Green", IF(AND([Finish Date]@row - TODAY() >= -1, [Finish Date]@row - TODAY() < 7, OR(Status@row = "In Process", ISBLANK(Status@row))), "Yellow", IF(AND([Finish Date]@row - TODAY() < -1, OR(Status@row = "In Process", ISBLANK(Status@row))), "Red", "")))))

    Here's a screenshot of my formula in case that's easier to read:


    ------- Sample Results -------

    Here's a screenshot of the results I got when I tried a bunch of different Status / Finish Date combinations:

    I organized it by the Finish Date & added in a Notes column to make it a little easier to interpret what you're looking at.


    I really hope this helps!! 😊 Feel free to mention me on here if you have any more questions. I know setting up nesting IF/AND/OR statements can be tricky.


    Good luck! - Ro

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Here is my take on it:


    =IF(COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))> 0, IF(COUNTIFS(CHILDREN(), @cell = "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), @cell = "Yellow")> 0, "Yellow", "Green")), IF(OR(Status@row = "On Hold", Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Complete", [Finish Date]@row> TODAY(7)), "Green", IF([Finish Date]@row<= TODAY(-2), "Red", "Yellow"))))


    You should be able to apply this as a column formula, and it should work on both parent and child rows.

  • This was helpful - thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!