In many parts of life, having a combination of two things can lead to better outcomes. Having stocks and bonds means sometimes the stocks do well while at other times the bonds do well, resulting in better overall portfolio performance. Also, the driver of this truck pictured below probably has at least one of his teams doing well.
Over the course of a fantasy football season, we’ve all wished that we could have some combination of players where our fantasy defenses would be playing Jacksonville/Oakland as often as possible, while our fantasy offensive players go against Dallas/Philadelphia as often as possible. What if having two pretty good players (like Tony Romo and Andrew Luck) could be just as good as having one very good player (Aaron Rodgers), due to the two pretty good players having schedules against easier teams that “fit” nicely with each other, with each player tending to play bad defenses on opposite weeks?
Learning how to use spreadsheets can help you think about how to turn a jumble of numbers into useful information, which is what we will do here.
Our Spreadsheet Inputs: We need the NFL schedule, as well as the projected points each week of each quarterback for the 16 weeks in a typical fantasy football season. Let’s assume for the BYE weeks, the quarterback would score points at a replacement player level, about 16 points (although we can make this an adjustable variable in our spreadsheet if you would prefer to set this to 0).
Desired Spreadsheet Output: For each combination of 2 Quarterbacks, show how many total points could be scored if we picked the best quarterback to start for each week.
For Example: Let’s say we are looking at the combination of Tom Brady (NE Patriots) and Tony Romo (DAL Cowboys). This is what their schedules would look like:
And this is what the points per game would look like:
We see that in week 1, we’d play Tony Romo against SF, then in week 2 Tom Brady against MIN, in week 3 Brady again against Oakland, and then in week 4 switch back to Romo against New Orleans while Brady is facing the relatively strong Chiefs defense. Summing the total of the better of the two QB’s points results in 311 total points, while Brady on his own is projected for 301 and Romo on his own is 300 – this is how two pretty good quarterbacks could become better than one really good quarterback!
Let’s get started!
First, the hardest part of this spreadsheet is probably getting and inputting all the data. In Yahoo!, under player list, if you click the little yellow notepad, it will show a projected fantasy points for each week for each player.
We then go through each quarterback and then enter in the information. Luckily, we did this already so you don’t have to! We note that Yahoo! projected Brian Hoyer to play the first few weeks, and then Johnny Manziel would take over – we just lumped the two into “CLE”.
Here’s what our spreadsheet looks like so far:
Now comes the second hardest step (out of only 2 steps), putting in the logic for the output:
For each combination of two quarterbacks, we want to calculate the total points for the season using the best individual matchup in each week. We can start by setting up an output table with all the teams on each of the two axis, and then the total point combination within the table:
Let’s continue using that CAR/ATL output cell as an example and the spreadsheet seen below. For week 1, we would VLOOKUP the value in the first column A163 (CAR) in the QB projected points per week table for week 1 (VLOOKUP(A163, A123:R154, 2,0) – the 2 is the proper column index number to get to the week 1 value, then VLOOKUP the QB projected points for BAL for week 1: VLOOKUP(E158, A123:R154,2,0). Then we use the MAX function to take the greater of the two.
Since we know we will be copying this formula to the rest of the table, we need to put in proper formula references, and freeze the column in the first lookup, the row in the second lookup, as well as the vlookup table. For more on relative formula references, click here: Essential Function Concepts. Here is what our formula looks like so far (near the bottom):
So that gets us the value for week 1, we need to add to that a similar formula for weeks 2-16, adjusting the VLOOKUP cell reference to 3-17 to get the appropriate week:
Finally, we copy in this horribly ugly formula into the rest of the spreadsheet, which results in this not so horrible and actually quite beautiful table output giving us projected total points for each combination of quarterbacks:
We can build another table on the side, which tells us how much better the combo of quarterback is than either quarterback individually, in order to point us to any extreme examples of combo benefits. We add some conditional formatting to make certain values stand out:
Here, we see that Romo (DAL) and Brady (NE), as well as Romo (DAL) and Luck (IND) seem to offer pretty good gains to having a two quarterback combo. Having Phil Rivers (SD) and Jay Cutler (CHI) seems to do well also.
In the Excel spreadsheet, Fantasy Football Player Combos, we do these calcs for defenses as well…check it out to see the results!
Related Posts: A Simple Fantasy Football Draft Spreadsheet