Help needed with averaging RYGB balls of children cells

Options
Naveen Srikakulam
Naveen Srikakulam ✭✭
edited 07/05/20 in Formulas and Functions

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!

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Naveen Srikakulam
    Naveen Srikakulam ✭✭
    Answer ✓
    Options

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

    · Share on FacebookShare on Twitter

Answers

  • Connor Hartford
    Connor Hartford ✭✭✭✭✭
    Options

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


    Connor Hartford

    · Share on FacebookShare on Twitter
  • Naveen Srikakulam
    Naveen Srikakulam ✭✭
    Answer ✓
    Options

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!