RYGB symbol average Formula

joe.quinones

Hello Everyone,

I have a sheet with 13 different people filling it out with RYGB symbols. So I am having issues with creating a formula that will take an average of 13 people. Here is what I got so far and it's coming out unparseable.

=IF(COUNTIF(Aaron@row, Chuck@row, Pablo@row, Lee@row, Oralia@row, Brady@row, Lowrey@row, Max@row, Joe@row, [Anthony(1)]@row, Marlon@row, Tim@row, Stephen@row, "Blue") = 13, "Blue", IF(COUNTIF(Aaron@row, Chuck@row, Pablo@row, Lee@row, Oralia@row, Brady@row, Lowrey@row, Max@row, Joe@row, [Anthony(1)]@row, Marlon@row, Tim@row, Stephen@row, "Green") > 8, "Green", IF(COUNTIF(Aaron@row, Chuck@row, Pablo@row, Lee@row, Oralia@row, Brady@row, Lowrey@row, Max@row, Joe@row, [Anthony(1)]@row, Marlon@row, Tim@row, Stephen@row, "Yellow") > 5, "Yellow", IF(COUNTIF(Aaron@row, Chuck@row, Pablo@row, Lee@row, Oralia@row, Brady@row, Lowrey@row, Max@row, Joe@row, [Anthony(1)]@row, Marlon@row, Tim@row, Stephen@row, "Red") > 0, "Red", ))))))))

Please Help lol

Mark Cronk

Hi @joe.quinones ,

I didn't test this, but something along the lines of the formula below should get you what you're after. It converts the colors into numbers and determines the average then assigns BGYR:

=IF(((COUNTIF(Aaron@row: Stephen@row, "Blue") *4)+ (COUNTIF(Aaron@row:Stephen@row, "Green") *3)+(COUNTIF(Aaron@row:Stephen@row, "Yellow") *2)+(COUNTIF(Aaron@row:Stephen@row, "Green")))/Count(Aaron@row:Stephen@row))>3.5, "Blue", IF(((COUNTIF(Aaron@row: Stephen@row, "Blue") *4)+ (COUNTIF(Aaron@row:Stephen@row, "Green") *3)+(COUNTIF(Aaron@row:Stephen@row, "Yellow") *2)+(COUNTIF(Aaron@row:Stephen@row, "Green")))/Count(Aaron@row:Stephen@row))>2.5, "Green", IF(((COUNTIF(Aaron@row: Stephen@row, "Blue") *4)+ (COUNTIF(Aaron@row:Stephen@row, "Green") *3)+(COUNTIF(Aaron@row:Stephen@row, "Yellow") *2)+(COUNTIF(Aaron@row:Stephen@row, "Green")))/Count(Aaron@row:Stephen@row))>1.5, "Yellow", "Red")))

Help at all?

Mark

joe.quinones

Hey @Mark Cronk,

Thanks, for the formula but it didn't work. Also in the end I will have two formulas one for current skills and one for goals. Each of the 13 people has 2 columns so one formula is going to take the AVG for current status and one for their goals. That is why I had each individual cell selected.