Building Lineups in Spreadsheets
Building lineups in a spreadsheet can be really easy if you set yourself up with a good foundation to build from. For starters, take a look at this lesson from Microsoft Office on how to set up your fantasy football team with the same general principles for daily fantasy basketball.
Step One: Define Your Variables
In this instance, you are searching for the optimal lineup based on some projection model. I personally set up my Excel file so I can paste in a daily update with the following organization of columns:
- Player, Team and Opp – Pretty clear, but these are the basic variables to include for organizational purposes.
- Position (POS) – With position it’s important to realize you want the player’s listed position on a given site and not necessarily their true position. For example, FanDuel lists “”(player-popup)Avery Bradley”:/players/avery-bradley-941(player-profile)”:/players/Avery_Bradley-941 as a PG despite him playing majority of minutes at the SG position. You want the position listed for a FD Optimal lineup creator to show PG not SG.
- Salary – Given salary for site you’re looking at.
- Projection – Projection for that day.
Beyond this you can set your file up so you can weight your projections to better fit what you want to find. So if a guy is a late scratch, you can mark that player to a weight of 0 quickly and then adjust his replacements up to quickly recalculate the optimal lineup.
Setting up the Solver Sheet
Open a new sheet. In this sheet you’ll want to set up the columns as shown below. NOTE: In this example, I am looking at working with FanDuel. Use the Excel lesson linked above and trial and error to work this same process out for other sites, but understand that the process is somewhat different for other sites.
- Column A: Play? – The Play column should be set to all 0’s to start. Type 0 into A2 and drag that all the way down to A201. It’s important to note that you can only have 200 max entries when using solver. Entries below that will create issues in the program.
- Column D: Projection – I’m skipping straight to this column because you’ll need to set your file up so you have the Top 200 projected players showing instead of a randomly sorted 200 players. In the first sheet of your file you should have projection in Column F. Let’s say, for example, we titled that sheet “PROJECTIONS”. In cell D2 of your Optimal Lineup sheet, you’ll want to use the following formula:
IFERROR (LARGE (PROJECTIONS!$F:$F,1),”“)
That formula will produce the largest projection number of all players into Cell D2 of the Optimals sheet. Drag that down to Cell D201. The annoying aspect of this is you’ll have to go through each cell manually and change the “1” in the formula above to 2,3,4, etc. all the way down to 200.
- Column B: Player Name – We’ll now use the Projection found in Column D to VLOOKUP the player name. First go back to your PROJECTIONS sheet and you’ll find out why we have 2 player columns in that sheet. You want to use a VLOOKUP function to search for the projection total and connect it to a player name. So in Cell B2 you’ll want to use the formula:
IFERROR (VLOOKUP ($D2,PROJECTIONS!$F:$G,2,FALSE),”“)
This formula will return a player name associated with the projection number.
- Column C: Salary – The next step is to find the salary associated with that player. For this one I like to lookup the player name in the PROJECTIONS sheet by using this formula in Cell C2:
IFERROR (VLOOKUP ($B2,PROJECTIONS!$A:$G,5,FALSE),”“)
- Columns E: Position – Use the same formula as above to search the player name in the PROJECTIONS sheet to return a position instead of a salary.
- Columns F, G, H, I and J: Positions Part 2 – This is where it gets a little tricky. You need to set it up so that you can create min and max limits for number of positions. For example, on FanDuel you have the following roster format:
2 Point Guards, 2 Shooting Guards, 2 Small Forwards, 2 Power Forwards and 2 Centers
The values returned in Columns F-J will tally the number of players at each position being used in your optimal lineup later on in the calculation process.
So for Cell F2, under “PG”, I would use this formula for FanDuel:
And here would be the other positional formulas:
SGs – IF ($E2=“SG”,$A2*1,IF(OR($E2=“PG”,$E2=“SF”,$E2=“PF”,$E2=“C”),”“,”“))
SFs – IF ($E2=“SF”,$A2*1,IF(OR($E2=“SG”,$E2=“PG”,$E2=“PF”,$E2=“C”),”“,”“))
PFs – IF ($E2=“PF”,$A2*1,IF(OR($E2=“SG”,$E2=“SF”,$E2=“PG”,$E2=“C”),”“,”“))
Cs – IF ($E2=“C”,$A2*1,IF(OR($E2=“SG”,$E2=“SF”,$E2=“PF”,$E2=“PG”),”“,”“))
Drag all of these down to Row 201 as well.
Setting Up the Calculator: Part 1
For the next section, you’ll want to highlight an area of your OPTIMALS sheet so you know where these formulas are. We’ve dragged the stats discussed above down to Row 201, so I use Cells A202 through J208 (as shown in the image below):
To keep this simple and understandable, I’ll just breakdown the formulas in each cell:
Cell A204 ‘SUM (A2:A201)’
Cell A206 9 – This is just the number of players needed to fill out the roster, which on FanDuel is 9
Cell C204 ‘SUMPRODUCT ($A$2:$A$201,$C$2:$C$201)’
Cell C206 $60000 – This is the max salary allowed on FanDuel
Cell D204 ‘SUMPRODUCT ($A$2:$A$201,$D$2:$D$201)’
Cell H203 ‘SUM (F2:F201)’
Cell H204 ‘SUM (G2:G201)’
Cell H205 ‘SUM (H2:H201)’
Cell H206 ‘SUM (I2:I201)’
Cell H207 ‘SUM (J2:J201)’
Cells I203 to J207 – These numbers represent the Maximum or Minimum allowed number of players for each position. On FanDuel it’s easier since they do not offer utility positions, so every position except Center has a Max and Min of 2 per position. Center has a Max and Min of just 1.
Ready to Set Up Solver
Alright you’re finally ready to set up Solver. Go to the Data tab and on the far right there should be an option for ‘Solver’. If you don’t see this, you’ll need to go to the Add-Ins page on ‘Options’ and enable Solver.
Open up Solver and, for FanDuel, enter the parameters exactly as shown below:
Hit “Solve” after you’ve set up the parameters and you’ll see the number 1 in Column A of the file. The players with a 1 next to their name are the guys who made your optimal lineups.
There are infinite options of how you can build your own file out to make it bigger and badder but for the case of this lesson I thought I’d try to keep it as simple as possible. Excel is a tremendous tool and using Solver to create optimal lineups can be applied to any sport and site for DFS purposes!