Hi, hoping someone can help with a formula. I have two tables, one on the left with players, scores, team and account columns. this table has a thousand+ rows. On the right is a cut of a pivot showing average score for people in those teams/account types. I want to find the players with the 5% most anomalous scores within each team/account bracket. So a formula for the "Within 95% range" in the first sheet to look up that player's individual score, cross reference it with the relevant team/account average from the "Averages" table, then to calculate if it among the 95% of players with a score closest to that average for that team/account, or if it is in the 5% most anomalous. Example - Tom has a score of 160 and is in the red team with account A. The formula needs to determine the range of scores within Red/A and to see if Tom's score of 160 is in the 95% of scores closest to the red/A average of 100 (from Averages table). Make sense to anyone?
<colgroup><col span="4"><col><col span="6"></colgroup><tbody>
</tbody>
Analyst | Score | Team | Account | Within 95% range | Average | A | B | C | D | |
Tom | 160 | Red | A | Red | 100 | 145 | 170 | 200 | ||
Sarah | 120 | Blue | B | Blue | 125 | 130 | 156 | 180 | ||
Sonny | 132 | Red | B | Pink | 130 | 1250 | 167 | 198 | ||
George | 156 | Red | A | Yellow | 85 | 130 | 150 | 159 | ||
Jodie | 98 | Pink | C | |||||||
Bruce | 149 | Yellow | C | |||||||
Leroy | 200 | Yellow | D | |||||||
Julia | 120 | Pink | A | |||||||
Stan | 98 | Blue | B | |||||||
Jon | 132 | Blue | C | |||||||
Keith | 189 | Yellow | D |
<colgroup><col span="4"><col><col span="6"></colgroup><tbody>
</tbody>