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
-
Leibel S ✭✭✭✭✭✭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
-
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([email protected]= "Green", 1, IF([email protected]= "Yellow", 2, IF([email protected]= "Red", 3, IF([email protected]= "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([email protected]< 2, "Green", IF([email protected]= <3, "Yellow", IF([email protected]< 4, "Red", IF([email protected]>= 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 ✭✭✭✭
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 ✭✭✭✭✭✭
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"))
-
Mike Wilday ✭✭✭✭✭✭
I see, good point.@Leibel Sgave you a formula that should work for you too!
-
Leibel S ✭✭✭✭✭✭
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"))
-
Gunn_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:
I hope that helps!!!
Help Article Resources
Categories
@SarahI<\/a> Yes you can do that just use CHILDREN([COLUMN NAME])<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106901,"type":"question","name":"Can you use a COUNTIF formula to county symbols?","excerpt":"I have a list of goals for the year and we are using harvey ball symbols to indicate the percent complete an the red, yellow, green, gray balls to indicate the health\/status of the project. I would like to display a report on my dashboard that shows the number of projects we have in each harvey ball stage and also in each…","categoryID":322,"dateInserted":"2023-06-26T15:59:48+00:00","dateUpdated":"2023-06-26T16:00:12+00:00","dateLastComment":"2023-06-26T16:29:45+00:00","insertUserID":162764,"insertUser":{"userID":162764,"name":"Stephanie D","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Stephanie%20D","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mctWfgWrL7o!n-DX3ymVfmQ!msa32mT4_k9","dateLastActive":"2023-06-26T16:52:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162764,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T23:47:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3375593973,"url":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106901\/can-you-use-a-countif-formula-to-county-symbols","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106901,"commentID":382346,"name":"Re: Can you use a COUNTIF formula to county symbols?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382346#Comment_382346","dateInserted":"2023-06-26T16:29:45+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T23:47:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:24:48+00:00","dateAnswered":"2023-06-26T16:08:42+00:00","acceptedAnswers":[{"commentID":382328,"body":" Hi @Stephanie D<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =COUNTIF(Status:Status, \"Green\")<\/p> =COUNTIF([% Complete]:[% Complete], \"Half\")<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
