Exciting optimization question

Hey guys/gals, came across this forum while I was searching for some help for an optimization model I am building. I read through some similar threads but couldn't really find the answer I am looking for. So hopefully ya'll can help.

So this is what I am trying to do.

I am building a model for the Cricket Fantasy League. Yes, its Cricket (the game that most people find super boring, lol).

So here is how the league is structured.

1)each day there are is one or two games
2)there are 14 teams in the tournament and at this stage of the tournament there are about 2 games per team left (Yes, this is the middle of the tournament)
3)we can pick 11 players per game and we get points based on how well they do in the actual game
4)each time we make a change to our lineup, it counts as a substitution
5)we only have a limited number of substitutions.

[B]My idea: My idea is to build a model that can be optimized to give me a "roster". The roster will look at all upcoming games and within the confines of the total number of substitutions I have, will suggest me a roster of "country slots" that I can then fill with the best performers from that country. /B]

Right now I have the model developed (see attachment) but my optimizations are not working ( I assume I have to use Evolutionary method, but no matter what I do it does not give me a solution. Even if I start with an obviously non optimum solution of just having all 11 slots filled from one country and not making any changes after that (which is dumb, because that way I only get to score points from only 2 games out of a possible 15, the model does not use any of the remaining substitutions to do any changes.

What am I doing wrong here? Can anyone take a look and take a stab at this?

Here is the model Cricket model for upload.xlsx

Thanks loads

Looking at this again, I should have added C10:p20 <14 as a constraint. I think this would help excel only look at integers that actually represent the countries.

I've added that and re-run the model, still nothing. Why dosn't it even change a single digit. Just making one of those 1 s to a 2 can instantly improve the score. Why doesn't solver see this?

Looking at your model I not sure I understand your setup.

First a minor point, looking at your solver setting under the "options" tab the box for "Ignore Integer Constraint" is ticked. As you are setting the range H10:H14 as "Integer" this box should be unchecked. I've tested with this unchecked but this does not make any change to solver so as I said a minor point.

The result you got in range C10:P20 I've not been able to match. Normally the range with the variable cells is cleared before a solver run to let solver pick the optimal solution. Doing so I got a 1 in all cells C10 to H20. The range I10 to P20 are all 0 and number of total matches are 11.

The formulas with "If and Or" staring at C22 is probably the cause of the solver problem. Solver will not work with discontinues functions i.e. "If" functions. This goes for the linear (Simplex model) and most of the time for the GRG non-linear model. The Evolutionary may succeed in solving the non smooth problem but there is no guarantee it will do so. I also tested the Evolutionary model but after running it for several minutes not getting any better result than 11 I stopped it.

Solver solutions are sometimes depending on the starting point so for me starting at zero value in range C10 to P20 I've not managed to get the result you have in the uploaded file. So did you give solver another starting point or did you run solver for more than 15 minutes?

Alf

Hey Alf, thanks for looking it over.

First of all, after you pointed it out I took another look at the model.

What I found was that my excel settings on my home PC was set up as "manual formula updates", so that's why the model was not updating values. Once I made it to automatic things started moving.

But I am still not sure whether I am doing this correct.

After running Evolutionary for a few times (about 10mts each) I have arrived at a solution which is giving me a 39 score while utilizing all 25 substitutions.

Can you take a look? (I have updated the model with the latest games, substitution numbers and an easier to read Final Results table).

THANKS again

Here is the updated model
Cricket model for upload.xlsx

Had a look at you updated model and I do wonder about the range you set for variable cells i.e. D10 to N20. Should that not be C10 to N20?

Running your updated model (after clearing range D10:N20) took quite a while on my PC (AMD CPU not the best of number crunchers) > 20 minutes the result is quite different from what you got (see upload).

I’m still confused about the way your model is set up; not being a cricketer could probably be one reason why.

Alf

Attachment 381722Attachment 381722