Formula for Red, Yellow, Green, Gray balls

Hi guys,

I think my logic for the below is right, but I've got a syntax problem somewhere. Results for any "Not Started" are "Incorrect Argument Set". Otherwise it works fine.

=IF(Status@row = "N/A", "Gray", IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", IF([Start Date]@row, TODAY(), "Gray", IF([End Date]@row <= TODAY(), "Red")), IF(Status@row = "In Progress", IF([End Date]@row > TODAY(), "Green", "Yellow")))))

Any fixes appreciated.

Jason Wirl

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ignore that last comment. You have a comma in place of an argument in the bold portion...

    =IF(Status@row = "N/A", "Gray", IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", IF([Start Date]@row, TODAY(), "Gray", IF([End Date]@row <= TODAY(), "Red")), IF(Status@row = "In Progress", IF([End Date]@row > TODAY(), "Green", "Yellow")))))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Jason Wirl

    Not sure if my previous one is correct.

    I guess that you want it more like this.

    If not, could you write it part by part instead?

    =

    IF(Status@row = "N/A", "Gray",

    IF(Status@row = "Complete", "Green",

    IF(Status@row = "Not Started", "ANSWER",

    IF([Start Date]@row = TODAY(), "Gray",

    IF([End Date]@row <= TODAY(), "Red",

    IF(Status@row = "In Progress", "ANSWER",

    IF([End Date]@row > TODAY(), "Green", "Yellow")))))))

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jason Wirl
    Jason Wirl ✭✭
    Answer ✓

    Gents,


    Thank you very much! @Andrée Starå, from what I understand, I need to nest some of them together to get the right logic as there are a couple conditions that need to be met for the "answer." Here it is in English to see if I'm doing that right.


    If STATUS is "N/A" - gray ball

    If STATUS is "Complete" - green ball

    If STATUS is "Not Started" - AND START DATE >= TODAY - gray ball, OR END DATE is <= TODAY - red ball

    If STATUS is "In Progress" - AND END DATE > TODAY - green ball

    Otherwise yellow ball.


    As of right now, I changed two things, and it works in all but three rows (not sure why yet) so I'm either on the target or really close. @Paul Newcome's find on the comma where there needed to be an argument was spot on (and I changed that in bold as you see above). I'd just like to understand the syntax for nesting the IF statements according to my logic better. Is there a document/post/anything else you can point me to that could help?

    Thanks,

    Jason

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    When you nest an IF in the "output if true" portion of another IF, it is basically replacing an AND statement.

    =IF(AND(Status@row = "In Progress", Start@row > TODAY()), "Green", IF(AND(Status@row = "In Progress", End@row < TODAY()), "Red"))


    In the above you have two IF/AND statements. Both of them share the criteria of Status@row = "In Progress". We can consolidate this by writing it out as

    =IF(Status@row = "In Progress", IF(Start@row> TODAY(), "Green", IF(End@row< TODAY(), "Red")))


    In this very basic example it doesn't save a whole lot, but when you start getting into longer, more complex nested IFs it can save a lot of typing. It is also helpful when you have additional Status criteria to add more IFs for because you can organize your formula based on the Status.


    Looking at your outline from your last post though, I would recommend grouping by ouput (color) instead.

    If STATUS is "N/A" - gray ball

    If STATUS is "Complete" - green ball

    If STATUS is "Not Started" - AND START DATE >= TODAY - gray ball, OR END DATE is <= TODAY - red ball

    If STATUS is "In Progress" - AND END DATE > TODAY - green ball

    Otherwise yellow ball.


    Gray:

    Status@row = "N/A"

    or

    AND(Status@row = "Not Started", [Start Date]@row >= TODAY())

    IF(OR(Status@row = "N/A", AND(Status@row = "Not Started", [Start Date]@row >= TODAY())), "Gray"


    Green:

    Status@row = "Complete"

    or

    AND(Status@row = "In Progress", [End Date]@row > TODAY())

    IF(OR(Status@row = "Complete", AND(Status@row = "In Progress", [End Date]@row > TODAY())), "Green"


    Red:

    AND(Status@row = "Not Started", [End Date]@row <= TODAY())

    IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red"


    Yellow:

    Everything else


    =IF(OR(Status@row = "N/A", AND(Status@row = "Not Started", [Start Date]@row >= TODAY())), "Gray", IF(OR(Status@row = "Complete", AND(Status@row = "In Progress", [End Date]@row > TODAY())), "Green", IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red", "Yellow")))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Jason Wirl

    Excellent!

    You're more than welcome!

    Yes, grouping is a personal preference. I usually break the parts down into different rows in the sheet and test them individually and then put the parts together. It can be tricky to get them right.

    If I write the formula in a text editor, I usually format it something like this, so it's easy to see each part.

    =
    IF(Status@row = "N/A", "Gray",
    IF(Status@row = "Complete", "Green",
    IF(Status@row = "Not Started", "ANSWER",
    IF([Start Date]@row = TODAY(), "Gray",
    IF([End Date]@row <= TODAY(), "Red",
    IF(Status@row = "In Progress", "ANSWER",
    IF([End Date]@row > TODAY(), "Green", "Yellow")))))))
    

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I frequently do the same thing as @Andrée Starå where I break it down into sections on different rows for testing and then put it all together once everything is working.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 05/26/21

    Double check that both date columns are in fact set to date type columns. If they are, double check that the data within those cells are definitely being stored as dates and not a text string that just looks like a date.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 05/26/21

    Hi @Jason Wirl

    I hope you're well and safe!

    There were a couple of issues. (You were missing the true parts, and there were one too many parentheses)

    Try something like this.

    =

    IF(Status@row = "N/A", "Gray", 

    IF(Status@row = "Complete", "Green", 

    IF(Status@row = "Not Started", "ANSWER", 

    IF([Start Date]@row, TODAY(), "Gray", 

    IF([End Date]@row <= TODAY(), "Red", 

    IF(Status@row = "In Progress", "ANSWER", 

    IF([End Date]@row > TODAY(), "Green", "Yellow")))))))


    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ignore that last comment. You have a comma in place of an argument in the bold portion...

    =IF(Status@row = "N/A", "Gray", IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", IF([Start Date]@row, TODAY(), "Gray", IF([End Date]@row <= TODAY(), "Red")), IF(Status@row = "In Progress", IF([End Date]@row > TODAY(), "Green", "Yellow")))))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Jason Wirl

    Not sure if my previous one is correct.

    I guess that you want it more like this.

    If not, could you write it part by part instead?

    =

    IF(Status@row = "N/A", "Gray",

    IF(Status@row = "Complete", "Green",

    IF(Status@row = "Not Started", "ANSWER",

    IF([Start Date]@row = TODAY(), "Gray",

    IF([End Date]@row <= TODAY(), "Red",

    IF(Status@row = "In Progress", "ANSWER",

    IF([End Date]@row > TODAY(), "Green", "Yellow")))))))

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jason Wirl
    Jason Wirl ✭✭
    Answer ✓

    Gents,


    Thank you very much! @Andrée Starå, from what I understand, I need to nest some of them together to get the right logic as there are a couple conditions that need to be met for the "answer." Here it is in English to see if I'm doing that right.


    If STATUS is "N/A" - gray ball

    If STATUS is "Complete" - green ball

    If STATUS is "Not Started" - AND START DATE >= TODAY - gray ball, OR END DATE is <= TODAY - red ball

    If STATUS is "In Progress" - AND END DATE > TODAY - green ball

    Otherwise yellow ball.


    As of right now, I changed two things, and it works in all but three rows (not sure why yet) so I'm either on the target or really close. @Paul Newcome's find on the comma where there needed to be an argument was spot on (and I changed that in bold as you see above). I'd just like to understand the syntax for nesting the IF statements according to my logic better. Is there a document/post/anything else you can point me to that could help?

    Thanks,

    Jason

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    When you nest an IF in the "output if true" portion of another IF, it is basically replacing an AND statement.

    =IF(AND(Status@row = "In Progress", Start@row > TODAY()), "Green", IF(AND(Status@row = "In Progress", End@row < TODAY()), "Red"))


    In the above you have two IF/AND statements. Both of them share the criteria of Status@row = "In Progress". We can consolidate this by writing it out as

    =IF(Status@row = "In Progress", IF(Start@row> TODAY(), "Green", IF(End@row< TODAY(), "Red")))


    In this very basic example it doesn't save a whole lot, but when you start getting into longer, more complex nested IFs it can save a lot of typing. It is also helpful when you have additional Status criteria to add more IFs for because you can organize your formula based on the Status.


    Looking at your outline from your last post though, I would recommend grouping by ouput (color) instead.

    If STATUS is "N/A" - gray ball

    If STATUS is "Complete" - green ball

    If STATUS is "Not Started" - AND START DATE >= TODAY - gray ball, OR END DATE is <= TODAY - red ball

    If STATUS is "In Progress" - AND END DATE > TODAY - green ball

    Otherwise yellow ball.


    Gray:

    Status@row = "N/A"

    or

    AND(Status@row = "Not Started", [Start Date]@row >= TODAY())

    IF(OR(Status@row = "N/A", AND(Status@row = "Not Started", [Start Date]@row >= TODAY())), "Gray"


    Green:

    Status@row = "Complete"

    or

    AND(Status@row = "In Progress", [End Date]@row > TODAY())

    IF(OR(Status@row = "Complete", AND(Status@row = "In Progress", [End Date]@row > TODAY())), "Green"


    Red:

    AND(Status@row = "Not Started", [End Date]@row <= TODAY())

    IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red"


    Yellow:

    Everything else


    =IF(OR(Status@row = "N/A", AND(Status@row = "Not Started", [Start Date]@row >= TODAY())), "Gray", IF(OR(Status@row = "Complete", AND(Status@row = "In Progress", [End Date]@row > TODAY())), "Green", IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red", "Yellow")))

  • Many thanks @Paul Newcome & @Andrée Starå. Very helpful as always. The grouping by color for easier interpretation and organization makes total sense.

    Cheers,

    Jason

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    really the grouping boils down to personal preference and can be different case by case. Sometimes I group by output. Other times I group by criteria. And then other times I just go brute force because my brain doesn't want to function enough to consolidate. Haha

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Jason Wirl

    Excellent!

    You're more than welcome!

    Yes, grouping is a personal preference. I usually break the parts down into different rows in the sheet and test them individually and then put the parts together. It can be tricky to get them right.

    If I write the formula in a text editor, I usually format it something like this, so it's easy to see each part.

    =
    IF(Status@row = "N/A", "Gray",
    IF(Status@row = "Complete", "Green",
    IF(Status@row = "Not Started", "ANSWER",
    IF([Start Date]@row = TODAY(), "Gray",
    IF([End Date]@row <= TODAY(), "Red",
    IF(Status@row = "In Progress", "ANSWER",
    IF([End Date]@row > TODAY(), "Green", "Yellow")))))))
    

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I frequently do the same thing as @Andrée Starå where I break it down into sections on different rows for testing and then put it all together once everything is working.

  • @Paul Newcome I am trying to write a formula that will return red, yellow, green, or gray staus symbols based on the following criteria:

    • Gray  - If actual % complete = expected % complete
    • Green  - if actual % complete more than 10% > expected % complete
    • Yellow - if the actual % complete is within 10% of the expected % complete
    • Red - if the actual % complete is more than 10% below the expected % complete

    Where expected % complete is calculated as follows: =(TODAY() - Start@row) / (Finish@row - Start@row)

    Would you be able to help me with the formula? Also please feel free to correct my assumptions if they overlap or are not inclusive.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!