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
-
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
-
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! 😊
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!