Help needed with averaging RYGB balls of children cells

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!

Find more posts tagged with

Sheets

Formulas

functionality

Project Management

Accepted answers

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.