Nested IF Formula - Project Health
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!
Best Answers
-
@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.
-
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"))))
Answers
-
@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.
-
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"))))
-
Thank you both so much, this is extremely helpful!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!