Parent Color Symbol, Based on "Average" of Child Symbols

SherryFox

Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive it was not needed. So we are doing it how the Stakeholder wants. I thought it would be nice to have a status symbol that would be an "Average"

— Red - None of the Child Rows complete, the child rows would all be Red

— Yellow - Some of the Child Rows have been Complete. The child rows would show multiple colors, with at least one red.

— Green - All of the child rows are Green

In the Example below, the parent is on Row 1 and the Children are on rows 2-6. As there are 2 that are Red, 2 Green and 1 yellow, that would make this Parent a Yellow, as the Children meet the Yellow criteria above.

Jessica Miller

Hi! I think that averages require associated number values (which I usually put in a helper column)
Without that the best I could suggest would be:

=IF(COUNTIF(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow"))

This means if all rows were red parent would be red, if all were green parent would be green, if a mix then parent would be yellow.

• Would like to see how others respond though as there is probably a better way, which more accurately captures the weighting of the RAG status! 😊

Jessica Miller

Hi! I think that averages require associated number values (which I usually put in a helper column)
Without that the best I could suggest would be:

=IF(COUNTIF(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow"))

This means if all rows were red parent would be red, if all were green parent would be green, if a mix then parent would be yellow.

• Would like to see how others respond though as there is probably a better way, which more accurately captures the weighting of the RAG status! 😊

SherryFox

@Jessica Miller ,

That is exactly what I was trying to do. And I want to avoid helper columns whenever possible. Thanks so much!!!!