Nested If And Not Formula

nlehmann

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!

Jeff Reisman

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