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 catchall 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 catchall 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
Check out the Formula Handbook template!