Health formula error

Michelle Maas

I'm learning how to set up the Health column. I have followed the various conversations and got to a point where most of the formula is working. I am stuck on an issue and would like help.

In the Status column we have the below series:

"Complete" or "In Progress" = "Green" | "On Hold" or "Forecast" = "Blue" | "Schedule" or "Not Started = "Yellow" | "Delayed" or "Cancelled" or if date is greater than TODAY = "Red"

The Health column is picking up an error in the Parent and Children rows. The other rows are working well. The Parent/Children are identifying 100% Complete = "Green", blank date columns = blank Health, but it is missing all the others. I would like it to show the Parent/Children as the AVG or highest count overall of any Symbol (colour) under each section. I have added a screenshot and below is my formula:

=IF(OR(ISBLANK(Start@row), ISBLANK(Finish@row)), "", IF([% Complete]@row = 1, "Green", IF(Start@row < TODAY(), "Red", IF(COUNT(CHILDREN(Start@row), ISBLANK(@cell)), IF(Status@row = "Not Started", "Yellow", IF(Status@row = "In Progress", "Green", IF(Status@row = "Schedule", "Yellow", IF(Status@row = "Forecast", "Blue", IF(Status@row = "On Hold", "Blue", IF(Status@row = "Delayed", "Red", IF(Status@row = "Cancelled", "Red", IF(Status@row = "Complete", "Green"))))))))))))

Jen Lange

Hi @Michelle Maas.

I've combined the formulas you referenced above into the Health column, which I think is your first objective. From here, you mentioned that you want an AVG count in the Parent/Children rows. What count do you want averaged?

If you clarify on the above, I think I can achieve your goal. Let me know if this is progressing in the right direction.

=IF(COUNT(ANCESTORS([Task Name]@row)) <= 1, COUNT(ANCESTORS([Task Name]@row)), IF(OR(Status@row = "In Progress", Status@row = "Complete"), "Green", IF(OR(Status@row = "Schedule", Status@row = "Not Started"), "Yellow", IF(OR(Status@row = "Forecast", Status@row = "On Hold"), "Blue", IF(OR(Status@row = "Delayed", Status@row = "Cancelled", Status@row = "Unresourced"), "Red")))))

Michelle Maas

