Step 2 — Customize Fantasy Points to Your League

<– Step 1            Step 3–>

In Step 1, we imported raw data for each player’s performance.  We can now assign fantasy points to each player’s performance by week.

We’ll assume the following fantasy point scheme:

1 Passing_Comp = 0 points

1 Passing_Att = 0 points

20 Passing_Yds = 1 point

1 Passing_Int = -1 point

1 Passing_TD = 4 points

10 Rushing_Yds = 1 point

1 Rushing_TD = 6 points

1 Receiving_Rec = 0.5 points

20 Receiving_Yds = 1 point

1 Receiving_TD = 6 points

1 Fum_Loss = -1 point

Naturally, you will want to use your own league’s fantasy point scheme for your spreadsheet.

Now, it’s simply a matter of good old algebra.  (I knew that we would use algebra after high-school; I just knew it!)

First, add the column header, “Fan_Pts”, to the right of your data set.

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 2-1

Then, create the following formula under the Fan_Pts header:

=F2/10+I2*6+J2*0.5+K2/20+N2*6-P2
This formula translates the player’s stats into fantasy points, based on the above fantasy point scheme.

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 2-2

Now, double click the fill handle.  (It’s that little black dot on the lower right of the selected cell.)   And, pow!  You now have fantasy points by player, for each week of every season.  I love that feature!

 excel fantasy football spreadsheet nfl draft cheat sheet

Figure 2-3

In Step 3, we’ll begin the cool part — PivotTables.  With PivotTables, we can convert our data into more useful information.  After that, we’ll analyze further, making our way to a cheat sheet that is customized to your league.

<– Step 1            Step 3–>

fantasy football cheat sheet Excel spreadsheet pivottable vlookup rank tier raw data stats statisticsfantasy football cheat sheet Excel spreadsheet pivottable vlookup rank tier raw data stats statisticsExcel Fantasy Football Book iBook iBookstore iTunes iPad Cheat Sheet NFL

Advertisements

10 responses to “Step 2 — Customize Fantasy Points to Your League

  1. my league 1 Fum_Loss = -2 point so how will the formula be?? assuming all the other stats is the same??

    =H2/20+L2*6-K2+N2/10+Q2*6- ?
    ^
    Thanks guys!

    • =H2/20+L2*6-K2+N2/10+Q2*6-U2*2 <– This multiplies each fumble loss (column U) by 2 points, and subtracts it from the point total.

      Thanks for the inquiry. Good luck at the draft!

      • Hello what will be formula for rb just for rushing yd =10 for 1 point, rushing td = 6 pts, reception yd =10 for 1 pt, reception td = 6 pts and fumble loss = -2.

        will it be =F2/10+I2*6+K2/10+N2*6-P2*2??

      • As this falls into the area of your own customized cheat sheet, I would be happy to customize your cheat sheets for you. Please check out my new service — Customized Cheat Sheets!

  2. help my formula above dont think is correct. cause after end of step 8 my top four tier is forte, ricky williams, thomas jones, hightower . that don’t make sense when adrian peterson, chris johnson, ray rice, mdd?? help anyone??

    • Check your VLOOKUP functions, and make sure that the Col_index_num field references the Average or Median columns on your RB PivotTable. Also, review Help for VLOOKUP to develop a mastery of the function.

  3. I’m having some issues with the formula. Let’s say my passing yardage per point is 50 yards and all touchdowns are worth 6 points. Everything is the same as your example. What formula would I use?

    • The above formula example is for RB’s.

      In the free downloadable data file on the “QB Raw Data 2008-2011” tab, QB Passing_Yds are in Column H, and QB Passing_TD is in Column L.

      So, you would include H2/50+L2*6 in your formula for QB’s. It will look something like “=…H2/50+L2*6…” The first part divides the passing yards by 50. The second part multiplies the passing TD’s by 6.

      You can do the same for QB Rushing_Yds and QB Rushing_TD’s, as well.

  4. How can I add bonus points for hitting milestones, such as “3 points at 300 yards; 4 points at 400 yards; 7 points at 500 yards” to my excel page?

    • Hi, icefire57. The following is from this blog’s FAQ section:

      Q: Can I apply performance bonus points with the Fantasy Football Draftomatic?

      A: No. The Fantasy Football Draftomatic does not have a bonus point feature.

      However, the bonus point feature can be built into the spreadsheet described in my books.

      In addition to the steps in the books, use a nested IF statement for each bonus point tier. Below is a video link that explains how to create nested IF statements. Use NFL stats, instead of dollars as used in the video.

      Nested IF Statements Video

      As you can see, this is pretty complicated stuff.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s