Excel Solver- Optimal Fantasy Football Lineup- "Solver could not find a feasible solution"

[kwg2200]

I am working with the Excel Solver to find the optimal fantasy football roster (maximum projected points). The constraints are:

- Salary Cap of $100,000
- Roster Constraints:
- 1 QB
- 2 to 4 WR
- 2 to 4 WR
- 1 to 3 TE
- 1 Defense
- Total roster size must be 10

I have set up the spreadsheet so that Column A will either be 1 or 0 - 1 being that that player is selected. I have built the constraints into the solver, but it is not working correctly for some reason. It says "Solver could not find a feasible solution." I have gone over the constraints and formulas numerous times, and I cannot figure out why it cannot find a solution. Any help would be greatly appreciated. Thanks!

[kwg2200]

Bump to hopefully get this solved by kickoff today! Thanks!

[Alf]

In you formula you are using "SUMIF" and "COUNTIF" but solve can't handle discontinuous functions like "IF", "COUNTIF" and "SUMIF" so ¨rebuild your model a bit and substitute "SUMIF" with "SUMPRODUCT" and "COUNTIF" with "SUM"

I also doubt that you present Solver model can handle that many rows (199). I would split it in two equal parts and run each separately. Then set up the two solutions as a third model and run solver again.

Alf

[kwg2200]

Thanks Alf! I got it to work by rebuilding the formulas without any "IF"

[martindwilson]

cross post http://www.mrexcel.com/forum/excel-q...-solution.html

[bballstar492]

What did you use instead of "COUNTIF"?

[Alf]

Perhaps a setup like this?

Alf

Ps works with excel 2003, 2007 and 2010 not tested with excel 2011.

[bballstar492]

Thanks ALf! I am using 2011. I will try this out and let you know if I encounter any other problems.

[Alf]

I think it should be ok as long as you don't try to run Solver using a vba script

There is a serious problem using Solver for Excel in Office 2011 when it is called from vba. Because Solver is a separate application, control must be transferred from Excel to Solver, and this control does not occur until after vba has terminated. This means that any processing that occurs in the vba program after the call to SolverSolve will be completed before Solver has done its task.

Alf

[gdrydenc]

I'm very interested in how this works. I downloaded the excel file that you provided, Alf, but I'm a Solver novice. Once the file is set up, what do I put into the solver fields?

Thanks for your help!

[Alf]

what do I put into the solver fields?

Nothing at all as this is a ready to go model. Download and open up the file. Go to "Tools" -> Find and click "Solver" and the "solver parameter" windows opens. Then click button "Solve" and after some seconds the "Solver Results" windows pops up, click "Ok" and that's it.

Due to the mathematics involved in shuffling numbers using binaries you also get some results like 8.88178E-16. These you can manually set to zero as

0.000000000000000088178 is rather close to zero.

For a rerun you must first clear the range A2:A201.

Alf

Ps If it's more comprehensive explanation you wish for do tell me and I could write and upload a word document explaining the how and the why of this solver setup. This will take a bit of time so I'm not sure when I could do that. In the meantime you could read the support manual that the makers of solver have provided.

http://www.solver.com/excel-solver-help

[gdrydenc]

Oh, look at that! Awesome, thank you so much for your help!