Adding a element to formula to make STATUS of "Cancelled" = HEALTH (Green)
Here is my current formula that updates the % Complete in my plan. I need to have something in the formula that recognizes the status type of "Cancelled" and marks it 100% Complete like the "Complete" status so that the Health calculates that task/item as green instead of Red. Current when someone marks a task as Cancelled it flags it as Red HEALTH in the sheet and makes their reporting look like there are issues with the plan/project. We want to correct this issue so reporting/HEALTH is correct.
% Complete Formula:
=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(Status@row = "Completed", 1, IF(OR(Status@row = "In Progress", Status@row = "Proofing Approved", Status@row = "Proofing Rejected"), 0.5, IF(Status@row = "On Hold", 0.25, IF(Status@row = "Not Started", 0, "")))))
Health Formula:
=IF([End Date]@row = "", "Green", IF(OR(Level@row = 0, Level@row = 1), IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Blue"))), IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY(7)), "Yellow", "Green")))))
Best Answer
-
Try this...
=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(OR(Status@row = "Cancelled", Status@row = "Completed"), 1, IF(OR(Status@row = "In Progress", Status@row = "Proofing Approved", Status@row = "Proofing Rejected"), 0.5, IF(Status@row = "On Hold", 0.25, IF(Status@row = "Not Started", 0, "")))))
Answers
-
Try this...
=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(OR(Status@row = "Cancelled", Status@row = "Completed"), 1, IF(OR(Status@row = "In Progress", Status@row = "Proofing Approved", Status@row = "Proofing Rejected"), 0.5, IF(Status@row = "On Hold", 0.25, IF(Status@row = "Not Started", 0, "")))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!