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!

Find more posts tagged with

Formulas

Project Management

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.