RYG Formula For Plan Vs Actual % Complete

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

Answers

  • Jason Duryea
    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
    Anthony Barthelemy ✭✭✭✭✭✭

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

  • Anthony Barthelemy
    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
    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
    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))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!