# HEALTH SYMBOL OVERALL FORMULA

Hello...Still having issues with the overall health formula. I am trying to establish a formula that will do the following:

Overall Health Formula Need:

Green = More than 80% of tasks health are green

Yellow = More than 20% and less than 80% of tasks health are yellow

Red = More than 5% and less than 20% of tasks health are red

Yellow= Task is in progress and not past due

Red=Task is in progress and past due or delayed or at risk

Any assistance is greatly appreciated.

• ✭✭✭✭✭✭

Ok. That's what we needed to know. Now that we know the syntax is working, we just need to adjust the logic a little bit like so:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(Status@row = "Complete", "Green", IF(Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Delayed", Status@row = "At Risk", [Due Date]@row< TODAY()), "Red", IF([Due Date]@row<= TODAY(7), "Yellow", IF(Status@row = "In Progress", "Green", "Blue"))))), IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Blue")> 0, "Blue", "Green"))))

• ✭✭✭✭✭✭

This will get us started:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(Status@row = "Complete", "Green", IF(Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Delayed", Status@row = "At Risk", [Due Date]@row< TODAY()), "Red", IF(Status@row = "In Progress", "Yellow", "Blue")))), I have more questions here...

We need some more clarity on the parent row logic...

Green = More than 80% of tasks health are green

Yellow = More than 20% and less than 80% of tasks health are yellow What if 90% are yellow?

Red = More than 5% and less than 20% of tasks health are red Would you want it to be red if 100% of child rows are red?

• Paul,

I apologize. I am still learning smartsheet. Right now I am identifying tasks that are due in 7 days as yellow. So I am hoping that I do not have 90% due in 7 days. But if that were the case then yes I would assume 90% yellow would be yellow for at least the one day. If the task is less than 7 days I would want it to do red and status to change to At-Risk. If all the child rows are red then yes I would want the parent field to be red. I am open to best practice on how we calculate overall health and individual task health.

• Paul,

So when I apply the formula I am not getting the overall symbol to show green when everything is complete. Below are the formulas in use.

Overall Health

=IF(COUNT(CHILDREN(Tasks@row)) = 0, IF(Status@row = "Complete", "Green", IF(Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Delayed", Status@row = "At Risk", [End Date]@row < TODAY()), "Red", IF(Status@row = "In Progress", "Yellow", "Blue")))))

Need overall health to reflect individual health status. If that makes sense. Not sure of best way to do this.

=IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", "Blue", IF(Status@row = "In-Progress", "Green", IF(Status@row = "On Hold", "Blue", IF(Status@row = "Delayed", "Red", IF([End Date]@row < TODAY(7), "Yellow", IF([End Date]@row < TODAY(), "Red")))))))

If not past due and complete = green

If past due and complete = green

If due in 7 Days = yellow

If due < 7 days and in progress= At Risk and Red

If due >7 Days and in progress = green

If due days and not started = blue

If not started and not past due and not less 7 days or less = blue

• ✭✭✭✭✭✭

Right. The formula I provided is for the child rows only. I did not enter anything for the overall yet because I had those questions.

My suggestion for a rollup is to flag red if there is even one red in the child rows. This will let you know that there is something within that section that requires immediate attention.

If there are no reds, we would use the same thought process for yellow.

If there are no reds or yellows, we check for blues.

If there are no colors other than green, that is when we output green.

If the above logic makes sense, the formula would look like this:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(Status@row = "Complete", "Green", IF(Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Delayed", Status@row = "At Risk", [Due Date]@row< TODAY()), "Red", IF(Status@row = "In Progress", "Yellow", "Blue")))), IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Blue")> 0, "Blue", "Green"))))

The above can be applied as a column formula so you don't have to worry about copy/pasting separate formulas if you happen to adjust any hierarchy.

• Paul,

I applied the formula and when I change status on the individual row to Delayed individual row health shows red but overall is showing green. When I turn all individual rows to delayed any the rows health changes red the overall still remains green. Also, when I change the due date to 7 days it stays green and I am trying to turn it yellow. If due date is less than 7 i would want health to turn red and status to change to at risk. I appreciate your assistance. It looks the child row logic is working but the overall is not giving me the output I am looking for.

• ✭✭✭✭✭✭

Are you able to provide a screenshot?

• Paul,

Below is a screen short. When Children are green and sub children are red.

Below is when the children are red and the sub children are green. Also, I am trying to get status of health to change to yellow when end date is more than days away and less than 14 days away

• ✭✭✭✭✭✭

What is the exact formula you are using?

=IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", "Blue", IF(Status@row = "In-Progress", "Green", IF(Status@row = "On Hold", "Blue", IF(Status@row = "Delayed", "Red", IF([End Date]@row < TODAY(7), "Yellow", IF([End Date]@row < TODAY(), "Red")))))))

Formula for Overall Health Indicator

=IF(COUNT(CHILDREN(Tasks@row)) = 0, IF(Status@row = "Complete", "Green", IF(Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Delayed", Status@row = "At Risk", [End Date]@row < TODAY()), "Red", IF(Status@row = "In Progress", "Yellow", "Blue")))), IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Blue") > 0, "Blue", "Green"))))

• ✭✭✭✭✭✭

Ok. So the formula I provided is intended to go on every row regardless of hierarchy level. I understand that right now it is not accounting for the yellow dates like you want it to, but apply my formula as a column formula to make sure everything else is working as expected.

• Your formula works as intended. Just not catching the yellow when tasks are due in 7 days. Other than that it works the way it needs to.

• ✭✭✭✭✭✭

Ok. That's what we needed to know. Now that we know the syntax is working, we just need to adjust the logic a little bit like so:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(Status@row = "Complete", "Green", IF(Status@row = "Cancelled", "Gray", IF(OR(Status@row = "Delayed", Status@row = "At Risk", [Due Date]@row< TODAY()), "Red", IF([Due Date]@row<= TODAY(7), "Yellow", IF(Status@row = "In Progress", "Green", "Blue"))))), IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow")> 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Blue")> 0, "Blue", "Green"))))

• Paul,

Thank you for your help. Seems to be working the way it needs to. Greatly appreciated.

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!