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
-
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
-
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
-
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
-
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:
- If the status is "In Progress" AND the Due Date is less than today (i.e. in the past), then green
- If the status is "Completed" AND the % Complete is 100%, then green
- If the status is "At Risk" AND the Due Date is less than today (i.e. in the past), then Yellow
- If the % Complete is blank, then yellow
- If the % Complete is blank AND the Start Date is greater than today (i.e. in the future), then red
- If the Status is "Not Started" OR is blank, then red
- 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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!