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

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.

Sherry Fox

Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies

EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

Core App Certified 🦊

NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

Connect with me on LinkedIn

Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.

Best Answer

  • Jessica Miller
    Jessica Miller ✭✭✭
    Answer ✓

    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! 😊

Answers

  • Jessica Miller
    Jessica Miller ✭✭✭
    Answer ✓

    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
    SherryFox ✭✭✭✭

    @Jessica Miller ,

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

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

    Core App Certified 🦊

    NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

    Connect with me on LinkedIn

    Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!