If I add a further formula, IF(NOT(Start@row > TODAY()), "Yellow", this covers anything that is in current date, but makes it all yellow, when I want it to identify the other colours. I feel I am so close, just missing something...

=IF(OR(ISBLANK(Start@row), ISBLANK(Finish@row)), "", IF([% Complete]@row = 1, "Green", IF(Start@row < TODAY(), "Red", IF(NOT(Start@row > TODAY()), "Yellow", IF(COUNT(CHILDREN(Start@row), ISBLANK(@cell)), IF(Status@row = "Not Started", "Yellow", IF(Status@row = "In Progress", "Green", IF(Status@row = "Schedule", "Yellow", IF(Status@row = "Forecast", "Blue", IF(Status@row = "On Hold", "Blue", IF(AND(ISBLANK(Start@row = "On Hold"), IF(Status@row = "Delayed", "Red", IF(Status@row = "Cancelled", "Red", IF(Status@row = "Complete", "Green")))))))))))))))

Paul Newcome

My suggestion would be to insert 4 helper columns. These can be hidden after setting everything up to keep the sheet looking clean and will help keep the formulas a little more manageable.

Name one each of the colors and use a COUNTIFS to count how many children are in each of the colors:

=IF(COUNT(CHILDREN(Status@row)) <> 0, COUNTIFS(CHILDREN(Status@row), "Green"))

Then we can use a nested IF to output whichever color has the highest count.

=IF([Red Count]@row = MAX([Red Count]@row:[Blue Count]@row), "Red", IF([Yellow Count]@row = MAX([Red Count]@row:[Blue Count]@row), "Yellow", IF([Green Count]@row = MAX([Red Count]@row:[Blue Count]@row), "Green", IF([Blue Count]@row = MAX([Red Count]@row:[Blue Count]@row), "Blue"))))

Now that we have the formula for the parent rows established, we can nest that inside of an IF statement to say that if it is a parent row then run the parent row formula, otherwise run the child row formula.

=IF(COUNT(CHILDREN()) <> 0, parent_row_formula, child_row_formula)

=IF(COUNT(CHILDREN()) <> 0, IF([Red Count]@row = MAX([Red Count]@row:[Blue Count]@row), "Red", IF([Yellow Count]@row = MAX([Red Count]@row:[Blue Count]@row), "Yellow", IF([Green Count]@row = MAX([Red Count]@row:[Blue Count]@row), "Green", IF([Blue Count]@row = MAX([Red Count]@row:[Blue Count]@row), "Blue")))), IF(OR(ISBLANK(Start@row), ISBLANK(Finish@row)), "", IF([% Complete]@row = 1, "Green", IF(Start@row < TODAY(), "Red", IF(COUNT(CHILDREN(Start@row), ISBLANK(@cell)), IF(Status@row = "Not Started", "Yellow", IF(Status@row = "In Progress", "Green", IF(Status@row = "Schedule", "Yellow", IF(Status@row = "Forecast", "Blue", IF(Status@row = "On Hold", "Blue", IF(Status@row = "Delayed", "Red", IF(Status@row = "Cancelled", "Red", IF(Status@row = "Complete", "Green")))))))))))))

Michelle Maas

Hi Paul, thanks for responding. I get #UNPARSEABLE. I want to avoid doing extra columns. I already have one for managing the formatting of Parent and Children rows, and the other for Health.

When I look at the formula I created, I keep thinking in the beginning there is something missing and maybe I need to add an AND or OR to the status@row colours or remove a clashing part.

I have a formula that if it has no date, then it remains blank, if it is complete at 100% = green, if it is overdue = red, and if it is today or in the future, then it should not affect the rest of the settings, but here I think is the issue because I have told it for today or future = yellow, but it should be that it does not affect the Health colour already assigned to the status selection. Also if it is i.e. On Hold or Forecast with no date, it should still be assigned to the Blue symbol. Any thoughts on a workaround?

Michelle Maas

I'm going round and round with this issue and can't seem to get it resolved. Please can someone help me with the formulas as I can't quite get it right. Below is what I am trying to achieve:

In the Parent column I have a formula that picks up whether there is a section "heading" or "subheading". This works the conditional formatting for adding specific colours to those rows.

=IF(COUNT(ANCESTORS([Task Name]1)) < COUNT(ANCESTORS([Task Name]2)), COUNT(ANCESTORS([Task Name]1)))

The Status column has the below series:

"Complete" or "In Progress" = "Green" | "On Hold" or "Forecast" = "Blue" | "Schedule" or "Not Started = "Yellow" | "Delayed", "Cancelled", or "Unresourced" = "Red"

The Health column has the below formula to capture the above status. What I want to achieve is for the below formula to have an average count in the Parent and Children (header) rows with a combined formula that works in all rows. Then I can copy the formula down the sheet in the Health column.

=IF(Status@row = "In Progress", "Green", IF(Status@row = "Schedule", "Yellow", IF(Status@row = "Not Started", "Yellow", IF(Status@row = "Forecast", "Blue", IF(Status@row = "On Hold", "Blue", IF(Status@row = "Delayed", "Red", IF(Status@row = "Cancelled", "Red", IF(Status@row = "Unresourced", "Red", IF(Status@row = "Complete", "Green")))))))))

Jen Lange

Hi @Michelle Maas.

I've combined the formulas you referenced above into the Health column, which I think is your first objective. From here, you mentioned that you want an AVG count in the Parent/Children rows. What count do you want averaged?

If you clarify on the above, I think I can achieve your goal. Let me know if this is progressing in the right direction.

=IF(COUNT(ANCESTORS([Task Name]@row)) <= 1, COUNT(ANCESTORS([Task Name]@row)), IF(OR(Status@row = "In Progress", Status@row = "Complete"), "Green", IF(OR(Status@row = "Schedule", Status@row = "Not Started"), "Yellow", IF(OR(Status@row = "Forecast", Status@row = "On Hold"), "Blue", IF(OR(Status@row = "Delayed", Status@row = "Cancelled", Status@row = "Unresourced"), "Red")))))

Michelle Maas

Hi @Jen Lange

Thank you for your response. I realised I was not clear on the count or confused actually. I thought what I needed was a rollup in the Parent/Children for the colour of the highest count, so if there are mostly reds, then the symbol in the Parent/Children is red. It's all new to me and my overall intention of creating these columns is to get the best out of them in reporting, i.e. at a glance either via a dashboard or the report, I can see which projects are lagging behind or on hold, or running well. The Parent/Children would cover each section so I would be able to drill down quicker if I could see where the issues were. I don't know if I am on the right track and want to get a better handle on our project management. What would you advise?

Michelle Maas

Deleted comment

Michelle Maas

@Jen Lange

The formula I was using (a) could be linked to conditional formatting and picks up as many headers as I add, but the one you've suggested (b) only recognises the first and 2nd header in conditional formatting. How do you fix the parent formula so it recognises all the subheaders?

a) =IF(COUNT(ANCESTORS([Task Name]1)) < COUNT(ANCESTORS([Task Name]2)), COUNT(ANCESTORS([Task Name]1)))

b) =IF(COUNT(ANCESTORS([Task Name]@row)) <= 1, COUNT(ANCESTORS([Task Name]@row)),

Michelle Maas

I managed to figure it out. Thanks @Jen Lange and @Paul Newcome

Jen Lange

That's great, @Michelle Maas.

Also, thank you for your engagement and contribution to our community. Your inquiry is likely to assist other members AND it helps me flex my skills.

If you appreciate my response, please recognize the effort by with an "Insightful" or "Vote Up" selection. Thanks. Have a great weekend!

-Jen