Averaging RGB balls
I'm hoping this is quite simple, but my simple mind is struggling to piece it together.
I have a Parent Row with multiple Children but only one hierarchy below it and i want the parent row to average the status of the Red, Green and Blue balls on the Children rows...make sense?
Help would be much appreciated, screen shot below:
Best Answer

No need to add any cell references, just put in the formula as is.
=IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Blue"), "Green", "Blue"), IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Blue"), "Red", "Blue"))
Answers

I did this with a helper column and a few formulas.
 Add a number/text column to the right of the symbol column. I titled mine Scoring
 Add the following formula to every child row: =IF(Status@row = "Green", 1, IF(Status@row = "Yellow", 2, IF(Status@row = "Red", 3, IF(Status@row = "Blue", 4)))) REPLACE Status with your column Name and use brackets [ ] if your name has a space or ends in a number.
 In the helper column, for every parent row add the following formula: =AVG(CHILDREN()) This will provide an average for your children rows.
 Then in the parent row of the symbol column add this formula to each parent: =IF(Scoring@row < 2, "Green", IF(Scoring@row = <3, "Yellow", IF(Scoring@row < 4, "Red", IF(Scoring@row >= 4, "Blue", "Blah"))))
This will provide you an averaged score based on the colors.
I rated them Green 1, Yellow 2, Red 3, and Blue 4.
Would this work for you?

Hi @Mike Wilday
Thank you for your response
It does work to an extent but not exactly what i was after.
The reason being I think is because it gives the RYGB a weighting, and in this example all are equal. Maybe average was the wrong way of describing it, I guess what I essentially want is for it to show me which colour appears the most.
For example, in the screenshot above there are 16 sections, if 9 are Green, the parent row should show as Green. With your formula, if i have 12 Green and 4 Blue it averages to a Red. (Note, i edited the formula and used Green  1, Red  2 and Blue  3):
=IF(AND([Scoring 1]@row < 1.5, [Scoring 1]@row > 1), "Green", IF(AND([Scoring 1]@row <= 2.5, [Scoring 1]@row >= 1.5), "Red", IF([Scoring 1]@row > 2.5, "Blue", "")))
Should I have stuck with yours?
Thanks,
Jack

Below is a formula I think would work for you.
You can switch colors around (keep in mind it only works for 3 colors). Also, the order of the colors makes a difference to which color it returns in a tie...
=IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Blue"), "Green", "Blue"), IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Blue"), "Red", "Blue"))

I see, good point. @Leibel S gave you a formula that should work for you too!

Thank you @Leibel S
However this is only giving me a blue result no matter what, have i done something wrong in the formula?
Thanks,
Jack

No need to add any cell references, just put in the formula as is.
=IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Blue"), "Green", "Blue"), IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Blue"), "Red", "Blue"))


I just found this thread while researching something similar, but we DO use 4 colors. Can this formula be adjust to work for more harvey ball colors? Or, do we need to assign an a numerical weight in a helpfer column and work from there? Thanks!

Yes it will work for any harvey colors that are found on Smartsheet's symbols columns. The full list is here:
I hope that helps!!!
Help Article Resources
Categories
Check out the Formula Handbook template!