Solver is an add-in Tool for Microsoft Excel that will look at a group of variables that you set and run hundreds of thousands of potential outcomes and give you the optimal result. That is perfect when trying to figure out what players to play in Daily Fantasy Sports.
Download Sample file:
There are six steps you need to do to get a spreadsheet ready for Solver.
- Supply the data
You need to give Solver the data it needs to make lineups. This includes the player name, salary and projection. You can get this from many places.
- You can have your own spreadsheet with projections you create
- You can go to the web and copy them from trusted websites like Numberfire or FantasyPros.
- You can copy the export from the Daily Fantasy website.
For this example, let’s just get the export from Fanduel. To do this, open up a tournament and click Download Players list and it will export them as a csv file
Then either save that file as an Excel file or copy and paste the data to an Excel file. You can not run Solver on a Csv file.
- Create an Extra Column to Sort the players to show the best players first
Solver only can look at 200 players, so you want to be sure the top 200 are being looked at. To do this, you need to create an extra column to divide Projection by Salary and then sort descending.
- Create a column of 0s and 1s for Solver to pick the lineup
You need to add a column and put 0s all the way from row 2 to row 200. Then when Solver runs, it will put a 1 next to the players it chooses for your lineup.
- Create a column for each Position
You need to add a column for each position that Solver is going to choose from. So for this example, we have a column for PG, SG, PF, SF and C.
Then in the first cell under PG, you need to put this formula:
What this formula is doing is performing an IF statement. It is asking: IF the position of the player in cell A 2 equals the position in cell J2 , then it performs the function 1 X I2. Otherwise put “”, which means a blank cell.
You need to be sure to have a dollar sign in front of A and the I so when you copy the formula to the right, that column is anchored down and it remains that column. You also need to put a dollar sign in front of the 1 in J1 so it always looks at row 1 which is the position title.
- Enter all the variables in the spreadsheet to finish off your parameters for the Lineup.
You need to add a cell to:
Keep track of the score:
**this is multiplying the values in column I which is a 1 or 0 by the values in column D which is the score.
Keep track of the Salary used:
**this is multiplying the values in column I which is a 1 or 0 by the values in column E which is the salary.
Keep track of players used:
**This is summing all the 1s and 0s which Solver chose
Keep track of how many players at each position Used:
**This is summing all the 1s in the column for the corresponding position. In this case, it is summing all the 1s in column J which is the PG category
- Enter the Constraints into Solver
Now that your Excel spreadsheet is set up, you just need to add the constraints or parameters into Solver. Go to Data and then to Solver. It will open up the Solver Wizard. You need to add the following:
You need to maximize cell Q1 which is the score. You do this by changing the values in I2 to I200. Then you need to add constraints to the various formulas. You need to make sure the total players equals 9, salary is less than or equal to 60000 and all the positions have 2 or 1 player. You also need to set !2 to !200 as binary so Solver will return a 0 or 1. Otherwise you will get fractions in there which you don’t want.
Once you did all six steps, you are ready to run Solver. So click Solve and then it will take a second or two and you should get a screen like below:
All parameters were set and a solution occurred. You should now see a 1 in column where Solver picked a player and you should see a score and salary populated also.
You just created an Optimal Lineup!