Task Health Formula - Color Ball

Lyndsay R.

In our project plans we have child level task health and then parent level task health in the same column, indicated by a color ball. Previously, we had separate formulas for each level, but I have been working with Pro Desk to implement a formula that will work for all levels and can be a column formula. We are so close to having it how we need - the child level task pieces of the formula are working great. For parent level, we almost have it where we need. The problem I'm having is if the child tasks are a mixed bag that don't necessarily meet the criteria in the formula, the else green statement is catching it. And while the formula is working as expected, in real world scenarios, it isn't accurate. I'm stumped on how we can possibly put criteria in the formula that could take into account every possible scenario of mixed child level status. I'll add picture examples of what is occurring with the mixed child level statuses and the formula as we have it today is below:

=IFERROR(IF([Summary Header]@row = 1, "", IF(Level@row < 2, IF((COUNTIF(CHILDREN(), "Not Defined") / COUNT(CHILDREN()) > 0.6), "Not Defined", IF((COUNTIF(CHILDREN(), "Yellow") / COUNT(CHILDREN()) > 0.5), "Yellow", IF((COUNTIF(CHILDREN(), "Red") / COUNT(CHILDREN()) >= 0.5), "Red", IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", "Green")))), IF([% Complete]@row >= 1, "Blue", IF(ISBLANK([Start Date]@row), "Not Defined", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < WORKDAY(TODAY(), 3), "Yellow", "Green")))))), "No Data")

Scenario: 3 yellow children, 1 red, and 2 Not Defined is returning parent level = green. While it's accurate according to the formula, in a real world scenario, this should likely fall to yellow.

Scenario: 3 red children, 1 yellow, and 2 Not Defined returning parent level = green. While it's accurate according to the formula, in a real world scenario, this should likely fall to red.

Paul Newcome

What is the logic you are hoping to achieve? Whichever color is most populated? How would the "Not Defined" come into play?

Lyndsay R.

I think the color most populated would work. Not Defined means the user has not put dates to the child level task. And then if so many of the children are Not Defined, then parent goes Not Defined. Basically a way to say the Project Manager hasn't built out that part of the plan yet and we don't want it to give a "false" color.

Ipshita

Hello @Lyndsay R. have you tried the MAX(Children) for the roll up line/parent line of the symbol column to see if that picks up the right color? Also, always wrap all your formula with the =IFERROR( "") so that it either returns a value when a value is available or a blank when cells are blank and does not return #INVALID for your formula line.

Hope this helps.

Cheers!

Ipshita

Paul Newcome

@Ipshita The MAX function will not work because that particular function only works with numbers. It will not work with text or symbols.

@Lyndsay R. If I understand correctly, you basically want to output whatever is populating the most amount of children, and "Not Defined" is (in this instance) considered another "color". If that is the case, we would use something along the lines of...

=IF(COUNTIFS(CHILDREN(), @cell = "Red")>= COUNTIFS(CHILDREN(), @cell <> "Red"), "Red", IF(COUNTIFS(CHILDREN(), @cell = "Yellow")>= COUNTIFS(CHILDREN(), @cell <> "Yellow"), "Yellow", IF(COUNTIFS(CHILDREN(), @cell = "Green")>= COUNTIFS(CHILDREN(), @cell <> "Green"), "Green", IF(COUNTIFS(CHILDREN(), @cell = "Not Defined")>= COUNTIFS(CHILDREN(), @cell <> "Not Defined"), "Not Defined"))))

Ipshita

@Paul Newcome you are absolutely right. I should have tested the formula first. There is no end to knowledge and I am still learning. Thanks for your response! :)

Cheers,

Ipshita

Paul Newcome

@Ipshita No worries at all.