Formula for health status of overall project

Francine Blue

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!

Leibel S

@Francine Blue Where did you put this formula? It should work...

Francine Blue

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"))))

Capture10.JPG

Francine Blue

Also in addition to the above, is the overall project status not changing because of the below formula for the 'Status' column?

Capture10.JPG

=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")))

Leibel S

@Francine Blue

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...