Need help for match making formula!

[vaskas]

Good evening everyone, hope you are well.

I am trying to figure out a solution for the problem below:

1) assume we have 3 players in our team and 3 opponents in an upcoming tournament lets say.
2) each on of our players can have 1 opponent in a tournament. (no duplication of players or opponents)
3) assume that we know all the scores for all possible combination of players and opponents, i.e.
3.1) if player 1 plays with opponent 1 the score will be 15 for player 1
3.2) if player 1 plays with opponent 2 the score will be 16 for player 1
3.3) if player 1 plays with opponent 3 the score will be 17 for player 1
3.4) if player 2 plays with opponent 1 the score will be 10 for player 2
3.5) if player 2 plays with opponent 2 the score will be 11 for player 2
3.6) if player 2 plays with opponent 3 the score will be 12 for player 2
3.7) if player 3 plays with opponent 1 the score will be 18 for player 3
3.8) if player 3 plays with opponent 2 the score will be 19 for player 3
3.9) if player 3 plays with opponent 3 the score will be 25 for player 3

Question is, is there any way to automatically calculate (or populate the various scenarios) which opponent to assign to our players in order to have the maximum score for the team?

To be more clear, lets have a look at two different tournament scenarios:

Scenario 1:
player 1 plays with opponent 1 the score will be 15 for player 1
player 2 plays with opponent 2 the score will be 11 for player 2
player 3 plays with opponent 3 the score will be 25 for player 3
total team score = 15 + 11 + 20 = 51

Scenario 2:
player 1 plays with opponent 2 the score will be 16 for player 1
player 2 plays with opponent 3 the score will be 12 for player 2
player 3 plays with opponent 1 the score will be 18 for player 3
total team score = 16 + 12 + 18 = 46

As you can see scenario 1 is preferable cause it has more expected score than scenario 2. I need this to be automated in some way.

I also have the data in a table, columns have our player names, rows have opponent names, and respective cells have the score expected for our player.

I would really appreciate your help!

Thank you very much in advance for reading this.

 

[Eric W]

Welcome to the MrExcel forum!

Consider:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Table	Enemy 1	Enemy 2	Enemy 3	Enemy 4			Us	Them
2	Player 1	15	16	17	18		1	Player 1	Enemy 1
3	Player 2	10	11	12	13		2	Player 2	Enemy 2
4	Player 3	18	19	25	28		3	Player 3	Enemy 3
5	Player 4	4	5	6	8
6	Player 5	8	4	7	7			Scenario 1	Scenario 2	Scenario 3	Scenario 4	Scenario 5	Scenario 6
7								1-1,2-2,3-3	1-1,2-3,3-2	1-2,2-1,3-3	1-2,2-3,3-1	1-3,2-2,3-1	1-3,2-1,3-2
8								51	46	51	46	46	46
Sheet2

Cell Formulas
Range	Formula
H8:M8	H8	=INDEX($B$2:$E$6,MATCH(INDEX($H$2:$H$4,MID(H7,1,1),0),$A$2:$A$6,0),MATCH(INDEX($I$2:$I$4,MID(H7,3,1),0),$B$1:$E$1,0))+INDEX($B$2:$E$6,MATCH(INDEX($H$2:$H$4,MID(H7,5,1),0),$A$2:$A$6,0),MATCH(INDEX($I$2:$I$4,MID(H7,7,1),0),$B$1:$E$1,0))+INDEX($B$2:$E$6,MATCH(INDEX($H$2:$H$4,MID(H7,9,1),0),$A$2:$A$6,0),MATCH(INDEX($I$2:$I$4,MID(H7,11,1),0),$B$1:$E$1,0))

Your table is in A1:E6. You put the players that are going to compete in H2:H4, and the opponents in I2:I4. Then there are 6 scenarios to consider (3! = 3*2*1), which are enumerated in H7:M7. The lookup formulas in H8:M8 will tell you the expected results.

Note that this really will only work if you have just 3 players against 3 players. If you go up to 4 playing 4, the number of scenarios jumps to 24, 5 to 120, etc. In that case, you'd want to set it up as a Solver model, or use a VBA macro to create the scenarios.

 

[vaskas]

Welcome to the MrExcel forum!

Consider:

Your table is in A1:E6. You put the players that are going to compete in H2:H4, and the opponents in I2:I4. Then there are 6 scenarios to consider (3! = 3*2*1), which are enumerated in H7:M7. The lookup formulas in H8:M8 will tell you the expected results.

Note that this really will only work if you have just 3 players against 3 players. If you go up to 4 playing 4, the number of scenarios jumps to 24, 5 to 120, etc. In that case, you'd want to set it up as a Solver model, or use a VBA macro to create the scenarios.

Thank you for the prompt reply Mr. Eric!
Actually the matrix I have and i want to look for the possible scenarios is having 32 players against 32 enemies.
But i have no idea how solver of VBA macros
Any guidance for this approach?
Thank you very much!

 

[Eric W]

Just as an FYI, it's usually best to explain what your final requirements are. If you simplify it too much, any suggestions may not work for you, as is the case here.

32 by 32 is a much tougher nut to crack. 32! is about 2.6 X 10^35. That's a 2 followed by 35 zeros! This pretty much puts the problem out of the range of both the Solver, and a typical brute force VBA macro. If a macro could examine a million cases per second, it would still take many times the lifetime of the universe to finish, so that's pretty much out.

There are algorithms that can solve problems like this. They usually involve working with subsets of your cases, then combining them. For example, take 8 sets of 4, figure out the best result for each set. A 4X4 set makes 24 cases, times 8 = 192 total cases. Then, based on the results of that, you rearrange the subsets and try again. You'd repeat until the algorithm tells you it's not going to get any better. Unfortunately, I don't know how to do such an algorithm, nor do I have time to figure one out. You can Google "Knapsack problem" or go to Wikipedia, and check out that algorithm or related ones.

The only other option I can think of is a Monte Carlo type of simulation. This would randomly assign the pairings, figure out the total score, and repeat a thousand times. Keep the best result. Depending on how fast your computer is, you can probably run it more than that. This would not check every case, and statistically it probably would not find the "best" case, but it would probably come close. Let me know if that's of interest.