Help needed with averaging RYGB balls of children cells

Naveen Srikakulam

Hi, I wanted to know how I can average the RYGB balls of the children cells. Something on 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. If there are no reds and yellows, and at least one green, turn the parent green. If all the balls are blue, turn the parent blue. Please help. Thanks!

Naveen Srikakulam

Thanks Connor. I have built a different formula using ur formula and it works great.

=IF(OR(COUNTIF(CHILDREN(), "Yellow") > 3, COUNTIF(CHILDREN(), "Red") > 2), "Red", IF(OR(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Red") > 0), "Yellow", IF(COUNTIF(CHILDREN(), "Blue") = (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))), "Blue", IF(OR(COUNTIF(CHILDREN(), "Green") > 0, AND(COUNTIF(CHILDREN(), "Blue") < (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))), COUNTIF(CHILDREN(), "Blue") > 0)), "Green", "")))) 

Connor Hartford

Hi Naveen,

I've done something similar but took a different approach to it. What I did was give a weight (or multiplier value) to each color. Then you count the colors, and multiply them by the weight. For me, if the weighted score is twice as much as the number of balls, I make it 'Red' and if it's 1.5 times as much as the number of balls I make it 'Yellow', anything less is 'Green'. See my formula below.

=IF((COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3) >= (COUNT(CHILDREN()) * 2), "Red", IF((COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3) >= (COUNT(CHILDREN()) * 1.5), "Yellow", "Green"))

Naveen Srikakulam

Thanks Connor. I have built a different formula using ur formula and it works great.

=IF(OR(COUNTIF(CHILDREN(), "Yellow") > 3, COUNTIF(CHILDREN(), "Red") > 2), "Red", IF(OR(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Red") > 0), "Yellow", IF(COUNTIF(CHILDREN(), "Blue") = (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))), "Blue", IF(OR(COUNTIF(CHILDREN(), "Green") > 0, AND(COUNTIF(CHILDREN(), "Blue") < (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), ISBLANK(@cell))), COUNTIF(CHILDREN(), "Blue") > 0)), "Green", ""))))