# RYG Formula

2»

• ✭✭✭✭✭

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")))))

• ✭✭✭✭✭

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, "")))))

• ✭✭✭

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!

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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?

• ✭✭✭✭✭

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.

• ✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭

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"))))))

• ✭✭✭✭✭✭

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")))

• ✭✭✭✭✭

Thanks

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!