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:

Find more posts tagged with

Sheets

Formulas

Automations

Conditional Formatting

Project Management

Accepted answers

Leibel S

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

All comments

Mike Wilday

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?

Gunn_Jack

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

Leibel S

@Gunn_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...

Mike Wilday

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

Gunn_Jack

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

Leibel S

@Gunn_Jack

No need to add any cell references, just put in the formula as is.

Gunn_Jack

Thank you @Leibel S what a plonk i am lol.

Appreciate all your help everyone,

Thanks,

Jack

Tina Rustvold

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!

Mike Wilday

Yes it will work for any harvey colors that are found on Smartsheet's symbols columns. The full list is here:

https://help.smartsheet.com/articles/2480316-available-symbols-in-symbols-column

I hope that helps!!!

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.