need help with RYG formula

Carol Rykovanova

Hi Smartsheeters',

Please help me to find a solution with my formula.

The essence of the task is to change the indicators in the Health column using a formula.

I need to:

if Status is In Progress and Due Date is less than Today

and if Status is Completed and % Complete is 100%, then GREEN

if Status is At Risk and Due date is less than Today

and if % Completion is Blank, then YELLOW

if % Completion is Blank and Start Date is greater than today

and if the Due Date is greater than today and the % Complete is less than 100%, then RED

The formula I'm currently using doesn't work. It's so long that I might have made a syntax error.

Is it possible to make it shorter?

=IF(OR([% Complete]@row = 1, [Due date]@row > = TODAY()), "Green", IF(Status@row = "In progress", "Green", IF(AND([Due date]@row > TODAY(), [% Complete]@row < 1), "Green", IF(Status@row = "Completed", "Green", IF(Status@row = "At risk", "Yellow", IF(AND([Due date]@row > TODAY(), [% Complete]@row < 1), "Yellow", IF(AND([Due date]@row > TODAY(), [% Complete]@row = 0), "Yellow", IF(Status@row = "At risk", "Red", IF(AND([Due date]@row > TODAY(), [% Complete]@row < 1), "Red", IF(Status@row = "Not started", "Red", IF(AND([Start date]@row < TODAY(), [% Complete]@row = 0), "Red", IF([% Complete]@row < 0.75, "Yellow", IF(AND([Due date]@row = TODAY(7), [% Complete]@row < 0.25), "Red", IF([% Complete]@row < 0.5, "Yellow", "Green"))))))))

Thanks in advance and will greatly appreciate your help,

Carol

Aravind GP

Hi Carol,

The formula works from left to right. So if you want Red to take priority, add that prior to condition #4 and make condition #4 as the last IF statement. If you still have issues, email me and we can arrange for a screen sharing session to make it easier.

Aravind GP

Hi Carol,

Based on the conditions you have specified, I have given the formula below. However, I would suggest that you have a max of 2 conditions for each RYG color to make it simpler.

=IF(AND(Status@row = "In Progress", [Due Date]@row < TODAY()), "Green", IF(AND(Status@row = "Complete", [% Complete]@row = 1), "Green", IF(AND(Status@row = "At Risk", [Due Date]@row < TODAY()), "Yellow", IF(ISBLANK([% Complete]@row), "Yellow", IF(AND(ISBLANK([% Complete]@row), [Start Date]@row > TODAY()), "Red", IF(AND([Due Date]@row > TODAY(), [% Complete]@row < 1), "Red"))))))

Carol Rykovanova

Hi Aravind,

Thanks for the answer, but the formula still doesn't work as expected.

So what I'm saying - if the % Complete cell is Empty, then the Health colour should change accordingly.

Now the formula works the other way around - if the Status is At Risk and the % Complete is 10%, then the Health cell becomes Empty.

Also, binding to dates is very important, which is probably why I got such a long formula

To demonstrate what I mean - please see attached file. 4 highlighted rows show different statuses.

Thanks,

Copy of Document (4).pdf

Carol

Aravind GP

Hi Carol,

I realized that there was an error in the formula. It says "Complete" instead of "Completed" as in your sheet. Fixing that will fix the error in row# 7.

For Row# 8, the formula of % Complete being blank is taking it as Yellow. What color are you expecting it to be?

Row# 9 is taking the formula Due date being greater than today and % Complete being less than 100%. What color are you expecting it to be?

Row# 10 is blank as I didn't include a formula for "Not Started". Since it was not a condition as per the explanation in your "I need to:"

Carol Rykovanova

Hi Aravind,

You're right about Complete and Completed - this part is working now.

And I'm sorry about Not started status, forgot to add this condition to my initial question.

I need to:

if Status is In Progress and Due Date is less than Today

and if Status is Completed and % Complete is 100%, then GREEN

if Status is At Risk and Due date is less than Today

and if % Completion is Blank, then YELLOW

if % Completion is Blank and Start Date is greater than today

if Status is Not started or Blank

and if the Due Date is greater than today and the % Complete is less than 100%, then RED

Thanks again,

Carol

Aravind GP

Hi Carol,

Here's the new formula.

=IF(AND(Status@row = "In Progress", [Due Date]@row < TODAY()), "Green", IF(AND(Status@row = "Completed", [% Complete]@row = 1), "Green", IF(AND(Status@row = "At Risk", [Due Date]@row < TODAY()), "Yellow", IF(ISBLANK([% Complete]@row), "Yellow", IF(AND(ISBLANK([% Complete]@row), [Start Date]@row > TODAY()), "Red", IF(OR(Status@row = "Not Started", ISBLANK(Status@row)), "Red", IF(AND([Due Date]@row > TODAY(), [% Complete]@row < 1), "Red")))))))

It works like this:

1. If the status is "In Progress" AND the Due Date is less than today (i.e. in the past), then green
2. If the status is "Completed" AND the % Complete is 100%, then green
3. If the status is "At Risk" AND the Due Date is less than today (i.e. in the past), then Yellow
4. If the % Complete is blank, then yellow
5. If the % Complete is blank AND the Start Date is greater than today (i.e. in the future), then red
6. If the Status is "Not Started" OR is blank, then red
7. If the Due Date is greater than today (i.e. in the future) AND the % Complete is less than 100%, then red

Ideally, I would work it the other way around, where if the due date is in the past and it is not 100% complete, then red and if the start date is in the future, then green.

Carol Rykovanova

Hi Aravind,

You're absolutely right :) I worked so hard yesterday and I got confused.

This is current formula

=IF(AND(Status@row = "In Progress", [Due date]@row > TODAY()), "Green", IF(AND(Status@row = "Completed", [% Complete]@row = 1), "Green", IF(AND(Status@row = "At Risk", [Due date]@row < TODAY()), "Yellow", IF(ISBLANK([% Complete]@row), "Yellow", IF(AND(ISBLANK([% Complete]@row), [Start date]@row > TODAY()), "Red", IF(OR(Status@row = "Not Started", [Start date]@row < TODAY(), ISBLANK([% Complete]@row)), "Red", IF(AND([Due date]@row > TODAY(), [% Complete]@row < 1, ISBLANK([% Complete]@row)), "Red")))))))

I changed conditions to:

1. If the status is "In Progress" AND the Due Date is greater than today (i.e. in the future), then green

2. If the status is "Completed" AND the % Complete is 100%, then green

3. If the status is "At Risk" AND the Due Date is less than today (i.e. in the past), then Yellow

4. If the % Complete is blank, then yellow

5. If the % Complete is blank AND the Start Date is less than today (i.e. in the past), then red

6. If the Status is "Not Started" OR is blank, then red

7. If the Due Date is less than today (i.e. in the past) AND the % Complete is less than 100%, then red

8. If the Status is "Not Started" and the Start Date is less than today (i.e. in the past), AND if the "% Complete" is blank, then RED

The formula works, but the condition #8 just changes the ball colour to YELLOW. I think it happens because of #4

It turns out that filling in "% Completion" has a higher priority than "Start Date" and "Due Date" dependencies, but the priorities should be the same. I don’t know how to fix it (rows 10 and 11 in attached file)

Copy of Document (6).pdf

I would be grateful for your help

Thanks in advance,

Carol

Aravind GP

Hi Carol,

The formula works from left to right. So if you want Red to take priority, add that prior to condition #4 and make condition #4 as the last IF statement. If you still have issues, email me and we can arrange for a screen sharing session to make it easier.

Carol Rykovanova

Hi Aravind,

Thanks a lot for your help.

Now everything works, I swapped operators in the formula.

Have a great week,

Carol