RAG Rating Formulas

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

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?