Project health formula support

Options

Hello, I am hoping for additional guidance on adjusting a formula. I have built out a lengthy project plan, I have 4 colors for health (green - on track; yellow - under control but there are a few concerns; red- at risk, grey - not started.). My project has many parent tasks with child tasks. All of these tasks roll up in to one master project health that should sync to the portfolio dashboard. A copy of the project follows, https://publish.smartsheet.com/a7e21cda28ea45a6aa2735303dbdd5ad My concern is that the formula doesn't account for grey status not seem to be averaging out correctly. Any help would be appreciated. Thank you!

Katie

Answers

  • Katy H
    Katy H ✭✭✭✭✭✭
    Options

    Kathryn, can you share the formula you are using? It isn't visible on the published version of your sheet. And is the formula only in the parent/grandparent cells? Or is it a column formula?

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

    This is the formula for the main project health: =IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))

    I would then need a formula for (1) the parental rows and (2) formulas for the child tasks underneath the parent row).

    Also, my other primary concern is that the initial formula doesn't include grey which indicates tasks that have not yet started.

  • Katy H
    Katy H ✭✭✭✭✭✭
    Options

    For the main project health, what would define gray? I am assuming that it would be gray only if the entire project was not started?

    And then you would need to define the color categories for both the children and parents.

    This is what I would assume the Children colors would mean?

    • Green = complete
    • Yellow = due within 1 week and not marked complete
    • Red = past due and not marked complete
    • Gray = Not started and not due in next week

    Then parents...

    • Green = all children either green or gray
    • Yellow = 1 or more children marked yellow
    • Red = 1 or more children marked red
    • Gray = all children marked gray

    If that is correct we can use that to define your formulas.

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    edited 05/19/22
    Options

    @Katy H Yes, that would be correct. Also, it is a column formula.

  • Katy H
    Katy H ✭✭✭✭✭✭
    Options

    To the best of my knowledge, you wouldn't be able to generate a column formula that differentiates between parent and children if the parent rows are reading the children rows. you would have to create a separate column to measure the value of the children rows to be able to accommodate that type of column formula.

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

  • Kathryn Pineda
    Kathryn Pineda ✭✭✭✭
    Options

    I’m still a bit confused on what an ultimate solution or formula would look like

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!