# Formula for Smartsheet PMO Template Project Plan Schedule Health

Options
edited 03/20/23

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 :

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.

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• edited 03/23/23
Options

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

Regards.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!