Actual % Complete vs. On Schedule % Complete

Steve Akana

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 

Ramzi K

@Steve Akana

Looks like your question has two parts maybe?

1. Create a Symbol column with Red, Yellow, Green, Gray balls
2. 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