IF formula for color indicators

Nichole Pyle

Hello,

 I’m hoping for some help with color indicators for a project schedule. If someone could help with the formula I would greatly appreciate it.

Here is what we are looking for using the @ row references if possible.

·      If 0 day duration and % Complete = 100, return Blue

·      If 0 day duration and the scheduled finish is at least seven days away, return Clear

·      If 0 day duration and % Complete is < 80, return Red

·      If not 0 day duration and % Complete is between 80 and 99, return Yellow

·      If not 0 day duration and % Complete = 100, return Blue

·      If it’s not 0 day duration and not scheduled to start yet, return Clear

·      If it’s not 0 day duration and past the scheduled finish date, return Red

·      If not 0 day duration and % complete >= the prorated expected completion, return Green, Otherwise, return Yellow.

·      If not 0 day duration and Start date is in the past AND finish date is in the next 7 days AND % complete less than 50%, return Red

Column Names:

% complete

Start

Finish

Thank you for any help you can provide

Sean Morgan

Hello @Nichole Pyle !,

I was able to get nearly all those criteria in place, but the only one I was unable to understand was " If not 0 day duration and % complete >= the prorated expected completion, return Green, Otherwise, return Yellow." as I was unsure how the "expected completion" was calculated within your sheet. If you are able to share a touch more info on this, I will be able to implement this into the formula. Nonetheless, Here is what I was able to come up with so far. Please see the screenshot showing it meeting 8/9 criteria

The formula I used was: =IF(AND(Duration@row = 0, Percentage@row = 1), "Blue", IF(AND(Duration@row = 0, [Scheduled Finish]@row > TODAY(7)), "", IF(AND(Duration@row = 0, Percentage@row < 0.8), "Red", IF(AND(Duration@row <> 0, Percentage@row > 0.8, Percentage@row < 0.99), "Yellow", IF(AND(Duration@row <> 0, Percentage@row = 1), "Blue", IF(AND([Scheduled Start]@row > TODAY(), Duration@row <> 0), "", IF(AND(Duration@row <> 0, [Scheduled Finish]@row < TODAY()), "Red", IF(AND(Duration@row <> 0, [Scheduled Start]@row < TODAY(), [Scheduled Finish]@row = TODAY(7)), "Red"))))))))

Here is the screenshot below. Criteria 8 is blank as it's the one question I was unable to fully understand:

Let me know if you have any questions!

Regards

Sean