Actual % Complete vs. On Schedule % Complete
Hope everyone is doing well? I'm looking for a formula or suggestions to accomplish the following:
IF “On schedule % Complete is > Actual % Complete then the task is “Late”, “Red Ball”.
IF “On schedule % Complete is < Actual % Complete then the task is “On track”, “Green Ball”.
IF “On schedule % Complete is = 0% (Actual % Complete) then the task is “Not Started”, “Grey Ball”.
Scenario:
Actual % Complete is currently being given based on progress from task owners. “On Schedule % Complete” is an automated value based on the task duration. For example, if I have a task that is 5 days long, each workday would be worth 20% complete, 100% at the end. However, if at end of 5 days the task owner reports 25%, then the task would be late. But we can also predict that the new end date will be 3 weeks/ 15 days from now based on the current reported progress.
Any information would be greatly appreciated,
Thank you.
Steve
Answers

Looks like your question has two parts maybe?
 Create a Symbol column with Red, Yellow, Green, Gray balls
 Use a nested if statement like this: =IF([On schedule % Complete]@row > [Actual % Complete]@row, "Red", IF(“[On schedule % Complete]@row < [Actual % Complete]@row, "Green", IF([On schedule % Complete]@row = [Actual % Complete]@row, "Gray", "")))
If your second question is "Can the duration be automatically adjusted, the answer is currently no. The duration cannot be a formula.
I hope this was helpful.
Ramzi
Ramzi Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: [email protected]
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Help Article Resources
Categories
Check out the Formula Handbook template!