RYGB Symbols Average Formula
I currently having issues with creating formula that gets the average of 4 cells that have the option of 4 different symbols (Red,Yellow,Green,Blue). The goal is to show the average of the four cell symbol's in one here is what I have so far.
=IF(COUNTIF([PW Goal]3, [BB Goal]3, [JQ Goal]3, [MC Goal]3, "Red")) = 4, "Red", IF([PW Goal]3, [BB Goal]3, [JQ Goal]3, [MC Goal]3, "Yellow") > 0, "Yellow", IF([PW Goal]3, [BB Goal]3, [JQ Goal]3, [MC Goal]3, "Green") > 3, "Green", IF([PW Goal]3, [BB Goal]3, [JQ Goal]3, [MC Goal]3, "Blue") = 4, "Blue"
Best Answer
-
Ramzi K ✭✭✭✭✭When you say "average" that implies numerical averages. Assuming your rules are as follows:
. If all 4 are Red then Goal AVG = Red
. If any of the 4 are Yellow then Goal AVG = Yellow
. If all 4 are Green then Goal AVG = Green
. If all 4 are Blue then Goal AVG = Blue
If this is the case
- Put the Goal Columns next to eachother so you can reference them as a range. Otherwise your formulas will be too complicated. Then try this formula:
=IF(COUNTIF([PW Goal]1:[MC Goal]1, "Red") = 4, "Red", IF(COUNTIF([PW Goal]1:[MC Goal]1, "Yellow") > 0, "Yellow", IF(COUNTIF([PW Goal]1:[MC Goal]1, "Green") > 3, "Green", IF(COUNTIF([PW Goal]1:[MC Goal]1, "Blue") = 4, "Blue", "No AVG"))))
Note:It's best to have a general catch-all exit clause for your nested if. In this case there are some cases that don't have a condition - for example 2 blue, 2 green. Make sure you account for those.
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me:[email protected]
If this post helped you out, please help the Community bymarking it as the accepted answer/helpful.
Answers
-
Ramzi K ✭✭✭✭✭
When you say "average" that implies numerical averages. Assuming your rules are as follows:
. If all 4 are Red then Goal AVG = Red
. If any of the 4 are Yellow then Goal AVG = Yellow
. If all 4 are Green then Goal AVG = Green
. If all 4 are Blue then Goal AVG = Blue
If this is the case
- Put the Goal Columns next to eachother so you can reference them as a range. Otherwise your formulas will be too complicated. Then try this formula:
=IF(COUNTIF([PW Goal]1:[MC Goal]1, "Red") = 4, "Red", IF(COUNTIF([PW Goal]1:[MC Goal]1, "Yellow") > 0, "Yellow", IF(COUNTIF([PW Goal]1:[MC Goal]1, "Green") > 3, "Green", IF(COUNTIF([PW Goal]1:[MC Goal]1, "Blue") = 4, "Blue", "No AVG"))))
Note:It's best to have a general catch-all exit clause for your nested if. In this case there are some cases that don't have a condition - for example 2 blue, 2 green. Make sure you account for those.
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me:[email protected]
If this post helped you out, please help the Community bymarking it as the accepted answer/helpful.
-
L_123 ✭✭✭✭✭✭You can do it the standard way, or you can do it the fun way.
=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([PW Goal]@row+[BB Goal]@row + [JQ Goal]@row +[MC Goal]@row), "Green", 1, "Yellow", 11), "Red", 111), "Blue", 1111)) / 20
*Edit - Haha, the output is supposed to be the color balls, just realized that. oh well
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/68659/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
