Averaging RGB balls

Gunn_Jack
Gunn_Jack ✭✭✭✭
inFormulas and Functions

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:

image.png


Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

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

    · Share on Facebook Share on Twitter

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    I did this with a helper column and a few formulas.

    1. Add a number/text column to the right of the symbol column. I titled mine Scoring
    2. Add the following formula to every child row: =IF((邮件布鲁泰克ted]= "Green", 1, IF((邮件布鲁泰克ted]= "Yellow", 2, IF((邮件布鲁泰克ted]= "Red", 3, IF((邮件布鲁泰克ted]= "Blue", 4)))) REPLACE Status with your column Name and use brackets [ ] if your name has a space or ends in a number.
    3. In the helper column, for every parent row add the following formula: =AVG(CHILDREN()) This will provide an average for your children rows.
    4. Then in the parent row of the symbol column add this formula to each parent: =IF((邮件布鲁泰克ted]< 2, "Green", IF((邮件布鲁泰克ted]= <3, "Yellow", IF((邮件布鲁泰克ted]< 4, "Red", IF((邮件布鲁泰克ted]>= 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.

    这将为你工作吗?

    · Share on Facebook Share on Twitter
  • Gunn_Jack
    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?

    谢谢,

    Jack

    · Share on Facebook Share on Twitter
  • Leibel S
    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...

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

    · Share on Facebook Share on Twitter
  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

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

    · Share on Facebook Share on Twitter
  • Gunn_Jack
    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?

    image.png

    谢谢,

    Jack

    · Share on Facebook Share on Twitter
  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

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

    · Share on Facebook Share on Twitter
  • Gunn_Jack
    Gunn_Jack ✭✭✭✭

    Thank you@Leibel Swhat a plonk i am lol.

    Appreciate all your help everyone,

    谢谢,

    Jack

    · Share on Facebook Share on Twitter
  • Tina Rustvold
    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!

    · Share on Facebook Share on Twitter
  • Mike Wilday
    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!!!

    · Share on Facebook Share 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公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Thank you, as always I was trying to make it too hard!!!!<\/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":106971,"type":"question","name":"Using SUMIFS Formula","excerpt":"Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, \"AMR\", {leadership}, \"Truttmann\")) I would like to have the sum of the budget if the type is AMR and the leadership is Truttmann. I have multiple types and leaderships that I would be using this for. Thanks!","snippet":"Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, \"AMR\", {leadership}, \"Truttmann\")) I would like to have the sum of…","categoryID":322,"dateInserted":"2023-06-27T18:45:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T20:20:13+00:00","insertUserID":162642,"insertUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:31:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162642,"lastUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:31:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":29,"score":null,"hot":3375791135,"url":"https:\/\/community.smartsheet.com\/discussion\/106971\/using-sumifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106971\/using-sumifs-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106971,"commentID":382695,"name":"Re: Using SUMIFS Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382695#Comment_382695","dateInserted":"2023-06-27T20:20:13+00:00","insertUserID":162642,"insertUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:31:55+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-27T20:19:39+00:00","dateAnswered":"2023-06-27T19:59:16+00:00","acceptedAnswers":[{"commentID":382688,"body":"

Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p>


<\/p>

When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/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":106940,"type":"question","name":"IF cell CONTAINS one\/two\/three different values, return corresponding values","excerpt":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\", \"Termination\", \"New\" Currently the formula works for single values with the following formula =IF([Type of Project]@row = \"Consolidation\", \"🝢\",…","snippet":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\",…","categoryID":322,"dateInserted":"2023-06-27T09:24:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:11:45+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-27T20:08:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":57,"score":null,"hot":3375727587,"url":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106940,"commentID":382505,"name":"Re: IF cell CONTAINS one\/two\/three different values, return corresponding values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382505#Comment_382505","dateInserted":"2023-06-27T12:11:45+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+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\/Q6RJIERQGF1Y\/screenshot-2023-06-27-at-10-22-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-27 at 10.22.29.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T10:17:00+00:00","dateAnswered":"2023-06-27T10:12:21+00:00","acceptedAnswers":[{"commentID":382490,"body":"

Hi @Sam Swain<\/a>,<\/p>

You should be able to use this formula to accomplish this:<\/p>

=IF(HAS([Type of Project]@row, \"Consolidation\"), \"🝢\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Reduction\"), \"︾\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"New\"), \"○\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Termination\"), \"⨷\", \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>

Example output:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - if there are any issues etc. then just post! ☺️<\/span><\/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":[]}">

Trending in Formulas and Functions

  • 使用Countif to count Often and Very Often,

    however I can't get it to work the Often is also counting the Very Often: =COUNTIF([Tools & Resources (SE-2)]8:[Tools & Resources (SE-2)]12, HAS("Often", @cell), NOT(HAS("Very", @cell)))

  • Using SUMIFS Formula

    Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, "AMR", {leadership}, "Truttmann")) I would like to have the sum of the budget if the type is AMR and the leadership is Truttmann. I have multiple types and leaderships that I would be using this for. Thanks!

  • IF cell CONTAINS one/two/three different values, return corresponding values

    Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria "Consolidation", "Reduction", "Termination", "New" Currently the formula works for single values with the following formula =IF([Type of Project]@row = "Consolidation", "",…