I'm trying to create a matrice with data from a small database to analyze sports performance. The rows are the 21 shifts during the game, and I have 5 columns that show the players that were on for each shift. Then I have an additional column with a number that rates the performance during each shift.
Now I want to create a matrice that shows the average of the performance score for each combination of players. So I need to find a way to search for each shift where 2 specific players were on, and pull the average score for each time the 2 players played a shift together. This way I'll be able to know what pairs of players work well together. There are 14 players on the team.
I've been trying to figure out how to fill out the matrice using various functions such as DAVERAGE and SEARCH, but to be honest I'm a little lost here.
My data is available here in case someone is willing to look at it and point me in the right direction. The players are designated by their numbers (some are very creative) so I formatted them as text. I could replace them by the actual players' names if that helps.
You can also find this thread here: http://www.excelforum.com/excel-general/780786-creating-a-matrice-from-a-database.html
Now I want to create a matrice that shows the average of the performance score for each combination of players. So I need to find a way to search for each shift where 2 specific players were on, and pull the average score for each time the 2 players played a shift together. This way I'll be able to know what pairs of players work well together. There are 14 players on the team.
I've been trying to figure out how to fill out the matrice using various functions such as DAVERAGE and SEARCH, but to be honest I'm a little lost here.
My data is available here in case someone is willing to look at it and point me in the right direction. The players are designated by their numbers (some are very creative) so I formatted them as text. I could replace them by the actual players' names if that helps.
You can also find this thread here: http://www.excelforum.com/excel-general/780786-creating-a-matrice-from-a-database.html
Last edited:
