Automated RYG Balls

Hi,


I'm trying to add an automated RYG light column formula with the following function:

  • Green = [Status] is either 1) "Resolved" or 2) [Status] is "Open" and [Due Date] is in the future
  • Yellow = [Status] is "Open" and [Due Date] is in 14 days or less (including today)
  • Red = [Status] is "Open" and [Due Date] is in the past

Can anyone assist with this?


Thanks!

Katie

Best Answer

  • bcwilson.ca
    bcwilson.ca ✭✭✭✭✭
    edited 05/14/21 Answer ✓

    =IF(Status@row = "RESOLVED", "Green", IF([DUE DATE]@row < TODAY(), "Red", IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green")))


    Now I will explain a technique for writing long nested IF statements


    You will need 

    1. Smartsheet (Works with Excel As Well)

    2. Notepad or Notepad++.. A simple Text Editor/Scratchpad


    Don't just jump in and start writing.. look at the logic cases

    You said

    Green = [Status] is either 1) "Resolved" or 2) [Status] is "Open" and [Due Date] is in the future

    Yellow = [Status] is "Open" and [Due Date] is in 14 days or less (including today)

    Red = [Status] is "Open" and [Due Date] is in the past


    So if we started with writing for green it looks like we would have a lot of checks for AND Cases and OR Cases

    "IF(AND(Status@row=OPEN,[DUE DATE]=..."


    But if we walk through the logic and use our brains we can simplify it

    IF#1. If it is resolved.. no matter what the date then it is GREEN

    Then we can assume everything else is OPEN and not bother checking for it. Then the determining Factor becomes the DUE DATE and only the DUE DATE

    IF#2. If the DATE is in the past then it is RED.. always

    Then the next check becomes just for 14 days.. But not really..

    Again If it is within 14 days then it is yellow..  If it is not yellow it must be green


    Just going to spend a bit here..

    See how the logic simplified and we don’t have to check for between today and 14 days because we checked for the lower limit in IF#2.. No ANDS or OR's required

    And we also don’t have to have any statement to check for greater than 14 days as it is the only logic case left.


    OK... So... Finally

    IF#3. If the DATE is Less than (or equal to) 14 days then YELLOW. 

    Leaving only Green Left


    So we took that large problem and broke it down into 3 if statements

    Now let's write them


    I create 3 columns for the IF Statements

    Call them IF1, IF2 and IF3


    In IF1 we will write our first if Statement

    =IF(Status@row = "RESOLVED", "Green", "IF2")

    Pretty Simple.. and you see how the "IF2" is simply saying.. well IF2 will deal with that..


    Then in IF2 we look at the next case

    =IF([DUE DATE]@row < TODAY(), "Red", "IF3")

    Again Simple and for more, you need to look at the next IF statement

    * Note that this will show a "RESOLVED" case in the past as RED.. but don’t worry.. remember IF1 catches that and it will never get here !!


    IF 3 is simply..

    =IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green") or

    =IF([DUE DATE]@row <= TODAY(14), "Yellow", "Green") depending on how you want to write it

    * Again note you will get a yellow from the above statement for resolved in the past and overdue.. but IF1 and IF2 have taken care of those


    Now comes the fun part !!

    Copy each of those statements into notepad


    Step 1:

    IF1: =IF(Status@row = "RESOLVED", "Green", "IF2")

    IF2: =IF([DUE DATE]@row < TODAY(), "Red", "IF3")

    IF3: =IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green")


    Delete the equals on everything but IF1

    Step 2

    IF1: =IF(Status@row = "RESOLVED", "Green", "IF2")

    IF2: IF([DUE DATE]@row < TODAY(), "Red", "IF3")

    IF3: IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green")


    Now Looking at IF1 replace where you see with IF2 with IF2

    Step 3

    IF1: =IF(Status@row = "RESOLVED", "Green", "IF2") becomes:

    IF1: =IF(Status@row = "RESOLVED", "Green", IF([DUE DATE]@row < TODAY(), "Red", "IF3"))


    Now we have to do the same for IF3

    Step 4

    IF1: =IF(Status@row = "RESOLVED", "Green", IF([DUE DATE]@row < TODAY(), "Red", "IF3")) becomes

    IF1: =IF(Status@row = "RESOLVED", "Green", IF([DUE DATE]@row < TODAY(), "Red", IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green")))


    That’s it now you have the big Nested If statement that should work the first time !!

    Paste it in your ANDON Column (That’s what RYG is called in Lean Manufacturing) and it should work


    And you are Done !!

Answers

  • =IF(Status@row = "Resolved", "green", IF(AND(Status@row = "Open", ([Due Date]@row - TODAY()) > 14), "green", IF(AND(Status@row = "Open", [Due Date]@row >= 0), "yellow", "red")))

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi @Katie Marion ,


    Try this:

    =if(AND(Status@row="Open",[due date]@row <TODAY()),"Red",if(AND(Status@row="Open",[due date]@row<=TODAY(14)),"Yellow",if(OR(Status@row="Resolved",AND(Status@row="Open",[due date]@row>TODAY()),"Green","")))


    Let me know if it works!


    Best,

    Heather

  • @Dennis Knipfer , I tried the formula and it's populating the green light appropriately, but the rows that should be red say #INVALID OPERATION; I don't have any rows meeting yellow criteria so can't confirm.

    @HeatherD, that formula says #INCORRECT ARGUMENT SET

  • bcwilson.ca
    bcwilson.ca ✭✭✭✭✭
    edited 05/14/21 Answer ✓

    =IF(Status@row = "RESOLVED", "Green", IF([DUE DATE]@row < TODAY(), "Red", IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green")))


    Now I will explain a technique for writing long nested IF statements


    You will need 

    1. Smartsheet (Works with Excel As Well)

    2. Notepad or Notepad++.. A simple Text Editor/Scratchpad


    Don't just jump in and start writing.. look at the logic cases

    You said

    Green = [Status] is either 1) "Resolved" or 2) [Status] is "Open" and [Due Date] is in the future

    Yellow = [Status] is "Open" and [Due Date] is in 14 days or less (including today)

    Red = [Status] is "Open" and [Due Date] is in the past


    So if we started with writing for green it looks like we would have a lot of checks for AND Cases and OR Cases

    "IF(AND(Status@row=OPEN,[DUE DATE]=..."


    But if we walk through the logic and use our brains we can simplify it

    IF#1. If it is resolved.. no matter what the date then it is GREEN

    Then we can assume everything else is OPEN and not bother checking for it. Then the determining Factor becomes the DUE DATE and only the DUE DATE

    IF#2. If the DATE is in the past then it is RED.. always

    Then the next check becomes just for 14 days.. But not really..

    Again If it is within 14 days then it is yellow..  If it is not yellow it must be green


    Just going to spend a bit here..

    See how the logic simplified and we don’t have to check for between today and 14 days because we checked for the lower limit in IF#2.. No ANDS or OR's required

    And we also don’t have to have any statement to check for greater than 14 days as it is the only logic case left.


    OK... So... Finally

    IF#3. If the DATE is Less than (or equal to) 14 days then YELLOW. 

    Leaving only Green Left


    So we took that large problem and broke it down into 3 if statements

    Now let's write them


    I create 3 columns for the IF Statements

    Call them IF1, IF2 and IF3


    In IF1 we will write our first if Statement

    =IF(Status@row = "RESOLVED", "Green", "IF2")

    Pretty Simple.. and you see how the "IF2" is simply saying.. well IF2 will deal with that..


    Then in IF2 we look at the next case

    =IF([DUE DATE]@row < TODAY(), "Red", "IF3")

    Again Simple and for more, you need to look at the next IF statement

    * Note that this will show a "RESOLVED" case in the past as RED.. but don’t worry.. remember IF1 catches that and it will never get here !!


    IF 3 is simply..

    =IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green") or

    =IF([DUE DATE]@row <= TODAY(14), "Yellow", "Green") depending on how you want to write it

    * Again note you will get a yellow from the above statement for resolved in the past and overdue.. but IF1 and IF2 have taken care of those


    Now comes the fun part !!

    Copy each of those statements into notepad


    Step 1:

    IF1: =IF(Status@row = "RESOLVED", "Green", "IF2")

    IF2: =IF([DUE DATE]@row < TODAY(), "Red", "IF3")

    IF3: =IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green")


    Delete the equals on everything but IF1

    Step 2

    IF1: =IF(Status@row = "RESOLVED", "Green", "IF2")

    IF2: IF([DUE DATE]@row < TODAY(), "Red", "IF3")

    IF3: IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green")


    Now Looking at IF1 replace where you see with IF2 with IF2

    Step 3

    IF1: =IF(Status@row = "RESOLVED", "Green", "IF2") becomes:

    IF1: =IF(Status@row = "RESOLVED", "Green", IF([DUE DATE]@row < TODAY(), "Red", "IF3"))


    Now we have to do the same for IF3

    Step 4

    IF1: =IF(Status@row = "RESOLVED", "Green", IF([DUE DATE]@row < TODAY(), "Red", "IF3")) becomes

    IF1: =IF(Status@row = "RESOLVED", "Green", IF([DUE DATE]@row < TODAY(), "Red", IF([DUE DATE]@row <= TODAY() + 14, "Yellow", "Green")))


    That’s it now you have the big Nested If statement that should work the first time !!

    Paste it in your ANDON Column (That’s what RYG is called in Lean Manufacturing) and it should work


    And you are Done !!

  • @bcwilson.ca --thank you so much for your detailed answer! The formula worked but I also really appreciate your explaining the logic and how to think differently to simplify the variables.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!