Formula for Smartsheet PMO Template Project Plan Schedule Health
I have set up a Schedule Health Formula in the Project Plan where it calculates the Schedule Health based on the Health of the Children Rows. For Example, if 1 of the 6 Children Rows(Level 2) is Red then the Parent Row(Level 1) is Yellow and if there are 2 or more Children row(Level 2) are Red then the Parent Row(Level 1) turns to be Red. This applies to the Grand Parent(Level 0) row as well as that is the Overall Schedule Health of a Project. Now to take it one step further, I am trying to develop a formula for Schedule Health in the Project Plan where if the Status is Complete or Cancelled the Schedule Health should be blank for that row irrespective of the Start Date and End date or any other criteria as that would calculate the correct Overall Schedule Health.
For the Level Column :
=COUNT(ANCESTORS([Task Name]@row))
The formula which I used to Calculate Schedule Health is :
=IF(IF(COUNT(CHILDREN([Task Name]@row)) > 0, "Parent") = "Parent", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) = 0, "Green", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) < 0.25, "Yellow", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) >= 0.25, "Red", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) = 1, "Green")))), IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))
Any suggestions on how we can do that would be really appreciated.
Best Answer
-
Hi @skdimitri
Try adding this at the front:
=IF(OR(Status@row = "Complete", Status@row = "Canceled"), "",
=IF(OR(Status@row = "Complete", Status@row = "Canceled"), "", IF(IF(COUNT(CHILDREN([Task Name]@row)) > 0, "Parent") = "Parent", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) = 0, "Green", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) < 0.25, "Yellow", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) >= 0.25, "Red", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) = 1, "Green")))), IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @skdimitri
Try adding this at the front:
=IF(OR(Status@row = "Complete", Status@row = "Canceled"), "",
=IF(OR(Status@row = "Complete", Status@row = "Canceled"), "", IF(IF(COUNT(CHILDREN([Task Name]@row)) > 0, "Parent") = "Parent", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) = 0, "Green", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) < 0.25, "Yellow", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) >= 0.25, "Red", IF(COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) = 1, "Green")))), IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much @Genevieve P. , that worked.
Regards.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!