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

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

Nichole Pyle

This is fantastic! Thank you so much, I realized the one component of the formula that you were struggling with is not feasible given the way we structured our template so no worries on incorporating it. Your help with this is very much appreciated.