Nested If And Not Formula

Options
nlehmann
nlehmann ✭✭
edited 02/21/23 in Formulas and Functions

Hi all,

I'm working to write a formula that displays a health ball (R,Y,G,B) that's based on a variety of logical expressions.

Below is what I am aiming to achieve.

Status@row = Complete, then Green

Status@row = On Hold, then Blue

Finish@row = Blank, then Blank

Here is where it gets tricky...

If Finish is less than today and status is NOT equal to Complete, then Red

If status = In Progress, and finish is equal to Today or two days previous (in other words, 2 or fewer days overdue), then Yellow

If status = In Progress and finish is three days previous or more (in other words, 3 or more days overdue), then Red


I'm getting caught up somewhere on the proper order of things. Any help would be appreciated!

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @nlehmann

    What I try to do on these is to first figure out all the criteria for a particular color, and then start with the color that has the most specific criteria.

    In your criteria, you want it to be Red where:

    1. The Finish date is less than today (so, in the past) and where the Status is not equal to Complete.
    2. The Status is equal to In Progress and where the Finish Date is at least three days in the past.

    Criteria set #2 is included in Criteria set #1, because the Status is equal to something other than Complete, and the Finish date is in the past. So we can drop Criteria set #2.

    Yellow is pretty specific and straightforward, as are Green, Blue, and blank.

    I would start with Green, because it's the most specific. IF formulas work from left to right and stop when it finds a true logical expression. If status is Complete, it's Green, no matter what any other field has in it. Same thing with Blue and blank. So we'll go with Green - Blue - blank - Yellow - Red.

    Try this formula:

    =IF(Status@row = "Complete", "Green", IF(Status@row = "On Hold", "Blue", IF(Finish@row = "", "", IF(AND(Status@row = "In Progress", Finish@row >= TODAY(-2)), "Yellow", "Red"))))

    Logic: If Status is Complete, Green;

    If Status is On Hold, Blue;

    If Finish is blank, blank;

    If Status is In Progress AND if Finish is greater than or equal to 2 days ago, Yellow;

    If it doesn't meet any of the above criteria, Red.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!