Nested IF Formula - Project Health

Courtney Asada

Hello! I'm struggling to complete this formula without receiving errors. I am currently attempting to create formula in a project tracking sheet to return colored symbols (R,G,Y,G) into a "Project Health" Column. Below is the formula that I have so far. It is working up to this point, but when I try to add on IF statements (I've been attempting to utilize the best practice of building logical statements, one by one) to return the "red" dot, I receive error messages.

=IF(Status@row = "Complete", "Green", IF(Status@row = "Holding", "Gray", IF(Status@row = "Not Needed", "Gray", IF([Approval Status]@row = "Declined", "Yellow", IF(Deadline@row = TODAY(-7), Status@row <> "Complete", "Yellow")))))

The following is a set of all criteria I'm hoping to meet:

• "Project Health" turns "Green" for any given row that has a status of "Complete" or if the Deadline is "3 weeks out or further"
• "Project Health" turns "Yellow" for any given row that does not have a status of "Complete", "Holding", or "Not Needed", and the deadline is within the next 7 Days
• "Project Health" turns "Gray" for any given row that has a "status" of "Holding" or "Not Needed"
• "Project Health" turns "Red" for any given row that does not have a "Status" of "Complete", "Holding", or "Not Needed", and the deadline is within the next 3 days or is in the past.

Thanks in advance for the help!

Mary_A

@Courtney Asada Your formula had a Declined status but your bulleted list did not. So, I omitted that. Also, your description implied that the Status could be blank. So, I built a sheet that populates like this:

The formula I used:

=IF(Status@row = "Complete", "Green", (IF(OR(Status@row = "Holding", Status@row = "Not Needed"), "Gray", IF(AND(Deadline@row >= TODAY(3), Deadline@row <= TODAY(7)), "Yellow", IF(Deadline@row < TODAY(3), "Red", "Green")))))

First, I applied the colors based on status and disregarded the time constraints you had. If a Status is applied, it doesn't matter what the Deadline is. Second, if there is no Status set and a Deadline is approaching, that appears where you want to be displaying Yellow or Red.

By putting the colors first and the time last, the Status is evaluated and colors applied. If there isn't any Status, and a deadline is approaching, then the item begins to change color. For the purposes of this sample, TODAY = 5/8/2020.

Paul Newcome

I would suggest switching the Yellow and Red statements. Nested IFs stop on the first true value. This means that since less than 2 days away is also less than 7 days away, it will flag a true value for the Yellow and stop, never changing to Red. If you switch them around, it will first check for less than 3 days away and either generate a Red if true or move on to see if it is less than or equal to seven days away and run the true/false check accordingly.

This means that you can remove the AND function for Yellow which saves a few keystrokes, runs just a little more efficiently (one less thing for the formula to have to do), and cuts out a set of parenthesis.

=IF(Status@row = "Complete", "Green", IF(OR(Status@row = "Holding", Status@row = "Not Needed"), "Gray", IF(Deadline@row < TODAY(3), "Red", IF(Deadline@row <= TODAY(7), "Yellow", "Green"))))

Mary_A

@Courtney Asada Your formula had a Declined status but your bulleted list did not. So, I omitted that. Also, your description implied that the Status could be blank. So, I built a sheet that populates like this:

The formula I used:

=IF(Status@row = "Complete", "Green", (IF(OR(Status@row = "Holding", Status@row = "Not Needed"), "Gray", IF(AND(Deadline@row >= TODAY(3), Deadline@row <= TODAY(7)), "Yellow", IF(Deadline@row < TODAY(3), "Red", "Green")))))

First, I applied the colors based on status and disregarded the time constraints you had. If a Status is applied, it doesn't matter what the Deadline is. Second, if there is no Status set and a Deadline is approaching, that appears where you want to be displaying Yellow or Red.

By putting the colors first and the time last, the Status is evaluated and colors applied. If there isn't any Status, and a deadline is approaching, then the item begins to change color. For the purposes of this sample, TODAY = 5/8/2020.

Paul Newcome

I would suggest switching the Yellow and Red statements. Nested IFs stop on the first true value. This means that since less than 2 days away is also less than 7 days away, it will flag a true value for the Yellow and stop, never changing to Red. If you switch them around, it will first check for less than 3 days away and either generate a Red if true or move on to see if it is less than or equal to seven days away and run the true/false check accordingly.

This means that you can remove the AND function for Yellow which saves a few keystrokes, runs just a little more efficiently (one less thing for the formula to have to do), and cuts out a set of parenthesis.

=IF(Status@row = "Complete", "Green", IF(OR(Status@row = "Holding", Status@row = "Not Needed"), "Gray", IF(Deadline@row < TODAY(3), "Red", IF(Deadline@row <= TODAY(7), "Yellow", "Green"))))

Courtney Asada

Thank you both so much, this is extremely helpful!

Paul Newcome

Happy to help! 👍️