Formula for health status of overall project
I was using the below formula to provide the health status of the overall project, but its not working. The result (green, yellow or red) is supposed to come back with the majority of the tasks that are in that color. In doing my test, most of my tasks were in red status, but the result for the overall project came back yellow.
=IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))
Is this the correct formula?
Thank you!
Answers
-
@Francine Blue Where did you put this formula? It should work...
-
Thank you. Please see attachment (highlighted in yellow is where I have the formula).
But for all the tasks underneath, I have a different formula (below) to include a gray color for completed task.
=IF(OR(ISBLANK(Start@row), Start@row > TODAY(1), ([% Complete]@row = 1)), "Gray", IF(AND(Finish@row < TODAY(1), [% Complete]@row <> 1), "Red", IF(AND(Finish@row - 3 < TODAY(1), [% Complete]@row <> 1), "Yellow", IF(AND(Finish@row > TODAY(1), Start@row < TODAY(1)), "Green", "Gray"))))
-
Also in addition to the above, is the overall project status not changing because of the below formula for the 'Status' column?
=IF(AND([% Complete]@row = 1), "Complete", IF(AND(Finish@row < TODAY(1), [% Complete]@row < 1), "Late", IF(AND(Start@row < TODAY(1)), "In Progress", "Not Started")))
-
I believe your issues lies in that you are expecting the formula to evaluate all the children rows as well as the children's children...
The CHILDREN() formula only looks at the sub rows beneath it not the sub sub rows...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!