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 ✭✭✭✭✭我刚刚发现这个线程虽然研究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
=IF(AND(.......), <\/strong>\"Critical\")<\/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":106881,"type":"question","name":"Calculate % complete with Date Range and Today's date","excerpt":"Hello community, I need a formula to return % Complete based on a start - end date range and today's date. So for example: Date Range: 06\/18\/23 - 06\/30\/23 Today's date: 06\/26\/23 As there are 12days in this date range, and today's date is day 8 out of 12, the percentage complete would be 66.6% Thanks in advance!","categoryID":322,"dateInserted":"2023-06-26T11:40:17+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T15:14:49+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T15:15:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T15:19:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":42,"score":null,"hot":3375575706,"url":"https:\/\/community.smartsheet.com\/discussion\/106881\/calculate-complete-with-date-range-and-todays-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106881\/calculate-complete-with-date-range-and-todays-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106881,"commentID":382293,"name":"Re: Calculate % complete with Date Range and Today's date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382293#Comment_382293","dateInserted":"2023-06-26T15:14:49+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T15:19:24+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-26T13:50:37+00:00","dateAnswered":"2023-06-26T13:24:44+00:00","acceptedAnswers":[{"commentID":382268,"body":" =MAX(MIN((TODAY() - Start@row) \/ (Finish@row - Start@row), 1), 0)<\/p> This does the percentage:<\/p> (TODAY() - Start@row) \/ (Finish@row - Start@row)<\/p> This caps it at 100%:<\/p> MIN(..............., 1)<\/p> This keeps it from going negative for future tasks:<\/p> MAX(..............., 0)<\/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"}]},{"discussionID":106871,"type":"question","name":"Parent Status based on Children Harvy ball status.","excerpt":"Dear team, I am trying to build a status update tracker, for teams to update each milestone. They are expected to update each milestone using the harvey ball status (0% \/25% \/50 % \/75% \/100%). I am trying to automate the status of parent based on children's Harvey ball status as below with logic as \" If the status of all…","categoryID":322,"dateInserted":"2023-06-25T03:09:21+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T02:39:36+00:00","insertUserID":162738,"insertUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162738,"lastUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3375410937,"url":"https:\/\/community.smartsheet.com\/discussion\/106871\/parent-status-based-on-children-harvy-ball-status","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106871\/parent-status-based-on-children-harvy-ball-status","format":"Rich","lastPost":{"discussionID":106871,"commentID":382196,"name":"Re: Parent Status based on Children Harvy ball status.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382196#Comment_382196","dateInserted":"2023-06-26T02:39:36+00:00","insertUserID":162738,"insertUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/81O3LR1GWG7D\/picture1.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Picture1.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T02:28:56+00:00","dateAnswered":"2023-06-25T04:29:41+00:00","acceptedAnswers":[{"commentID":382168,"body":" With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:<\/p> =IF(COUNTIF(CHILDREN([BallCell]@row), \"Empty\") = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), \"Full\") = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p> If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:<\/p> =IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p> In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.<\/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":[]}],"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":[]}">
<\/p>
<\/p>
<\/p>
