Help with formula for schedule health
Hello, looking for some help with calculating schedule health.
I have added a symbols column (Green, yellow, red, gray circles) called schedule health to schedule.
I have started creating a formula to calculate the schedule health for each row. This is what I have based the formula on:
 On Track = Green. Occurs when ‘% complete’ is ‘100%’ before reaching or on the due date
 At risk = Yellow. Occurs when ‘% complete’ is not equal to 100% 5 days after the due date
 Action required = Red. Occurs when ‘% complete’ is not equal to 100% 10 days after the due date
 On hold = Grey. Occurs when the status is set to ‘On hold’
This is the formula I have used:
=IF([% Complete]@row = 1, "Green", IF(TODAY() > [End Date]@row, IF([% Complete]@row < 1, "Yellow", IF(TODAY(5) > [End Date]@row, IF([% Complete]@row < 1, "Red", IF(TODAY(10) > IF(Status@row = "On Hold", "Gray")))))))
The formula is working but I have noticed it is not set up right.
For example, when the status is on hold but the % complete is 50% it is showing up as yellow but I would like it to be gray to show it is now on hold. Also, the red 'action required' part of the formula doesn't seem to be working. The symbol is showing up as yellow for everything not 100% past the due date.
Part 2
I would like to calculate the overall schedule health of all the rows.
For example, if 90% of the schedule rows are Green then the overall health should be green.
If anyone has a formula I could use the calculate the overall health of the schedule that would be really helpful.
Thanks,
Emily
Schedule example:
Formula example
Best Answer

Made a modification to the formula Michael shared. Try it and see if works.
=if(Status@row = "On Hold", "Gray",if(and([% Complete]@row = 1,TODAY() > [End Date]@row),"Green",if(and([% Complete]@row < 1,TODAY(5) > [End Date]@row),"Yellow",if(and([% Complete]@row < 1,TODAY(10) > [End Date]@row) if"Red"))))
Meg
Meg Young
mmyoungconsulting@gmail.com
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response  "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Answers

The way you did the formula isn't correct.
Try this:
=if(Status@row = "On Hold", "Gray",if(and([% Complete]@row = 1,TODAY() > [End Date]@row),"Green",if(and([% Complete]@row < 1,TODAY(5) > [End Date]@row),"Yellow","Red")))

Thanks, Michael. That fixed the gray status problem.
At the minute the formula is showing a status of yellow for anything not complete past the due date. I would like the status to be yellow for anything not completed 5 days after the due date and red for anything that is more than 5 days past the due date.
If you could provide me with that formula that would be very helpful.
Thanks,
Emily

Made a modification to the formula Michael shared. Try it and see if works.
=if(Status@row = "On Hold", "Gray",if(and([% Complete]@row = 1,TODAY() > [End Date]@row),"Green",if(and([% Complete]@row < 1,TODAY(5) > [End Date]@row),"Yellow",if(and([% Complete]@row < 1,TODAY(10) > [End Date]@row) if"Red"))))
Meg
Meg Young
mmyoungconsulting@gmail.com
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response  "Insightful"or "Awesome" reactions are much appreciated. Thanks! 
Thanks so much Meg! All work now :)

Happy to hear it worked. Below is what I do when a formula is long/complicated and/or is giving me a error. It helps me make sure all the puncation marks needed are there. 😀
Break it down by each function and test it on a row. Once they all work, combine them in order of needed.
What I did with the formula Michael created was copy the formula for "Yellow", pasted it on the end and changed the color to "red". Just make sure when you do that to add the extra ending ).
Meg Young
mmyoungconsulting@gmail.com
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response  "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 213 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!