Project health formula support

Kathryn Pineda

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

Katy H

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?

Kathryn Pineda

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

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.

Kathryn Pineda

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

Katy H

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.

Kathryn Pineda

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