Project health formula support
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

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?

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.

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 H Yes, that would be correct. Also, it is a column formula.

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.

I’m still a bit confused on what an ultimate solution or formula would look like
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 454 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!