Calculate Health for all tiers of a project based on hierarchy, Status, Dates, Percentage Complete

Melissa Moseley

I have a project with the following hierarchy:

I would like the Project Name health to calculate based on the average the Project Stage Health

I would like the Project Stage health to calculate based on the average the Milestone Health

I would like the Milestone health to calculate based on the average the Task Item Health

I would like the Task Item health to calculate based on Status, Start Date and End Date:

·       If Start Date is in the past, and Status is blank or “Not Started”, then Health = Red

·       If End Date is in the past, and Status is not “Complete” or “Canceled”, then Health = Red

·       If status is “Not started” or “On Hold”, AND Start Date is in the Future, then Health = Gray

·       If status is “In Progress” AND End Date is in the Future, Health = Green

·       If status is “Canceled”, Health = Green

·       If status is “Complete”, Health = Green

In addition to this, is it possible to incorporate percentage complete, actual vs expected? For example, if an item starts on 3/1 and ends on 3/31, and today is 3/15, the expected percentage complete should be approx. 50%. If the actual complete percentage is lower than the expected percentage complete can it play a role in the calculation of the health?

Ideally if it is lower than 26% I would want the health to downgrde from green to yellow, or yellow to red. 

Genevieve P.

Hi @Melissa Moseley

You've done really well! Very close.

What I can see here is that the " mark in front of "Gray" is actually curved instead of straight up and down. Certain programs write quotes in different ways and Smartsheet needs the quotes to be the straight up and down ones.

I also see your new "Red" criteria also has the wrong quote types. Try swapping them out, like so:

=IF([% Complete]@row = 1, "Green", IF(AND([Status]@row = "Not Started", [Start Date]@row>=Today()), "Gray", IF(AND([Status]@row = "Not Started", [Start Date]@row<Today()), "Red", IF([Expected % Complete]@row < [% Complete]@row, "Green", IF([Expected % Complete]@row < [% Complete]@row + 0.1, "Yellow", "Red"))))

Jaykel Torres

Hey @Melissa Moseley,

You will definitely want to use Formulas to perform these calculations. It is likely you will need to incorporate the Functions below when building them:

• IF Function - https://help.smartsheet.com/function/if
• AVG Function - https://help.smartsheet.com/function/avg
• AVGIF Function - https://help.smartsheet.com/function/avgif
• COUNT Function - https://help.smartsheet.com/function/count
• COUNTIFS Function - https://help.smartsheet.com/function/countifs
• DATE Function - https://help.smartsheet.com/function/date

I've listed a few Community Discussions that you may use for reference:

• https://community.smartsheet.com/discussion/5176/nested-if-formula
• https://community.smartsheet.com/discussion/76015/countifs
• https://community.smartsheet.com/discussion/comment/304541#Comment_304541

I highly recommend taking our Formula related Training Modules within Smartsheet University.

I hope this helps!

Jaykel

Melissa Moseley

Thank you. I was able to get all of the parent rows working... For the child rows I want to add criteria.

Right now I have the following formula:

=IF([% Complete]@row = 1, "Green", IF(Status@row = "Not Started", "Gray", IF([Expected % Complete]@row < [% Complete]@row, "Green", IF([Expected % Complete]@row < [% Complete]@row + 0.1, "Yellow", "Red"))))

This works, but I want to include criteria that looks at start date vs status

• If %complete = 100%, Green
• If status is not started and start date is greater than today, Gray
• If status is not started and start date is today or in the past, Red
• If expected% is less than %completed, Green
• If expected% exceeds (%completed +0.1), Yellow
• Otherwise, Red

I modified the formula to to include the change, but I am getting an unparesable error..

=IF([% Complete]@row = 1, "Green", IF(AND([Status]@row = "Not Started", [Start Date]@row>=Today()), “Gray", IF(AND([Status]@row = "Not Started", [Start Date]@row<Today()), “Red”, IF([Expected % Complete]@row < [% Complete]@row, "Green", IF([Expected % Complete]@row < [% Complete]@row + 0.1, "Yellow", "Red"))))

Any ideas what I am doing wrong?

Genevieve P.

Hi @Melissa Moseley

You've done really well! Very close.

What I can see here is that the " mark in front of "Gray" is actually curved instead of straight up and down. Certain programs write quotes in different ways and Smartsheet needs the quotes to be the straight up and down ones.

I also see your new "Red" criteria also has the wrong quote types. Try swapping them out, like so:

=IF([% Complete]@row = 1, "Green", IF(AND([Status]@row = "Not Started", [Start Date]@row>=Today()), "Gray", IF(AND([Status]@row = "Not Started", [Start Date]@row<Today()), "Red", IF([Expected % Complete]@row < [% Complete]@row, "Green", IF([Expected % Complete]@row < [% Complete]@row + 0.1, "Yellow", "Red"))))

Melissa Moseley

Aha! I think I stared at it for too long and didn't notice those. I was sooo close. Thanks for your help. It is now working. Whew!

Genevieve P.

No problem at all! 🙂