RYG Formula
Comments

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!

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.
Your thoughts

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 201 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!