HEALTH SYMBOL OVERALL FORMULA

RJT

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

Individual Task Health

Green= Task is complete

Yellow= Task is in progress and not past due

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

Blue= Task is not started

Gray= Task is cancelled

Any assistance is greatly appreciated.

Paul Newcome

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 Newcome

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?

RJT

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.

RJT

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.

Individual Tasks Rows

=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

Paul Newcome

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.

RJT

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.

Paul Newcome

Are you able to provide a screenshot?

RJT

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

Paul Newcome

What is the exact formula you are using?

RJT

Formula for Individual Tasks

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

Paul Newcome

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.

RJT

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.

Paul Newcome

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

RJT

Paul,

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

Paul Newcome

Happy to help. 👍️