ADP-adjusted Dynamic Value Based Drafting

I’ve created an OpenOffice spreadsheet to assist in fantasy football drafting, and am releasing it at the bottom of this post for everyone to use. If you’re not familiar with “value based drafting” or its “dynamic” variant, here are a few good links explaining the basics. Essentially, the method uses players’ projected stats to determine how much value is available at any given pick, compared to baseline projections of bench-level players and what other owners in the draft are expected to do.

It took several steps to complete this ADP-adjusted Dynamic Value Based Drafting (or, ADP-dVBD) spreadsheet. First, projections on players were collected from preferred sources such as Yahoo, FFToolbox, and FFToday. Second, average draft positions (ADPs) for a site I was drafting on were recorded for each player. In this case, I’m using recent point-per-reception (PPR) ADPs for 12-man drafts from Players are listed by position on different sheets and ordered by ADP. Then, the actual ADP-adapted dVBD calculations had to be created inside the spreadsheet. Since the order of players by ADP does not coincide with the order of players by projected fantasy points, this required a multi-step process. The dVBD offsets (or, “opportunity costs”) are calculated for players, and then the highest (and hence, relevant) opportunity cost at each position among players expected to be taken is found. There is a set of input cells for the drafter to estimate how many of each position (RB, WR, etc) will be taken before his next pick, so that the opportunity costs may be calculated. Lastly, there’s a set of cells to record the positions already taken on your roster, so that opportunity costs are reduced for bench players.

The minimal procedure to use the spreadsheet during a draft as picks are made is:

  • Delete the row (not “hide the row”, not “delete the content”) of a player when he is drafted.
  • When it’s your pick, enter the estimated number of players at each position that will be taken before your next pick comes around.
  • The recommended ADP-dVBD position to pick is the one with highest opportunity cost. Player names are given for the best remaining player at each position.
  • Make your pick, and update your roster listing with the new number of players at each position.
  • Continue!
  • Note, you’ll need to enter what draft slot you’re drafting out of in the appropriate cell, before the draft starts. It’s also easier to make predictions about how many players at each position will be taken before your next pick if you also update the positional rosters of other teams as they pick, but that’s not required.

    It sounds complicated, but is surprisingly easy to use once you open it up and have a look. If you want to use it for your league, you’ll need to update the ADPs, projected fantasy points, and “need multipliers” to match your league’s site and scoring/roster settings. It can also be extended to use additional roster slots, such as IDPs or flex positions. However, those things are left as an exercise for the user. I also left a blank column for player notes, since the spreadsheet is really great for that. I have all sorts of color coded notes in my personal version.

    I learned quite a bit about OpenOffice spreadsheets while creating all this. The OFFSET and INDIRECT functions were of particular value. If you notice any errors or have a particularly slick improvement or optimization to show off, please let me know.

    Good luck, and remember this is just a tool. Projections will inevitably be wrong. ADP-dVBD will be wrong. There is no perfect fantasy football drafting strategy, and it’s more art than science. So if the spreadsheet tells you to pick player A, but player B is also right there and you really want to take him instead — go for it and have fun!

    DOWNLOAD the spreadsheet. OpenOffice 3.0 or higher required to use. OpenOffice is free, and available for Windows, Mac OS X, and Linux. Filetype is .ODT, although obviously this was initially created as an .ODS (spreadsheet). OpenOffice should automatically open it with the proper application. File updated to version 1.2 (bugfix: one-off on dVBD offset player recommendations) on Saturday, September 5, 2009. It is recommended that everyone upgrades to 1.2.

    Also, some shout outs to fantasy football or football sites I enjoy. I already linked Advanced NFL Stats, Football Guys, FFToolbox, FFToday, and Yahoo. But, other good sites include FFLibrarian, Pro Football Reference, Football Outsiders, Pro Football Talk’s Rumor Mill, Rotoworld, Rototimes, FFCalc, and ESPN. Lastly, a special mention for Fantasy Sharks and their Main Tank forum which I often visit.


    10 Responses to ADP-adjusted Dynamic Value Based Drafting

    1. aqualls says:

      hey I just wanted to say this is great. I’ve been playing around with it for awhile and its really useful in the mockdrafts I’ve done so far. I made a few minor changes that I thought I would share.

      1. I used VLOOKUP and HLOOKUP on the Front Sheet to show the next 5 players at each position and their corresponding team and bye week.

      2. I used ADP results for drafts in the same format as mine to provide a guess at how many players at each position will be taken before my next turn. I added those results for each possible slot for each round of the draft to the front page.

      Also the one thing i noticed which i haven’t been able to resolve is that if you enter 0 for the number of players taken at given position it results with the the full score of the top person at that position.

    2. bcbarnes says:

      Thanks! I like your addition #1, with the lookups to provide a list of players. I usually just flip to the positional sheet, but it would also be neat to have the players listed on the front sheet.

      About having “0” as the # to be taken at a position — yeah, it just returns the top player’s total projected points. That’s because (by definition) there is no opportunity cost if you don’t think anyone at that position will be taken. I figured having it return the total points was more useful than an error or a zero. Easier to code, too 😉

    3. bcbarnes says:

      Ok, in version 1.2 (which everyone should get), I have removed the ‘feature’ of showing total points when you enter a zero at # to be taken at a position. It was confusing more than one person, so it’s gone. A couple other bugfixes too.

    4. Mike D says:

      Missed this year, but I’ll definitely look into it for next. Michael J. Fry published “A Player Selection Heuristic for a Sports League Draft” in Journal of Quantitative Analysis in Sports 2007. Take a look if you’re interested in extending these ideas.

    5. Hey, I just wanted to say what a quality website. I certainly enjoyed it and found it fascinating reading. Excited to read your next post!

    6. Joe says:

      I have attempted to download your spreadsheet but get an error message saying that the server is down. Any idea when this will be fixed? I have also downloaded openoffice. Any chance you’ll be making one of these in excel?

    7. says:

      This web site truly has all of the information I needed about this subject
      and didn’t know who to ask.

    8. I was wondering if you ever considered changing
      the page layout of your website? Its very well written; I love what
      youve got to say. But maybe you could a
      little more in the way of content so people could connect with
      it better. Youve got an awful lot of text for only having 1
      or 2 images. Maybe you could space it out better?

    9. Web Site says:

      I’m not that much of a online reader to be honest but your sites really nice, keep it up!
      I’ll go ahead and bookmark your website to come back later. All the best

    10. Paragraph writing is also a excitement, if you be acquainted with then you can write otherwise it is
      complicated to write.

    Leave a Reply

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

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

    Google+ photo

    You are commenting using your Google+ 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 )


    Connecting to %s

    %d bloggers like this: