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.

Find more posts tagged with

Accepted answers

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

All comments

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:

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.

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

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! 🙂

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.