RYG Formula For Plan Vs Actual % Complete

Anthony Barthelemy

Smartsheet Community:

I tried tweaking a Similar Formula but was Unable to get the Result I desired. Here is what I am looking to Do:

Red:

If the Difference is More Than 10% Between:

% Complete (Actual) And % Complete (Plan)

Yellow:

If the Difference is Less Than 10% Between:

% Complete (Actual)  And % Complete (Plan)

Green:

If % Complete (Actual)  And % Complete (Plan) Are Equal OR If % Complete (Actual) is Greater than If % Complete (Plan)

Grey:

% Complete (Actual)  And % Complete (Plan) Equal 0

Thanks

Jason Duryea

Assuming that your RYG is related to Task Health in a schedule, then it’s normally based not only on % Complete, but also on TODAY() compared with the planned finish date for the activity. The plan is that every Task is 100% complete, therefore you need second criteria to determine RYG.

For example, you could check if the finish date is within x days in the past or future to determine if it’s close to finishing or late. A Task that is 50% complete and within 1-2 days of the planned finish date might be Yellow, or if past the planes date it would Red.

Anthony Barthelemy

OK! Do you already have such a Formula? If so, I can Modify that Formula to meet my Need.

Anthony Barthelemy

Here is the Formula I Created by Unsuccessful:

=IF([% Complete (Actual)]@row = 1, "Green", IF(AND([% Complete (Actual)]@row, [% Complete (Plan)]@row = 0 "Gray", IF(TODAY() > [Finish Date (Plan)]@row, "Red", IF(TODAY(10) >= [Finish Date (Plan)]@row, IF([% Complete (Actual)]@row < 0.75, "Yellow", "Green"))))

IPC

hi Anthony,

Could you please share with me your formula for "Planned % Complete"?

And did you also find out the indicators' formulate based on your criteria above?

Highly appreciated.

Kaleb

% Complete (Plan) Formula: Parent

=IFERROR(ROUND(AVGW(CHILDREN([% Complete (Plan)]@row), CHILDREN([Duration (Actual)]@row)), 2), 0)

% Complete (Plan) Formula: Child

=IF(IFERROR(IF(AND(TODAY() >= [Start Date (Actual)]@row, TODAY() <= [Finish Date (Actual)]@row), ROUND(NETWORKDAYS([Start Date (Actual)]@row, TODAY()) / [Duration (Actual)]@row, 2), IF(TODAY() > [Finish Date (Actual)]@row, 1, 0)), 0) > 1, 1, IFERROR(IF(AND(TODAY() >= [Start Date (Actual)]@row, TODAY() <= [Finish Date (Actual)]@row), ROUND(NETWORKDAYS([Start Date (Actual)]@row, TODAY()) / [Duration (Actual)]@row, 2), IF(TODAY() > [Finish Date (Actual)]@row, 1, 0)), 0))