Using Solver Add-in for a Linear Program: For Fun Fantasy Football!

[tpresnell]

I thought it would be fun to make a linear program for fantasy football.




Background information: You pick 9 players; 2 quarterbacks, 2 running backs, 3 wide receivers, 1 tight end, and 1 flex (running back, wide receiver, or tight end).


Each player has an associated cost.


You're capped at 50,000 dollars.




I'm starting the LP fairly simple; using the previous point average as the "profit" for the upcoming week (I will add different variables to how I project "profit" later).


I extensively used Lindo and other Linear Programming software, but as I am no longer in school I figured I should advance my skills in Excel.




**What I Have**


5 rows;


+ Player Name
+ Selection Row (1 or 0)
+ Position
+ Projected Points (profit)
+ Cost


To set the objective constraint I made a box that is =sumproduct(selection row, projected points).


The cost constraint is similar; =sumproduct(selection row, cost)


I do not know how to construct the position constraints.

I.E., how do I construct the constraints such that I only have 0-2 quarterbacks, 0-3 running backs, 0-4 wide receivers, 0-2 tight ends, and a maximum of 9 total players.


If anyone has an idea of how I would do this I would appreciate it.

 

[mikecro]

Late answer - but there is always next season.

I had never used solver before but this article is helpful: Fantasy football

It is based on 11-man football but the principles are exactly the same and the screenshots are helpful.