Excel solver failure to get optimal solution when generating a schedule for 12 week schedule for 24 teams
I've been tasked with generating a schedule with 24 unique teams.
Rules:
- Over the 11 weeks each team plays 2 matchups
- In week 12 each team plays a single matchup
- The result is every team plays every other team exactly once
Problem setup
Constraints:
- a 24x24 matrix of cells are the input variables for the problem. Each value must be a week between 0 and 12. 0 is used to block out the cell to avoid scheduling a team against itself.
- Each row needs to add up to 144 (0+1+2+...12)
- On the far right 0 must be used in the correct cell, weeks 1-11 countif() needs to equal 2, and week 12 needs to equal 1 for every team/ream
The goal seeking objective is to target the sum of all the week numbers which should equal 144*24 = 3456
When I am running the solver (OpenSolver) It is returning errors with all the different linear and non-linear solvers. Any Ideas?
Solution
I don't know what is wrong with your model (difficult to tell without having a look at the spreadsheet or not being a mind reader).
This is a problem that really requires that we first develop a mathematical model before we start coding or typing. Here is my mathematical model for this problem:
I use the definition:
game(t1,t2,r) = 1 if team t1 plays against team t2 in round r (only for t1<t2 to prevent double counting)
= 0 otherwise
Not completely trivial, but this can be implemented with any MIP solver: all constraints are linear. When I tried this, my results look like:
---- 24 VARIABLE game.L schedule for teams
round1 round2 round3 round4 round5 round6 round7 round8
team1 .team15 1
team1 .team16 1
team1 .team17 1
team1 .team18 1
team1 .team19 1
team1 .team20 1
team1 .team22 1
team1 .team24 1
team2 .team12 1
team2 .team15 1
team2 .team16 1
team2 .team19 1
team2 .team20 1
team2 .team21 1
team2 .team23 1
team2 .team24 1
team3 .team12 1
team3 .team13 1
team3 .team14 1
team3 .team15 1
team3 .team16 1
team3 .team21 1
team3 .team23 1
team3 .team24 1
team4 .team10 1
team4 .team14 1
team4 .team15 1
team4 .team16 1
team4 .team18 1
team4 .team21 1
team4 .team22 1
team4 .team23 1
team5 .team11 1
team5 .team15 1
team5 .team16 1
team5 .team18 1
team5 .team19 1
team5 .team20 1
team5 .team21 1
team5 .team22 1
team6 .team9 1
team6 .team10 1
team6 .team11 1
team6 .team16 1
team6 .team17 1
team6 .team20 1
team6 .team22 1
team6 .team23 1
team7 .team9 1
team7 .team10 1
team7 .team12 1
team7 .team14 1
team7 .team16 1
team7 .team19 1
team7 .team21 1
team7 .team24 1
team8 .team9 1
team8 .team11 1
team8 .team12 1
team8 .team13 1
team8 .team14 1
team8 .team16 1
team8 .team19 1
team8 .team22 1
team9 .team18 1
team9 .team20 1
team9 .team21 1
team9 .team22 1
team9 .team23 1
team10.team17 1
team10.team20 1
team10.team21 1
team10.team23 1
team10.team24 1
team11.team17 1
team11.team18 1
team11.team19 1
team11.team23 1
team11.team24 1
team12.team17 1
team12.team18 1
team12.team22 1
team12.team24 1
team13.team17 1
team13.team18 1
team13.team19 1
team13.team20 1
team13.team23 1
team13.team24 1
team14.team17 1
team14.team18 1
team14.team20 1
team14.team21 1
team15.team17 1
team15.team19 1
team15.team22 1
+ round9 round10 round11 round12 round13 round14 round15 round16
team1 .team9 1
team1 .team10 1
team1 .team11 1
team1 .team12 1
team1 .team13 1
team1 .team14 1
team1 .team21 1
team1 .team23 1
team2 .team9 1
team2 .team10 1
team2 .team11 1
team2 .team13 1
team2 .team14 1
team2 .team17 1
team2 .team18 1
team2 .team22 1
team3 .team9 1
team3 .team10 1
team3 .team11 1
team3 .team17 1
team3 .team18 1
team3 .team19 1
team3 .team20 1
team3 .team22 1
team4 .team9 1
team4 .team11 1
team4 .team12 1
team4 .team13 1
team4 .team17 1
team4 .team19 1
team4 .team20 1
team4 .team24 1
team5 .team9 1
team5 .team10 1
team5 .team12 1
team5 .team13 1
team5 .team14 1
team5 .team17 1
team5 .team23 1
team5 .team24 1
team6 .team12 1
team6 .team13 1
team6 .team14 1
team6 .team15 1
team6 .team18 1
team6 .team19 1
team6 .team21 1
team6 .team24 1
team7 .team11 1
team7 .team13 1
team7 .team15 1
team7 .team17 1
team7 .team18 1
team7 .team20 1
team7 .team22 1
team7 .team23 1
team8 .team10 1
team8 .team15 1
team8 .team17 1
team8 .team18 1
team8 .team20 1
team8 .team21 1
team8 .team23 1
team8 .team24 1
team9 .team17 1
team9 .team19 1
team9 .team24 1
team10.team18 1
team10.team19 1
team10.team22 1
team11.team20 1
team11.team21 1
team11.team22 1
team12.team19 1
team12.team20 1
team12.team21 1
team12.team23 1
team13.team21 1
team13.team22 1
team14.team19 1
team14.team22 1
team14.team23 1
team14.team24 1
team15.team18 1
team15.team20 1
team15.team21 1
team15.team23 1
team15.team24 1
team16.team17 1
team16.team18 1
team16.team19 1
team16.team20 1
team16.team21 1
team16.team22 1
team16.team23 1
team16.team24 1
+ round17 round18 round19 round20 round21 round22 round23
team1 .team2 1
team1 .team3 1
team1 .team4 1
team1 .team5 1
team1 .team6 1
team1 .team7 1
team1 .team8 1
team2 .team3 1
team2 .team4 1
team2 .team5 1
team2 .team6 1
team2 .team7 1
team2 .team8 1
team3 .team4 1
team3 .team5 1
team3 .team6 1
team3 .team7 1
team3 .team8 1
team4 .team5 1
team4 .team6 1
team4 .team7 1
team4 .team8 1
team5 .team6 1
team5 .team7 1
team5 .team8 1
team6 .team7 1
team6 .team8 1
team7 .team8 1
team9 .team10 1
team9 .team11 1
team9 .team12 1
team9 .team13 1
team9 .team14 1
team9 .team15 1
team9 .team16 1
team10.team11 1
team10.team12 1
team10.team13 1
team10.team14 1
team10.team15 1
team10.team16 1
team11.team12 1
team11.team13 1
team11.team14 1
team11.team15 1
team11.team16 1
team12.team13 1
team12.team14 1
team12.team15 1
team12.team16 1
team13.team14 1
team13.team15 1
team13.team16 1
team14.team15 1
team14.team16 1
team15.team16 1
team17.team18 1
team17.team19 1
team17.team20 1
team17.team21 1
team17.team22 1
team17.team23 1
team17.team24 1
team18.team19 1
team18.team20 1
team18.team21 1
team18.team22 1
team18.team23 1
team18.team24 1
team19.team20 1
team19.team21 1
team19.team22 1
team19.team23 1
team19.team24 1
team20.team21 1
team20.team22 1
team20.team23 1
team20.team24 1
team21.team22 1
team21.team23 1
team21.team24 1
team22.team23 1
team22.team24 1
team23.team24 1
Of course, the solution is not unique. The model solved in 0.2 seconds.
- How to import values from excel with pandas into tkinter faster?
- Speed up concatenation of excel files with Pandas
- Trying to send screenshot of excel in an email
- Sum of text Does not contain in Range
- Stopping Excel Macro executution when pressing Esc won't work
- Consolidated Date window
- Excel formula for calculating difference between two date columns
- Get number of decimals in python float, including trailing zeros
- Excel VBA search for data and stuck at a certain point due to group or split source file?
- Convert Time Input in Decimal Format - Excel Formula or VBA
- Lookup unique "Values" if they follow a certain "Pattern" from a lookup table then return a "Name"
- Share office scripts connected to Excel with external organisations
- Tiling an Excel array in 2 dimensions, ideally not using VBA
- How to get the cartesian product of two sets via Excel Formulas
- Repeat Value for Every Instance of Another Value in Excel without using Power Query
- VSTACK doesn't pile up dynamic ranges
- How To Close a UserForm Properly in Excel VBA?
- In Excel, can I use a hyperlink to run vba macro?
- EXCEL: Count all unique values in Table column up to a certain point, and combine them into a single string?
- Excel VBA connection to SQL Server with Microsoft Entra MFA authentication
- Excel shows incorrect polynomial trendline function
- Split too large VBA into multiple Subs
- Trying to calculate user whos spent the most and the least - Excel
- How to do a union of two columns and then sort them into a third column in Excel?
- Is there a way to create a simple custom formula in Excel without VBA?
- Copying a String Array into a Range of cells (in MS Excel using VBA)
- vlookup with formula calculated from lookup cell location
- How to extract R InstallPath from Registry using Excel VBA
- How to inner-join in Excel (eg. using VLOOKUP)
- Scientific notation when importing from Excel in .Net