How to Build Your Own Fantasy Football Rankings – Part 2
Part one of this series was an overview of how to build your own Excel-based rankings table using the Fantasy Footballers industry-leading rankings as the baseline. I would highly recommend you read that first before this part.
In the previous article, we built out a table for the quarterback position; all of the same steps would then have been used to create a table for each of the core fantasy positions: QB, RB, WR, and TE. For those base tables, we used static rankings pulled from the Fantasy Footballers rankings page. Even if you’ve never paid much attention to early off-season rankings in the past, you will recognize that they are constantly changing. Every time news is released, the rankings are tweaked to represent the most current information available.
The Fantasy Footballers industry-leading rankings are no different. Andy, Mike, and Jason are ALWAYS updating their rankings. In fact, in the short period of time it took me to build my sample page and write this article, the rankings have changed. The point of this article is the process of building the rankings, not the rankings themselves.
Updating the Rankings
If you only used part one of this series, you would have to perform a lot of manual work to update the rankings to reflect the most recent Fantasy Footballers ranks. Combing through the rankings and comparing them to your sheet is a highly ineffective use of your time. What this article will do is walk you through how to build a “data sheet” for the positions, and then the formulas available to you to pull the data into your formatted tables, which at this point also include your own rankings; rankings that should also be tweaked periodically.
The Data Sheet
As you will recall from Part 1, pulling the rankings from the Fantasy Footballers website is easy, as they provide you with the COPY function right at the top of the rankings. When pasting this data into the Excel sheet, it will be “unformatted”; simple text. We previously spent a lot of time constructing those nice-looking tables, we don’t want to re-do that every time.
To create the “data sheet”, you will click the “+” tab at the bottom to create a new sheet, just like you did to create new tabs for each position. I like to name this new tab to include the position and the word data. In the example, I used “QB data”. I use the word data on the tab to indicate quickly that this tab should not be modified. Each position will have its own data tab and rankings tab. These new data sheets will serve as the host of our copy/pasted rankings from the website. There will be no formatting, no changes, no formulas on this page. Simply paste the data as shown below:
Static Numbers to Formulated Data
Over on the “QB” tab that was previously built, the rankings posted under the “consensus” and under each of the Ballers was static – unchanging from whatever we pasted that first time. In part one, when we used the “format as table” function, we also saved ourselves a lot of time for future work. When you type a formula into a cell within a table, the rest of the blank cells in the table will be automatically filled in with the corresponding formula, adjusted for the reference cell. In simpler terms – no copy/paste, no drag/double-click to fill; Excel does the work for you!
A key point here; the cells need to be blank for the tables autofill function to work. While this will seem scary at first, the first step is to delete the rankings under each of the four columns: Consensus, Andy, Jason, and Mike. Don’t worry, these rankings are outdated; that is why we’re pulling in new data!
Two Formula Options, Similar Result
Anyone who already knows a bit about Excel’s formulas recognizes that there are often multiple formulas that can be used to achieve the same end goal. In this case, we need the formula to find a player’s name and return the corresponding Baller ranking into the correct column. There are two options I will share to accomplish this task. At this point, neither is right or wrong, nor more beneficial than the other at this time. Knowing how to use both formulas, however, is very important if you want to continue to use Excel for your annual fantasy analysis.
The first option is part of the “sum” function family. In Excel, there are many variations of the sum function, in our case, we will add the “if” portion. A simple “sum” formula will add up all the numbers within a designated range of cells and/or columns. Adding the “if” function brings in the ability to only add up data that matches your “if”.
*Beginner Tip: Excel has a great formula builder tool that will really help you when building formulas, use it! The formula builder opened by clicking on the stylized “fx” directly to the left of the data entry bar.
Excel describes the “sumif” formula as “Adds the cells specified by a given condition or criteria.” Here is the logic behind the “sumif” formula using the formula builder; in a range of data, find the specific criteria, and sum up the data in another range.
The second option is a slightly more advanced formula that will “lookup” the first occurrence of a specific criteria and return the data in the same row from another column. Excel describes the “vlookup” formula as “Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in an ascending order.”
Using the formula builder, we will look up a value, in a table array, identify the column index number holding the target data, and lastly find a close match or exact match with range lookup.
Where the “sumif” formula will only return numerical data, the “vlookup” formula can return text. Additionally, “sumif” will sum up multiple points of data if the criteria are the same, such as points scored by QB; “vlookup” will only return the corresponding data from the first occurrence of its criteria.
Putting the “sumif” Formula to Use
For this section, I’m using the “sumif” function. I believe it is a slightly easier formula to use and understand how it works. I will re-introduce “vlookup” in Part 3 of this series. “Sumif” is also a valid option in most rankings lists because there are not (currently) any players with the same name at the same position, so the data to be “summed” is only one number. In this ranking build, we want to pull the rankings from our “QB Data” tab and show them in our “QB” table. The first column we will pull in will be the consensus. I highly suggest using the formula builder and that is what I will reference in this example.
In the first cell under the consensus header (E4 in my example), we will begin to enter the formula:
After typing that, click on the “fx” to open the formula builder.
RANGE: The first part of the formula is to identify the range of cells where our criteria might be found. As we learned in part one, Excel will allow is to “click our way” through a formula build. In our example, we want to look in column A on the “QB Data” tab for a specific player’s name. Click on the “QB Data” tab, and then simply click on the “A” column to highlight the whole column.
CRITERIA: This is the specific player on the “QB” tab we are looking for in the range above. In this case, we’re looking for the player in cell A4, “Lamar Jackson”. In the formula builder, you can simply click on A4.
SUM_RANGE: The Sum Range is the column of the rankings. The consensus ranking to find is on the “QB Data” tab is found in column C. Since we are looking for a specific player, the result will be that players ranking, in this case, 1. Your formula builder will look like this.
When you click done on the formula builder, Excel will automatically fill in the rest of the cells below E4 with the same formula, with the criteria always referencing the player in column A. Excel’s formulas can also be a little tricky. Instead of having to retype that formula into each column, wouldn’t it be nice to just copy/paste or autofill it across the columns? Great news! You can with a little tweak to your formula. By default, copy/pasting or dragging to autofill adjacent cells will also change the formula. If you copy/pasted this same formula into column F to find Andy’s rankings, it would also change the referenced range and criteria targets by one column to the right; which would find you looking up the team abbreviations instead of the player names.
If you want to keep the source range and criteria in column A while allowing the sum_range to move with you to the right, you will need to add dollar signs ($) to your formula. When used within an Excel formula, the dollar sign tells Excel that you want that specific data source to stay in its column or row. On our rankings we want to keep our range in “QB Data” tabs column A, our formula needs to be changed to show: ‘QB Data’!$A:$A
The Criteria unfortunately will not work the same in the table format. Luckily, all you will need to do is go into the formula in F4 and move the reference back to the Name instead of Team. The table will update all the cells below it to have the same criteria reference. Below is an image showing the formula in each cell for reference.
After placing the formulas into each column, you should now have a ranking table that is pulling the rankings from your “QB Data” tab. The reason for that tab should now be evident. Anytime you want to pull the most recent QB rankings, you simply go to the Fantasy Footballers Rankings page to copy them and paste right into the “QB Data” tab. The formulas on the rankings page will automatically show this new data right next to your personal rankings. Since our “My Consensus” column from part one was also a formula, that will update as well. Once your QB page is working, you can do the same process for each of the other positions.
Part 3 – Introducing Additional Ranking Sources
In the next part of this series, the build will be updated to introduce another data set, this time from an outside rankings page, such as the FantasyPros consensus rankings. Be prepared for some more advanced formulas.