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
-
Hi @joe.quinones,
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: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Answers
-
Hi @joe.quinones,
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: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!