Solver Constraints

Brief background, I'm helping a friend who runs the 1st-5th basketball league for a large public school. They usually have around 10 teams. For this upcoming season they want the teams to be more balanced and for the players to play with different players from last year. Using solver, I'm trying to create balanced teams based on simple ratings for the players. I'm also trying to to run a constraint so that solver selects players from a minimum of 7 different teams from the previous season. I'm having trouble with the constraint as I continue to get the Non-Linear message for all formulas I try. Any ideas?

Not knowing the layout of your data still something like this could be of help?

Alf

Hi Alf,

Thank you for the solver example. This is similar to what I'm trying to do, but in your example I'm also trying to add a constraint that forces the Solver to select players from a minimum number of teams. So in your solver I would want a constraint that forces the solver to pick from at least 7 or 8 NFL teams. I know how to put a constraint on the number of players from each team such as a maximum of 2 players from each team, but I need to take it a step further.

Also, I noticed you had the players sorted by lowest projected points to highest. Does that result in higher solver projections?

Does that result in higher solver projections?

No but if you look at the "defense" players their points are in the lower range going from 5.7 to 7.4 and sorting on points lower to higher give most of the solver results in rows 201 to 263 so if you have a decent screen size you will see the result for 9 out of the 10 players solver picks. Exception being "defense" player found on row 50.

To pick a number of players from different teams you could specify team as well as position (one column for every team) and add a constraint to specify what that number should be.

Alf

Hi Alf,

Thank you for the response again. I see your point regarding the team/position constraint, but I don't want to tell solver which teams I want only 1 or 2 players from. I'd like solver to select players from at least 7-8 teams while maximizing points, without me telling it what teams to choose 1-2 players from. Is that possible?

What is your objective? As I read you last post it seems to me you wish solver to chose players from 7 - 8 teams and maximize the total points for that selection.

I think you better upload a worksheet with players, teams, points and specify how you wish the solution should be so I can take a look at it and see if I can set up a solver model.

Alf

Capture.JPG

Hi Alf,

I'm unable to attach the Excel file. The attachment drop down does not work for some reason. I've attached a screen shot of my example instead.
I have a list of 100 players that were split among 10 teams last season.
I'm now creating balanced teams of 8 players and I'm trying tell solver to select players from at least 6 teams. I've entered
an If formula in column P to tell excel to put a 1 in that column if any players from that team are selected. I've summed
that column in P18 and have created a constraint that says that cell must be >= to 6. I get the WARNING: Constraints do not
appear to be linear. Thoughts on how to get this done?

The attachment drop down does not work for some reason.

Yes I know, this has not worked for years and umpteen members have asked the IT team to fix it or at least remove the drop-down. Still there is another option to upload a file. If you click on the "Go Advanced" button you will see near the bottom (left) on the page a line with blue text saying "Manage Attachment".

Click on this line and you will be taken to a new page where you can upload files and images.

I'll have a go during the weekend to setup a model for solver problem.

If you get a warning about constrain do not appear to be linear I need to see your file with formulas in order to see why. On the other hand you could try to use "GRG Nonlinear" engine for solver.

Alf

OK, I think it's attached. When you run the solver you should get the Linearity warning.
The GRG Non-Linear solver times out on my PC so I need to stick to the Simplex version.
I also use Open Solver on occasion but I get the same warning. Any help you can provide
is greatly appreciated.

Looking at your formulas you have an If function in the range P7:P16 and this causes your problem as the If function is a discontinuous function.

The Linear solver engine solves linear problems, the GRG nonlinear engine can solve smooth nonlinear problems and the Evolutionary engine can handle non-smooth problems and discontinuous functions.

But using the Evolutionary engine comes with a price i.e. in solution time and quality of the solution.

According to the makers of solver

By far the most common discontinuous function in Excel is the IF function where the conditional test depends on the decision variables, as in the example =IF(C1>10,D1,2*D1) where C1 is a variable. Here is a short list of common discontinuous Excel functions:

IF, CHOOSE

LOOKUP, HLOOKUP, VLOOKUP

COUNT

INT, ROUND

CEILING, FLOOR

and some non-smooth excel functions

Please Login or Register  to view this content.

So selecting the Evolutionary engine for solver and changing settings a bit in "Options" for the Evolutionary tab solver do find a solution to the problem.
aevo.jpg
I've tested using different "Maximum Time without improvement" i.e. 10, 20 and 30 but selected 30 as this gave the highest number of teams (7 as opposed to 6).

Still I do think that a rebuild of your model to get a linear relation would be the best choice so as I said I'll have a go at it during the weekend.

Could you please explain the formula in the E column as this has me puzzled.

Alf

After fiddling around a bit with your uploaded file I've not made a model I like. Still you can run it as a linear model if you delete the constraint

Please Login or Register  to view this content.

Solver will then find a solution taking players from 8 teams and this is also something I don't understand properly. Looking at your uploaded file the solver solution uses players 41 to 44 and players 52 to 55 and this is counted as 5 teams.

Looking at your data in column C you got teams A to J and this pattern repeats down to row 101. So I would think team1 are players in row 2 to 11, team2 are players in row 12 to row to row 20 and so forth.

With that in mind I would assume that using players 41 to 44 and 52 to 55 would only be counted as players from two teams not 5. So there are definitely things I don't understand in the setup of your model.

And of course as I said in post #10 your original settings work fine if you select the Evolutionary engine for solver.

Alf

Hi Alf,

Sorry I'm just responding, busy weekend here. Thank you for showing me the evolutionary model. I tried that and it does work in maximizing the number of teams the solver selects from, but it seems to not maximize the objective cell correctly. Plus when I use the evolutionary model in much larger solver files, it just runs and runs and eventually freezes my PC. To answer your question about column E, I use that to add up a running tally of players selected in column D, then do a VLOOKUP in column J so I can see what players were selected.

I've also asked this same question in other forums and it seems this constraint can't be done within a linear model / using Simplex or another like linear solver in Open Solver. I really appreciate your help with this but I don't want to take up any more of your time. I'll definitely use the Evolutionary method when possible. Thank you again.

Best of luck in solving your problem, If you manage to build or get help to setup a model that can solve this problem I would like if you could post your solution here as well.

Alf