ADP Availability Workbook
Just yesterday, I mentioned I was happy to get Michael Pineda in the 19th round in Lindy’s mock draft. I am not a believer he can quit getting hit around so hard and therefore lower his ERA near his FIP. I do though want the pitcher with a +9 K/9 and a walk rate under 3 BB/9.
Using NFBC average draft position data, Pineda’s is getting picked as the 230th player off the board but he has gone as early as 184th. A spread of 46 picks is about three to four rounds depending on league size. I created a simple spreadsheet to help an owner know the chances a player is still available at each of their picks thereby knowing when to wait on a player or immediately pick them up.
Merging an owner’s personal projections and the current ADP is probably one of the most important processes in a preparing for a draft. It takes some time (which I understand not everyone has) but it can give an owner a nice leg up on competition. These owners understand when they can get 5th round values in the 12th round.
In past articles, I demonstrated how to go through each round and use ADP to see which players may or may not be available. I would sort of gamble on when a player may go using the earliest and average pick. By setting up a spreadsheet to calculate the normal distribution using the draft pick data from NFBC.
Creating the spreadsheet is fairly straight forward. The inputs to the Excel normal distribution formula are the actual pick, the average pick, and the standard deviation. The only value missing is the standard deviation but it can be calculated using the Range Rule for Standard Deviation. The rule uses just the minimum and maximum values to get an estimated standard deviation. We are aiming for a general estimate of fantasy player availability, not perfection.
I decided to take this number crunching out of your hands and input the formulas into an easily downloadable spreadsheet (File -> Download As). Additionally, I went to NFBC and entered their current ADP values to finish filling out the spreadsheet.
The spreadsheet is fairly easy to work. Just put in the number of teams in the league and your pick and the chances a player is available at your picks will automatically update. For example, I could value Michael Pineda with the 60th overall pick. No one has taken him there so I can wait. It isn’t until the 16th round until he first gets taken but there is a 1% chance he will have been drafted. If I wait until the 18th round, his chance of being taken increases to 24%%, then up to 26% in the 19th round, and finally over 50% in the 20th round. I will then pencil him in for my 18th round choice and feel fairly confident I will pick him then.
Additionally, the ADP data should be updated from NFBC unless your draft is within a few weeks of this article being published. I haven’t delete any columns from the NFBC formatted tables. All a person needs to do is go to their website and copy-and-paste in the update ADP.
One issue some owners will run into is not knowing their draft position until just before their draft starts. Instead of trying to figure out all your targets right before or during the draft, I’d find targets from round 6 and on by setting the pick option to middle option like eight in a 15-team league. The targets will stay the same expect in the early rounds where the players available with the 1st and the 12th pick are completely different. I would quickly step through the top picks to see your 15th overall valued player can slide to the third round.
One item to keep in mind is the website hosting the draft. If a website has both ADP and earliest draft position, use their ADP instead of the NFBC’s values. Most websites post their own rankings and I have found unprepared owners generally stick to these site rankings when drafting. The website’s ADP will therefore closely mirror their rankings. The website’s ADP may force your hand earlier or later depending on their ranking. The problem is most websites don’t include the earliest and latest pick to help set the distribution so owners may be stuck with NFBC rankings.
I think that is it. It is a pretty straightforward resource with only two inputs. Let me know if the procedure or worksheet is unclear and needs to be adjusted.
Jeff, one of the authors of the fantasy baseball guide,The Process, writes for RotoGraphs, The Hardball Times, Rotowire, Baseball America, and BaseballHQ. He has been nominated for two SABR Analytics Research Award for Contemporary Analysis and won it in 2013 in tandem with Bill Petti. He has won four FSWA Awards including on for his Mining the News series. He's won Tout Wars three times, LABR twice, and got his first NFBC Main Event win in 2021. Follow him on Twitter @jeffwzimmerman.
Nice! Thanks, much appreciated
Thanks
how do we edit this spreadsheet as it’s view only?
I tell you how to download it for personal use in the article.
ah ok I missed that on first read… so I know you said you can copy and paste the updated ADP, but will you be updating that worksheet along the way as well? Either way, this is amazing. Thank you. It will come in handy for my NFBC drafts
Really cool! I never thought of looking at ADP in this manner, but it makes total sense.
I may have missed it or misinterpreted it, but does the percentage represent Player A has been drafted x% by now, or does it represent Player A will be drafted x% before your next pick?
In your Pineda example, does the 4% represent he has been drafted 4% of the time by your pick in round 16, or that by your pick in round 17, he will be drafted 4% of the time? Hope that makes sense, but this is a big difference, especially in the earlier rounds.
That is the percent chance he will be gone by that pick in that round. See how Mookie is at 9% in the first round? He’d be 100% if that was “by your next pick in round 2”
Hi Jeff, is there a way to edit the spreadsheet to remove players for a keeper league while maintaining the rankings?
Depends on the league settings but if your league has a set number of keepers then it’s easy. Remove all the players that have been kept and hide/ignore the columns for rounds 1 to [# of keepers per team].
For keepers, couldn’t you theoretically just delete the kept players and shift the next player up to fill the keeper’s previously ranked position? Obviously this doesn’t account for the fact that there will be a lot more discrepancy in draft position with lower ranked players, but it seems like the easiest way to handle keepers if you don’t want to think too much about it. I only say this because you’d start in Round 1 regardless of how many players are kept, you just have a different player pool to choose from.
Incredible! Only suggestion would be to flip the % into chance of being available instead of chance of being gone. That makes more intuitive sense to my brain, but maybe not others.
Would be an easy change to the formula. just add “=1-” in front
Very cool. I always wondered how people did these calcs for their sheets, but never dug into it.
I would suggest using STDV from fantasypros.com to account for the relative risk/reward profiles of individual players. It’s not perfect, because it is based on average ranking instead of ADP, but it is more realistic IMO.
For example, your stdv estimate for Trea Turner is 3, while the stdv from the consensus rankings is 14. Miguel Cabrera, on the other hand, has a stdv of 2 while being ranked in a similar spot.
Cool stuff, but there is some quirkiness to the values when the spread between earliest and latest pick is very large. Kelvim Herrerra is a good example (as are alot of the MR), his stdev is large at 56.75 meaning that he shows a 21% chance of being picked by round 8 even though that would be before the earliest recorded pick at 94.
I still like the thought and work that went into it. It presents a better visual approach to managing that analysis during the draft.