RYG Formula

Tony Barthelemy

Thanks Paul but the Not Started does not display at 0%.I added greater than less than to that portion of the formula:

=IF(AND([% Complete (Actual)]3 > [% Complete (Planned)]3, [% Complete (Actual)]3 < 1), "Ahead", IF([% Complete (Planned)]3 > [% Complete (Actual)]3, "Behind", IF(AND([% Complete (Planned)]3 = [% Complete (Actual)]3, [% Complete (Actual)]3 < 1, [% Complete (Actual)]3 > 0), "On Track", IF([% Complete (Actual)]3 = 1, "Complete", IF([% Complete (Actual)]3 = 0, "Not Started")))))

I appreciate all your help!

 

 

 

Tony Barthelemy

Paul how do I improve on this by creating a tolerance for RAG? 

I want % Complete Actual to look at %Complete Planned and if it falls within the tolerance, change RAG Color.

• Amber: Actual is Within 10% of Plan.
• Red: Actual is <= 10% behind Plan%

=IF(AND([% Complete (Actual)]1 > [% Complete (Planned)]1, [% Complete (Actual)]1 < 1), "Green", IF([% Complete (Planned)]1 > [% Complete (Actual)]1, "Red", IF(AND([% Complete (Planned)]1 = [% Complete (Actual)]1, [% Complete (Actual)]1 < 1, [% Complete (Actual)]1 > 0), "Blue", IF([% Complete (Actual)]1 = 1, "Complete", IF([% Complete (Actual)]1 = 0, "")))))

Mike Andreas

Kam - I got an email regarding a different post but am not able to find it on Community.  Please share sheet to me at mandr007@aol.com and I will be happy to help!

Paul Newcome

Sorry about that. I was just looking at the syntax.

Paul Newcome

Ok. So lets break this down to make sure we are covering everything you want.

.

If Actual = 1, "Complete"

If Actual > Planned, "Green"

If Actual = Planned, "Blue"

If Actual < Planned, If within 10%, "Amber", If greater than 10% variance, "Red"

.

Is that correct? Did I miss anything?

Tony Barthelemy

Yes that is correct, but I figured it out over the weekend. However, I used a different approach. I used the Variance from EVA and RYG. Here are the formulas I used:

Variance:

=([% Complete (Actual)]1 - [% Complete (Planned)]1) / [% Complete (Planned)]1

RYG Status:

=IF(Variance1 > 0, "Green", IF(Variance1 = 0, "Blue", IF(Variance1 < -0.2, "Red", "Yellow")))

 

Thus far it has passed all the test as I change the % complete for Planned VS Actual.

 

Your thoughts

Tony Barthelemy

Mike:

 

Thanks for offering to help, but I think I have figured out. Take a look below, test the formula if you do not mind, and let me know if RYG changes accordingly.

Thanks

Paul Newcome

My thoughts would be that if you have discovered your own solution and it is working, then congratulations! Nice work! 

Tony Barthelemy

I tried adding Date Variance but unsuccessful. I must have my commas or parenthesis out of order:

=IF(AND([% Complete Variance]1 > 0, [Finish Date (Planned) Variance]1 < 0, "Green", IF(AND([% Complete Variance]1 = 0, [Finish Date (Planned) Variance]1 = 0, "Blue", IF(AND([% Complete Variance]1 < -0.2, [Finish Date (Planned) Variance]1 > 45, "Red", "Yellow"))))))

Paul Newcome

It would be your parenthesis. You have to close out each AND statement individually.

 

=IF(AND([% Complete Variance]1 > 0, [Finish Date (Planned) Variance]1 < 0), "Green", IF(AND([% Complete Variance]1 = 0, [Finish Date (Planned) Variance]1 = 0), "Blue", IF(AND([% Complete Variance]1 < -0.2, [Finish Date (Planned) Variance]1 > 45), "Red", "Yellow")))

Tony Barthelemy

Thanks