I was able to contact the creator of the NFL mock draft spreadsheet, a sports fan known as “Beerfish” whom I’d like to thank for answering a few of our questions about his fantastic spreadsheet, which you can download from Google docs.
ESH: How long have you been using Excel?
B: Probably about 8 or 9 years.
ESH: Where did you get all the player data from?
B: If you ‘unhide’ Columns K to T you will see web sites where I got the player data from.
ESH: How did you populate the names?
B: In most cases I copied from web sites and then pasted as ‘text’ into a clean sheet. I then used ‘text to columns’ to stick the text data into columns and then transfer to the main player sheet.
ESH: How were players ranked?
B: By averaging all of the site rankings that I have (once again the hidden rows, some are pure player rankings some are mock draft rankings.)
ESH: What formulas were used?
B: Well, “Average” formulas for the averages but mostly I used the “VLookup” formula to populate the mock draft sheet from the player data sheet.
ESH: What aspects of the spreadsheet are macro driven?
B: The only parts that are macro driven are the buttons on the pages and all they do is make it easier to move from page to page and to filter. You really don’t need to use macros at all. You can do everything the macros do manually fairly easily by just moving from sheet to sheet with the tabs on the bottom and buy using the filtering arrows on the player data sheet.
ESH: How long did it take you to put all this information together? Your spreadsheet is a literal mock draft database.
B: It varies, the 1st year which was about 5-6 years ago it took quite a while fiddling with formulas and such. Now that I have the spreadsheet in place I just populate it. I usually update the rankings a couple times. Might take 3 or 4 hours to populate now that I have the sheet all ready.
ESH: Who do you think will be picked top five and in what order?
Wow this is a tough one, my voice wouldn’t carry any more than anyone else but I’ll wing it:
1) Andrew Luck
2) Robert Griffin III
3) Matt Kalill
4) Trent Richardson
5) Morris Claiborne
ESH: As a Browns fan, I would love Griffin but I’ll take Richardson too. Do you make any other sports related spreadsheets?
B: Yes, I do the same thing for the NHL draft as well.
ESH: Thanks again for answering our questions and for making such an awesome spreadsheet. I know it will help my NFL fantasy mock draft strategies this fall. All this talk about the NFL draft is getting me excited for the fall, especially since the 2012 NFL schedule has just been released (and I'll be working on a 2012 NFL helmet schedule soon).
Download the 2012 NFL Helmet spreadsheet.