RYGB Symbols Average Formula

joe.quinones

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"

Ramzi K

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

1. 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 K

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

1. 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

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