I have the results of 50 football games. The teams are formed from a group of 20 players, who are selected randomly each time. How do I find the combination of 2 and 3 players that are more likely to win a game? I would appreciate tips on how to organise the data in a spreadsheet to run the analysis.
Player A, Team X, Game 1, Win
Player B, Team X, Game 1, Win
Player C, Team X, Game 1, Win
Player D, Team Z, Game 1, Lose
Player E, Team Z, Game 1, Lose
Player F, Team Z, Game 1, Lose
Player A, Team P, Game 2, Lose
Player B, Team Q, Game 2, Win
Player C, Team P, Game 2, Lose
Player D, Team Q, Game 2, Win
Player E, Team P, Game 2, Lose
Player F, Team Q, Game 2, Win
Player A, Team R, Game 3, Win
Player B, Team R, Game 3, Win
Player C, Team S, Game 3, Lose
Player D, Team S, Game 3, Lose
Player E, Team R, Game 3, Win
Player F, Team S, Game 3, Lose
My current approach: count manually how many times a combination of players wins. In this case, Player A and Player B is the most successful pair of players.
Here are some ideas. You may have to try them out, and play with them to get results that feel right to you.
There are $(20 \times 19)/2 = 190$ pairs of players.
For each game that a team wins, add $1$ to the "score" of each pair of players on the team. (How many pairs will depend on the size of a team.)
Now rank the $190$ pairs by score.
You can do the same thing for triples. There will be $(20 \times 19 \times 18)/6$ of those triples.
You could consider subtracting $1$ from pair or triple scores when they are on a losing team. I haven't thought through whether that would change the rankings.
$50$ games may not be enough to generate good statistics since there are so many pairs (and triples). Your random assignment might mean some pairs never happen to play on the same team.
You will have to figure out yourself how to do this in Excel.