Need help for match making formula!

vaskas

New Member
Joined
Dec 7, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,759
Welcome to the MrExcel forum!

Consider:

Book3
ABCDEFGHIJKLM
1TableEnemy 1Enemy 2Enemy 3Enemy 4UsThem
2Player 1151617181Player 1Enemy 1
3Player 2101112132Player 2Enemy 2
4Player 3181925283Player 3Enemy 3
5Player 44568
6Player 58477Scenario 1Scenario 2Scenario 3Scenario 4Scenario 5Scenario 6
71-1,2-2,3-31-1,2-3,3-21-2,2-1,3-31-2,2-3,3-11-3,2-2,3-11-3,2-1,3-2
8514651464646
Sheet2
Cell Formulas
RangeFormula
H8:M8H8=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

New Member
Joined
Dec 7, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,759
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,735
Messages
5,626,583
Members
416,192
Latest member
steinach

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top