Fantasy soccer solver ($ up for grab) :)

[LeProDuJde]

Hello,

Me and my mate have been spending all day long trying to figure this out but as of right now we on the verge to give up due to fried brain injury (note that we both are fairly xcel noob, so wee didn t even get close to get this right...)...

Basically what we are trying to do is making an excel solver that would optimize the combination of players in a roster given their "projected points" and few contraints.

Example for a 5 players roster:

TEAM A: // Cost //Prjctd pts //Position
_ Buffon // 50M // 10 // GK
_Che guevara // 100M // 18 // DEF
_Maradonna //125M // 22 // MID
_Chuck Norris 200M 50 // FWD

Team B:
_Neuur 40M // 12 // GK
_Walter White 90M // 17 // DEF
_Napoleon Bonaparte 130M // 26 // MID
_Daenerys Targaryen 250M // 60 // FWD

Contraint:
3 players max from same team
Budget of 400M
positional contraint: GK min1-max1 , DEF min1-max2, MID min1-max2, FWD min1-max2

Ideally i m looking to have the excel give me directly the top X best combinations within those contraints. The site where i m playing has 5 players roster contest and 11 player roster contest , with the same contraints.
For 11 players roster the number of team to make combination from could be going up to 5/6, the budget and the positional contraint would also be diferent from the 5 player contest.

I m willing to give 50bucks for making the 5 player roster solver and 50 more if you manage to do the 11 players too.
Add me on skype if interested and have what he takes to do that: frenchfishpoker (menton)

If you don t have time, any comments or hint are more than apreciated.

Thnk you verry much and sorry for the broken english

[Bernie Deitrick]

You want to minimize the cost per point, to maximize the points available for a set total salary for your team.

So, the best strategy would be to put all the players into one table and calculate their cost per estimated points (Cost in millions divided by points) - sort of an "priciness" rating. Then sort the table so that the players with the lowest "priciness" are at the top, and go down the table filling out your team by position, choosing the player(s) for each position with the lowest priciness, given the other contraints (max players from one team, number of players per position).

Then, after you have the cost of that team, figure out how much salary you have left (or how much you have gone over) and then trade out players to increase your estimated points (or, decrease your total cost), but always do it in order of the lowest priciness player available, which will increase your total salary the least and maximize your points for that total salary.

Further, you would want to add the optional players (where you can choose the position) based on that index as well.

You could automate this, but it would take a full data set to figure out the algorithm.

[zbor]

I m willing to give 50bucks for making the 5 player roster solver and 50 more if you manage to do the 11 players too.

This is a free forum and you don't need to offer money.
However, if you want dedicated expert you can use our Commercial Services subforum http://www.excelforum.com/commercial-services/

[LeProDuJde]

Thanks for the current replies. I will also post on the commercial forum, sorry if i broke rules mod.

[AliGW]

Thanks for the current replies. I will also post on the commercial forum, sorry if i broke rules mod.

It's either/or: you can wait for free help here or offer money for a dedicated helper in the Commercial Services section, but if you do that, this thread will be closed. Your call!

[Alf]

Your present setup will not generate solver solution. As your min requirements is 1 player in each position the lowest cost for this is 455M
but your budget is only 400M so either increase your budget or do without a player in one of 4 possible positions.

I've set up a solver model for min number of players, try changing cell F18 to 1000 and do a rerun of solver.

Alf

[LeProDuJde]

Your present setup will not generate solver solution. As your min requirements is 1 player in each position the lowest cost for this is 455M
but your budget is only 400M so either increase your budget or do without a player in one of 4 possible positions.

I've set up a solver model for min number of players, try changing cell F18 to 1000 and do a rerun of solver.

Alf

Yes i pu t random numbers soo that why they didn t add up corectly. Anyway i m verry gratefull for your help and i ve just downloaded your solver and we look into it this afternoon. THANKS Alf!!!

[hathi25]

If you'd like to get a bit more adventurous, the Solver is completely configurable in VBA (References ... Solver). You can use VBA to create the X different lineups by looping through iterative executions of the solver where with each time you add in a constraint so that $C$27 (in Alf's workbook) is <= the value of the previous run. I actually do this to create my lineups for an 8-man roster; happy to offer more free advice if you want.

Steve