Does anyone have the formula syntax to control the RYG and Gray symbols on a sheet

Cecelia Martin

AWESOME!  Thank you so much

Mike Andreas

I thought about the equations more, and decided further refinement was needed.  The situation where a task should be 90% complete according to the timing defined (ex: given duration is 10 days, and 9 days have passed), has an effort of 2 hours, but is currently 0% complete.  In this scenario, my above equations would return the red status ball, but it may be very possible that the work was always planned on the last day of the 10 days.  To address this, I revised the equations per below.

 

These new equations require an additional column, which I've initially titled "Proj Mgr Curr Conf Level of On Time Completion".  This is a drop down field, with the values of N/A, Low, Medium, and High.  The way I am using this is to assign N/A to parent rows and also tasks with Detailed Status of "Not Started".  For tasks that have a Planned vs Actual % Complete variance of greater than 10%, Planned % complete not equal to 100%, and an on time confidence level of Medium, Detailed Status results in "...(Medium Confidence)" per below.

 

New "Detailed Status" Equation (row 2):

=IF(AND([Planned Percent Complete Per Timeline]2 - [Actual Percent Complete]2 > 0.1, [Planned Percent Complete Per Timeline]2 <> 1, [Proj Mgr Curr Conf Level of On Time Completion]2 = "Medium"), "In Progress - At Risk (Medium Confidence)", IF(AND([Planned Percent Complete Per Timeline]2 - [Actual Percent Complete]2 > 0.1, [Planned Percent Complete Per Timeline]2 <> 1, [Proj Mgr Curr Conf Level of On Time Completion]2 = "High"), "In Progress - On Track (High Confidence)", IF(AND([Actual Percent Complete]2 <> 1, [Date Completed]2 <> ""), "TASK NOT COMPLETED - DELETE EXISTING DATE", IF(AND([Actual Percent Complete]2 = 1, [Date Completed]2 = ""), "ENTER COMPLETION DATE", IF(OR(AND([End Date]2 - TODAY() < 0, [Actual Percent Complete]2 <> 1), ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) >= 0.25), "In Progress - Off Track", IF([Date Completed]2 - [End Date]2 > 0, "Completed - Late", IF(AND([Planned Percent Complete Per Timeline]2 = 1, [Actual Percent Complete]2 = 1), "Completed - On Time", IF(AND([Days Until Start]2 > 0, ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) <= 0.1), "Not Started", IF(AND(ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) > 0.1, ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) <= 0.25), "In Progress - At Risk", "In Progress - On Track")))))))))

 

New Status Equation (row 2):

=IF(OR([Detailed Status]2 = "In Progress - At Risk", [Detailed Status]2 = "In Progress - At Risk (Medium Confidence)"), "Yellow", IF(OR([Detailed Status]2 = "In Progress - Off Track", [Detailed Status]2 = "TASK NOT COMPLETED - DELETE EXISTING DATE"), "Red", IF(OR([Detailed Status]2 = "Completed - On Time", [Detailed Status]2 = "Completed - Late"), "Blue", "Green")))

 

Take care to keep an eye on the rows with Detailed Status of "...High/Medium Confidence)", as Planned Percent Complete can creep to 100%, but the Status ball will show Yellow or Green up to PPC = 99%.  To be clear, if PPC = 100% but the actual is anything less, the Red status ball DOES appear.

 

I don't dare declare that this code is the most efficient, nor that the logic is still entirely watertight, and invite feedback on improvements.

cdoetsch

Maybe I missed it but is there an equation for the new Proj Mgr Conf Level of On Time Completion or is this intended to be manually updated by the PM?

Mike Andreas

No, equation - it is a drop down based column manually updated by the PM after a dialogue about the task with the task owner.  Ultimate intent is to avoid showing more "Red"s than is really the case, provided there is a high degree of confidence between the Task Owner and PM that it will actually get done on time.  I found this was needed because I didn't want to say a task was 50% done just to make the traffic light green, when no work had actually been performed at that point.

Tony Barthelemy

Greetings Mike:

 

I am trying to create a similar formula for RAG Status. I want them to change based on Plan Vs Actual % Complete.

• Green (Ahead): Actual % Greater than Planned % Complete.
• Blue (On Track / Target): Actual % =  Planned %
• Amber: Actual is Within 10% of Plan.
• Red: Actual % Complete is 10% or Greater behind Plan% Complete

Here is what I thus Far for my Formula:

=IF(ABS([% Complete (Planned)]1 - [% Complete (Actual)]1) >= Tolerance1, "Red", IF(ABS([% Complete (Planned)]1 - [% Complete (Actual)]1) < Tolerance1, "Yellow"))

I can share my sheet if you like?

Thanks