Formula for Smartsheet PMO Template Project Plan Schedule Health

skdimitri

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.

Health.PNG

Genevieve P.

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

Genevieve P.

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

skdimitri

Thank you so much @Genevieve P. , that worked.

Regards.