Help with RYG Balls - Looking for an Average

Laurie Bearden

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!

Health Average - 3-13-20.docx

Laurie Bearden

Hi Paul.

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

Paul Newcome

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")))

Laurie Bearden

Hi Paul,

Thank you so much! That did help. :)

Laurie Bearden

Paul Newcome

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

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.

Laurie Bearden

Hi Paul.

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

Paul Newcome

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")))

Laurie Bearden

Hi Paul,

Thank you so much! That did help. :)

Laurie Bearden

Paul Newcome

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.

LisaB:-)

@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

Paul Newcome

@LisaB:-) Try something like this...

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", "Green"))

LisaB:-)

Works great! Thanks Paul

Paul Newcome

Happy to help. 👍️