Help with RYG Balls  Looking for an Average
Good morning/afternoon. I have been searching in the help articles and community for an answer to my question, to no avail. I have tried a few different suggestions but nothing has worked so far. Here is my dilemma. I have a sheet that has linked in RYG balls for health from another sheet. There are three sections, with parent child relationships on each section. I would like to calculate an average for the health of each of the three sections to determine the overall health of each. Any suggestions? TIA!
Best Answers

Hi Paul.
I would definitely need an average. This is needed to provide an accurate status for the whole.

Ok. You would need to start by assigning a number to each color in a different column.
=IF(Health@row = "Red", 0, IF(Health@row = "Yellow", 1, IF(Health@row = "Green", 2)))
Then in the parent rows you would average these numbers and convert it back to a RYG value using a similar IF statement.
=IF(ROUND([Helper Column]@row) = 0, "Red", IF(ROUND([Helper Column]@row) = 1, "Yellow", IF(ROUND([Helper Column]@row) = 2, "Green")))

Hi Paul,
Thank you so much! That did help. :)
Laurie Bearden

Excellent! Happy to help. 👍️
Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution will know that one may be found here.
Answers

Do you want an average, or would you want something more along the lines of if there is even 1 red, then turn the parent red. If there are no reds and at least one yellow then turn the parent yellow?
I ask because taking an average could end up displaying a green on the parent row even though there is a red in one of the child rows.
Both options can be accomplished.

Hi Paul.
I would definitely need an average. This is needed to provide an accurate status for the whole.

Ok. You would need to start by assigning a number to each color in a different column.
=IF(Health@row = "Red", 0, IF(Health@row = "Yellow", 1, IF(Health@row = "Green", 2)))
Then in the parent rows you would average these numbers and convert it back to a RYG value using a similar IF statement.
=IF(ROUND([Helper Column]@row) = 0, "Red", IF(ROUND([Helper Column]@row) = 1, "Yellow", IF(ROUND([Helper Column]@row) = 2, "Green")))

Hi Paul,
Thank you so much! That did help. :)
Laurie Bearden

Excellent! Happy to help. 👍️
Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution will know that one may be found here.

@Paul Newcome Hi Paul, I'm looking for this solution too, but I'd like '...something more along the lines of if there is even 1 red, then turn the parent red. If there are no reds and at least one yellow then turn the parent yellow?'
Could you help me out with that? Many thanks, Lisa

@LisaB:) Try something like this...
=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", "Green"))

Works great! Thanks Paul

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!