Fantasy Football: Creating A Cheatsheet

Dennis Towle Jr.Contributor IAugust 7, 2009

GREEN BAY, WI - AUGUST 03: Helmets sit on the field during the Green Bay Packers practice at summer training camp on August 3, 2009 at the Ray Nitschke Field in Green Bay, Wisconsin. (Photo by Jonathan Daniel/Getty Images)

Ok great, so now you’ve signed up for a fantasy football team and you are waiting for the inevitable auto-draft.  What now?  Well, most fantasy football aficionados will tell you that you should “pre-rank” your players.  You think, why – I looked at the website’s player rankings and it looks OK to me?  That’s a very popular misconception and probably the number one reason that first-timers don’t do very well come the end of the fantasy season.

The draft is the most important step in the fantasy football season.  Let me repeat that.  The draft is the most important step in the fantasy football season.  It will be the reason that you either win or lose your league.  It will not be because one or two of your studs had a bad game.  It won’t be because one or two of your players gets put on IR.  It will be because you made contingencies for these kinds of events, because you drafted not just the studs, but very capable backups, and because you planned for player inconsistencies.

Using the website’s rankings to do your draft means that you will draft players according to how their fantasy gurus see things and how they ranked the players.  Pre-ranking your players according to your system is crucial to a great draft.  It sets you apart from the also-rans and the lazies.  The question is, how do I go about this pre-ranking thing?  There is a very simple solution using a very simple philosophy:  What did you do last year and what might you do this year is going to determine what I do right now!

You can go to almost any fantasy football website and get that website’s top 100 – 200 – or 300 players, and therein lies the problem:  Which website has the best rankings?  They all have rankings that are compiled by that website’s crack staff.  These people believe “X” player has this much value and “Y” player is worth this much.  Remember, these are just opinions.  X or Y player may or may not be the answer to your needs.  My suggestion:  Make your own cheatsheet, your own top 100 – 200 – or 300 players, and then use that as your Pre-Rank List.

Start by creating a spreadsheet, it will be your database for your picks.  I use Microsoft Excel, but there are many spreadsheet programs out there to choose from, and they all work essentially the same.  You’re going to build the spreadsheet from data that you glean and input into the file.

You took a first step by picking a website and joining a league.  The league that you joined will have a scoring system setup that it uses for the season.  What is a TD worth, how many points for X number of yards or X number of tackles, how many points for a field goal, etc.?  Those points are built into the league before the first player is picked.

What you as an owner need to do is look at last year’s players statistics as your Step 1 in building that spreadsheet.  Sort the player stats by fantasy points in ascending order, then, copy down the first 300 players in the order of the points that they garnered, irrespective of their position.  Don’t forget to also copy down the Kickers and the Defenses or defensive players and add them in to your list according to the points that they made.  Your list should consist of column 1 – player name; column 2 – position; column 3 – team; and column 4 – fantasy points.  If you want to include any other columns, that will be your choice.  You should now have a list of about 364 player/teams (or more if using defensive players).  Don’t forget to include the players position and team.  Copy that list to worksheet 1 and worksheet 2.

Your second list is a little easier to cull, it involves copying that websites 100 – 200 – or 300 player ranking.  This list is going to include some players that are not on your first list.  Those players would be this year’s rookies and players that were on IR the previous season.  Don’t forget to include the players position and team.  Put that list on worksheet 3.

At this point you have enough information to make your first cheatsheet or pre-rank list.  A little patience is now going to be needed to combine the 2 worksheets.  Remember, we copied the first list onto 2 worksheets.  We will keep the second copy as the base copy and we will manipulate the first copy.  On worksheets 1, 2 and 3, we are going to add a column to the left of our list.  In this column we are simply going to number each cell starting with 1 down to the last player/team on the list.  This gives us a visual numerical listing.

On worksheet 1 we will add 4 columns to the right of our list.  Label the first column Last Year, label the second column This Year, label the third column SUM, and label the fourth column AVG.  In the column Last Year, start with the top row and type the number 1 and then continue down the column adding 1 to the number from the line above.  You now have a ranked list.

On worksheet 1, in the column labeled This Year, match the player name, position, and team to the corresponding line from worksheet 3 and then type in the ranking number from that line.  The new players not listed from last year will have to be added to the bottom of the list on worksheet 1.  Their value for the column Last Year should be a number that is 1 greater than the last number used.  (For example, if your list Last Year is 200 players long, then use the number 201 for the value of each player added to the list.)

You now should have columns Last Year and This Year propagated with rankings from worksheets 1 and 2.  Add the 2 values (from each row) in columns Last Year and This Year together and put the product in the column SUM.

In the column AVG, put in a formula that takes the value of SUM and divides it by 2 (or the number of columns of data).  Now, sort your worksheet according to the AVG column in ascending order.

Lastly, starting at the top of the worksheet, in the column to the left, you are going to type the number 1 and then continue down the column adding 1 to the number from the line above.  You are finished – save your work.

What you now have is a ranked list for that website that combines what the website authors conceived for a ranking with what actual fantasy values were from last year, and has rookies valued where they should be.  If you are a fantasy fanatic like I am, you can expand your worksheet to include fantasy rankings from several of the top websites – you just need to adjust the SUM and AVG formulas to accommodate the extra columns.

Now go and select “Pre-Rank Your Players” and use your list.  When you are done, remember to click “Save”.  Now, just sit back and wait for the draft… the most crucial part of the season is taken care of.  I guarantee that you will be pleasantly surprised by what you get and you won’t get stuck with a bunch of no-names and rookies.

Good luck!