Formula for Smartsheet PMO Template Project Plan Schedule Health

skdimitri
skdimitri ✭✭
edited 03/20/23 in Formulas and Functions

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

  • Genevieve P.
    Genevieve P. Employee Admin
    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    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

  • skdimitri
    skdimitri ✭✭
    edited 03/23/23

    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!