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

Options
Melissa Moseley
edited 10/21/21 in Formulas and Functions

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. 

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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"))))

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!