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
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
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
NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox
Connect with me on LinkedIn
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!