RYG Ball IF Statements

djm714

I have been trying for a few a while now to get the IF statement to work for automating the RYG to work off of end date and % complete. I would like to have it work where if the it is 3 days from complete and below 50% complete have it as red. If it is 51% to 90% yellow and 90% to 100% green. If on the last day it is still in the 90% keep it green. If it passes the date and not complete then make it red. 

 

My IF statements are not working and I am confused now. 

 

Thanks,

 

Dan

Greg Gates

Hi Dan,

 

This IF-statement gets a little complex since it involves a few different conditions. I think the following addresses all of your needs:

 

=IF(AND(TODAY() - [Date Column]1 > 0, [% Complete]1 < 1), "Red", IF([Date Column]1 - TODAY() < 4, IF([% Complete]1 >= 0.9, "Green", IF([% Complete]1 > 0.5, "Yellow", "Red"))))

 

I'm not sure what you want to have if the date is more than 3 days away, so I just leave it blank for now. Let me know if you don't understand how the formula works, or if it doesn't address all of your issues!

 

 

djm714

Hi Greg,

 

I was going to have it stay green until the it gets within 3 days. The IF statement that you did works great.