RAG Rating Formulas

handmadetsunami

Hi,

I am struggling with RAG rating formulas (and its something that I have done before but I can't get my head around it at the moment!)

I am trying to do the following tasks:

Regardless of the end date, if % complete is 100%, then show a green rag rating

If the finish date is within 30 days and the % complete isn't 100%, then show a yellow rag rating

If the finish date is within 15 days or in the past and the % complete isn't 100%, then show a red rag rating

I have attached a screenshot to show how the sheet is laid out

 

Mike Wilday

Try this: 

=IF([% Complete]@row=1, "Green", IF(AND(Finish@row < Today(30), [% Complete]@row < 1), "Red", IF(AND(Finish@row < TODAY(30), [% Complete]@row < 1), "Yellow","") 

Mike Wilday

Whoops, I forgot to change the red criterion to 15 days. 

This is the one you want: 

=IF([% Complete]@row=1, "Green", IF(AND(Finish@row < Today(15), [% Complete]@row < 1), "Red", IF(AND(Finish@row < TODAY(30), [% Complete]@row < 1), "Yellow","") 

Mike Wilday

Put that in a RAG column type and you'll be good to go. 

handmadetsunami

Thank you very much, it was similar to what I had managed to get to but I couldn't quite get it right last week!

Mike Wilday

Awesome. I am glad I could help you figure it out!