Need Help with Formula to Roll Up and Average Schedule Health.
Need Help with Formula to Roll Up and Average Schedule Health.
I have one that now updates the Schedule Health RYG based on the Start Date/End Date and Task Status.
Next, I am trying to roll up/average those Individual Task schedule health values to an overall Phase schedule health value and then ultimately to the Overall Project schedule health.
Current formula: Overall Project Schedule Health would never move from Green unless we were past the final End Date
Baseline model: Once you go red, you can never get back to green or even yellow
Any insight would be much appreciated.
Best Answer

You will need to write a nested IF function to deal with the levels of hierarchy in your RAG column.
You will have 1 set of IF's to deal with the Child Rows (usually based on Date and % complete etc)
Then another set of IF's to deal with Parent Rows (usually based on if there is a Red ball in the children then the parent is red, if there is a yellow one, then parent is yellow etc)
If the Project level (row1) logic needs to be different then a set of IF's for that will also be required.
For example:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow"))), IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))
The first half of this is dealing with Parent Rows:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow")))
The opening condition: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, is asking if there are any children (i.e. is this a Parent row) then this particular formula is then saying if it is a parent row then, if all the children are Blue then the Parent is also Blue, if all the children are Green then the Parent is also Green, if ANY of the children are Red then the Parent is also Red, or for anything else the Parent is Yellow.
The second half is child rows:
, IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))
If the row is NOT a parent row then if % Complete = 100% then Green, if the row is Overdue then Red, if the % Complete is still 0 then Blue otherwise it is set to Yellow.
I hope this helps...
Kind regards
Debbie
Answers

Hey @BArmendariz
Can you share some screenshots please?
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itaiperez/

Screenshots and your existing formula(s) would be very helpful.

You will need to write a nested IF function to deal with the levels of hierarchy in your RAG column.
You will have 1 set of IF's to deal with the Child Rows (usually based on Date and % complete etc)
Then another set of IF's to deal with Parent Rows (usually based on if there is a Red ball in the children then the parent is red, if there is a yellow one, then parent is yellow etc)
If the Project level (row1) logic needs to be different then a set of IF's for that will also be required.
For example:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow"))), IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))
The first half of this is dealing with Parent Rows:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow")))
The opening condition: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, is asking if there are any children (i.e. is this a Parent row) then this particular formula is then saying if it is a parent row then, if all the children are Blue then the Parent is also Blue, if all the children are Green then the Parent is also Green, if ANY of the children are Red then the Parent is also Red, or for anything else the Parent is Yellow.
The second half is child rows:
, IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))
If the row is NOT a parent row then if % Complete = 100% then Green, if the row is Overdue then Red, if the % Complete is still 0 then Blue otherwise it is set to Yellow.
I hope this helps...
Kind regards
Debbie

Here is my original formula. This one works as expected, just looking for a rollup at the phase level.
=IF(AND([Start Date]@row > TODAY(), Status@row = "Not Started"), "Gray", IF(OR(AND([% Complete]@row <= 0.25, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [End Date]@row < TODAY())), "Red", IF(AND([% Complete]@row <= 0.5, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Yellow", IF(AND([% Complete]@row >= 0.75, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Green", "Green"))))

Thank you, trying now. I had tried a few things, but hadn't considered the word "children." I'll come back and let everyone know how it works out.

To be honest, I pop in a helper column called Parent which is a checkbox, then I simplify the formula to say:
=IF(Parent@row =1, insert nested IF's here for Parent logic, insert nested IF's here for Child logic))
Good luck!
Kind regards

This is what we ended up with, and it worked beautifully.
=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 0.5, "Red", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 1.5, "Yellow", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 2.5, "Green", "Gray")))), IF([% Complete]@row >= 1, "Green", IF(OR([Start Date]@row > TODAY(), ISBLANK([End Date]@row)), "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < WORKDAY(TODAY(), 3), "Yellow", "Green")))))
Then rightclick on the column and select "Convert to Column Formula"


Hello
Use a weighted average approach to calculate overall project schedule health. Assign weights to tasks based on importance. Multiply task schedule health by weight for each task, then average for phases and overall project.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!