need help with RYG formula

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

Best Answer

  • Aravind GP
    Aravind GP ✭✭✭
    Answer ✓

    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.

    Thanks,

    Aravind

    Associate Director

    Copernicus Consulting Pte. Ltd.

    P: +65 9230 5657 | E: aravind@copernicusworld.com

    Feel free to reach out for licenses, services, and training on Smartsheet

Answers

  • Aravind GP
    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"))))))

    Thanks,

    Aravind

    Associate Director

    Copernicus Consulting Pte. Ltd.

    P: +65 9230 5657 | E: aravind@copernicusworld.com

    Feel free to reach out for licenses, services, and training on Smartsheet

  • 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,


    Carol

  • Aravind GP
    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:"

    Thanks,

    Aravind

    Associate Director

    Copernicus Consulting Pte. Ltd.

    P: +65 9230 5657 | E: aravind@copernicusworld.com

    Feel free to reach out for licenses, services, and training on Smartsheet

  • 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
    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.

    Thanks,

    Aravind

    Associate Director

    Copernicus Consulting Pte. Ltd.

    P: +65 9230 5657 | E: aravind@copernicusworld.com

    Feel free to reach out for licenses, services, and training on Smartsheet

  • 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)


    I would be grateful for your help

    Thanks in advance,

    Carol

  • Aravind GP
    Aravind GP ✭✭✭
    Answer ✓

    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.

    Thanks,

    Aravind

    Associate Director

    Copernicus Consulting Pte. Ltd.

    P: +65 9230 5657 | E: aravind@copernicusworld.com

    Feel free to reach out for licenses, services, and training on Smartsheet

  • Hi Aravind,

    Thanks a lot for your help.

    Now everything works, I swapped operators in the formula.

    Have a great week,

    Carol

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!