Writers of Pro Football Prospectus 2008

18 Aug 2011

Dynamic Drafting Spreadsheet

Hey all,

Last year, I posted the spreadsheet that I'd been using for dynamic value based drafting in this thread: http://footballoutsiders.com/discussion/draft-order-strategy

Based on feedback I received from the users here and observations from my own continued use, I've been adding refinements to the spreadsheet to make it more useful and user-friendly. This year, I've modified and hopefully improved how selected players are removed from the calculations and added an estimate of opponent team needs.

You can find a good primer on dynamic value based drafting here: http://www.thecoordinator.com/dynamic-drafting.html

You can download my newest version of the dynamic drafting spreadsheet here: http://www.mediafire.com/?d3zcm4h43dfutg6

I appreciate any feedback or error-reporting.

Posted by: tally on 18 Aug 2011

59 replies , Last at 26 Aug 2012, 7:47pm by JRocker

1
Re: Dynamic Drafting Spreadsheet
by TruFloridaGator :: Thu, 08/18/2011 - 9:16am

Pretty sick. Thanks.

1)So do we just do RB/WR/TE 1 for a flex spot or does doing .5 for RB & WR make more sense?

6
Re: Dynamic Drafting Spreadsheet
by tally :: Thu, 08/18/2011 - 2:35pm

This question came up last year and here was my suggestion then was to keep the positions separate and make estimates of how many would be taken while accounting for the flex position, as each team can only draft two players before your turn comes up again.

However, it is a little trickier now that I've added estimated need. You'll be fine inputting each as a half, but I think inputting the flex spot as a whole starter to each position might be a better assessment of need. There will be other factors that decrease this projection, such as the limit of 2/team between your picks and your own overrides.

And yes, I failed to add the 2/team limit in my formula for estimated need, so that will be updated.

48
Re: Dynamic Drafting Spreadsheet
by uscar :: Thu, 09/01/2011 - 2:36am

Just for clarification:

If your starting line-up is, for example, 1 RB, 2 WR and 1 RB/WR, should you input the positions as:

Option 1:

RB - 1
WR - 2
RB/WR - 1

or

Option 2:

RB - 2
WR - 3

or Option 3

RB/WR - 4

49
Re: Dynamic Drafting Spreadsheet
by tally :: Thu, 09/01/2011 - 10:44am

Go with Option 2. You can actually also do RB - 1.5, WR - 2.5 but I don't know if it handles fractions very well, so you may have to manually round any fractions that it spits out.

You might also want to estimate what that RB/WR flex is going to be filled with--in your case, perhaps more RBs than WRs, and therefore what will be drafted. The #Starters is really just there to give a gauge of how teams will draft based on starting slots.

2
Re: Dynamic Drafting Spreadsheet
by bird jam :: Thu, 08/18/2011 - 12:37pm

Awesome, thanks. I look forward to checking this out.

3
Re: Dynamic Drafting Spreadsheet
by DRoemelt :: Thu, 08/18/2011 - 12:45pm

I downloaded this and shortly thereafter got an e-mail from comcast saying that I have a bot on my computer; not saying they ARE related, just posting because they MIGHT BE.

thanks.
Doug

4
Re: Dynamic Drafting Spreadsheet
by ejayp :: Thu, 08/18/2011 - 1:21pm

Probably just a coincidence. I ran it through 35 virus scanners and it came back clean on all of them.

http://www.virustotal.com/file-scan/report.html?id=04b14fb18fff0085657f1...

5
Re: Dynamic Drafting Spreadsheet
by tally :: Thu, 08/18/2011 - 2:24pm

There is a macro on the spreadsheet but otherwise I don't know what could trigger that.

As far as I know, Mediafire is also a relatively trustworthy share site.

I have already found an error in the Estimated Need calculation (it won't affect positions with fewer than 3 starters) that I will need to address, so there should be an update soon.

7
Re: Dynamic Drafting Spreadsheet
by tally :: Fri, 08/19/2011 - 10:22am

This version fixes the formula for estimated need.

http://www.mediafire.com/?addd9syvblltzls

8
Re: Dynamic Drafting Spreadsheet
by CheeseHead :: Fri, 08/19/2011 - 11:27am

I have Excel 2002 which is apparently too old for this. It says some cells have overlapping conditional formatting ranges, which 2002 can't handle, so it opens the spreadsheet read-only. What do I need to run this? Thanks!

10
Re: Dynamic Drafting Spreadsheet
by tally :: Fri, 08/19/2011 - 2:39pm

This version is Excel 2007/2010. I will u/l a Excel 97/03 version. I don't think it loses too much functionality except perhaps for conditional formatting.

Here is the Excel 97-2003 compatible file. Don't know why it's 3x the size of 2007/2010: http://www.mediafire.com/?4oc1aulbp4encu1

9
Re: Dynamic Drafting Spreadsheet
by krugerindustria... :: Fri, 08/19/2011 - 1:46pm

Tally this is tremendously cool. I've got a few questions after playing around with it for a couple of days (using Kubiak data)

*first, when I put in the current round (say 1) and my pick (say 5) the sheet indicates that there are 14 selection till my next pick. This is true for the 2nd round, my next pick is actually only 4 selections away at the beginning of round 1.
*Also, shouldn't this number decrease as selections are made by other poolies?
*Do I understand that I need to estimate the positions chosen before I pick? does that number need to agree to the number of selections before I choose? i.e. for my first round pick, estimate 3 rbs and 1 qb or can I just say 2 each of rb,qb & wr, will the spreadsheet work?
*Lastly, should I be using the Dynamic rankings page to base my picks on (re-generated before I select)? This says I should go with Gates which would obviously be a big stretch in rd 1, or is this where the judgment comes in?

Thanks for all your work on this. You have expanded my knowledge of excel just by going through this. If only I can apply some more of it to work, I could better justify this time.

cheers

12
Re: Dynamic Drafting Spreadsheet
by tally :: Fri, 08/19/2011 - 4:28pm

So the selection number is only something you need to look at when your pick comes up each round. So if you're in a 12-team league and drafting 5th, when your turn comes up in round 1, there will be 14 selections until your next pick; in round 2, there will be 8; and this repeats. This number is important in that it is the maximum number of players that will be chosen before your next turn comes up.

You can use it to estimate the number of players at each position that might be selected in order to determine your baselines at each position. Let's say it's round 2, you've selected a RB with pick 1.5 and you're deciding on your next pick. There will be 8 players taken between your turns in rounds 2 and 3. So your baseline player will not be more than 8+1 spots down at any position--even if 8 straight RBs are taken, the worst RB left on the board will be the 9th ranked RB. The estimated needs field will further guide you by indicating, say, if you only start 1 QB, that the QB need is 4, as teams will not likely select more than 1 QB each before your next pick, so even at most 4 QBs will be taken, and the 5th ranked QB is your baseline. If one of teams 1-4 already took a QB in round 1, then the QB need drops to 3.

The spreadsheet defaults to estimated need at each position, but you should always override it where appropriate. I gave an example in the instructions that in a standard league you should set all non-QB/RB/WR estimates to 0 at the start of the draft and increase it based on ADP (e.g., TEs start coming off the board in rounds 3-4, DEF/IDPs around 5-8, kickers in the last 2-3 rounds).

The dynamic rankings tells you which player has the most value before each pick--it basically summarizes all the FPOB across positions in an easy-to-read table rather than requiring you to scroll around the Draft sheet. The example with Gates is that you should set the TE number to 0 in rounds 1-2 because you don't expect any to be taken before round 3, so Gates' FPOB should be 0--he'll still be there the next round. Even though every team has a need at TE, they usually won't select one. So that's where the estimated need is a guideline. It saves me the trouble of having a separate sheet wherein I cross out each team's starters as the draft progresses.

EDIT: If you are picking at either end of a snake draft (i.e., 1st or last) set the round number to the next round; otherwise, the formula indicates that there are 0 picks until your next turn (which is true) but doesn't really guide your pick. So if you pick 12th of 12 teams, you have picks 12 and 13 (1.12, 2.1), when your turn comes up in the first round, just set the round number to 2, and Excel will indicate that there are 22 picks until your next selection.

11
Re: Dynamic Drafting Spreadsheet
by tally :: Fri, 08/19/2011 - 4:17pm

Found an error in the FPOB calculations. This update should fix that.

Excel 97/03: http://www.mediafire.com/?9raf8dabn8n5pa9
Excel 2007/10: http://www.mediafire.com/?3tlbogau1l2bfx7

13
Re: Dynamic Drafting Spreadsheet
by CheeseHead :: Sun, 08/21/2011 - 5:12pm

Something's wrong. I'm using the 97/03 version 3.11. I have Excel 2002. I fill out the pre-draft info. Then I copy QBs outta KUBIAK into the Draft sheet, no problem. When I copy the FPs, I need to paste special / values to get values into the Draft sheet. No problem, but the FPOB column fills with #NAME? I tried to ignore that and finished filling in the Draft sheet. But the estimated needs area is all #NAME? too, and when I press the Generate Dynamic Rankings button I get an error message "#REF.xls could not be found". Thanks for your help...

League Settings Enter: Number of Starters:
Number of Teams 10
Draft Position 9
Position 1 QB 1
Position 2 RB/WR 5
Position 3 TE 1
Position 4 K 1
Position 5 IDP 3

UPDATE: the Macro stops on this line with an "Object doesn't support this property or method"

ActiveWorkbook.Worksheets("Dynamic Rankings").AutoFilter.Sort.SortFields.Clear

14
Re: Dynamic Drafting Spreadsheet
by tally :: Sun, 08/21/2011 - 6:38pm

Hmm. It's working fine when I try it, but I'm using Excel 2010 in compatibility mode.

The #REF and #NAME errors suggest to me that a reference is broken somewhere as all of those fields are dependent on the pre-draft and other cells, and that's screwing around with both the FPOB fields and the macro. Can you save and send me the file to: bleedingpurpleandgold@gmail.com so I can see if I can diagnose the issue?

15
Re: Dynamic Drafting Spreadsheet
by naosu6 :: Mon, 08/22/2011 - 12:15am

Are you aware of anything like this for auction drafts?

18
Re: Dynamic Drafting Spreadsheet
by tally :: Mon, 08/22/2011 - 9:41am

Dynamic VBD is dependent on the selection nature of serpentine drafts. For auction drafts, I think straight VBD is best for estimating value.

16
Re: Dynamic Drafting Spreadsheet
by fotop :: Mon, 08/22/2011 - 5:33am

Hey Tally,

Looks like a great sheet, would really like to give it a spin. Only problem is I'm having the same issue as jimcrist describes above. If you guys find a solution/fix, would you mind posting it here? Thanks for the help.

For reference, particulars for the league I'd like to setup in the sheet, if needed:

12 Team league, 11th draft position. 1 Qb, 2 Rb, 3 Wr, 1 Te, 1 K, 1 DST.

17
Re: Dynamic Drafting Spreadsheet
by tally :: Mon, 08/22/2011 - 9:36am

Can one of you send me your sheet once you've populated so I can see where the problem is, as I'm not getting it when I'm filling it out? Thanks.

19
Re: Dynamic Drafting Spreadsheet
by tally :: Mon, 08/22/2011 - 11:16am

Figured out the issue. The macro link got broken during conversion from the xlsm to xls format. Should be fixed now.

Thanks to jimcrist and fotop for pointing out the error. If you find any other errors, please bring them to my attention. Thanks!

Excel 97/03 v3.2: http://www.mediafire.com/?3wdbfr3xz35y3r5

20
Re: Dynamic Drafting Spreadsheet
by fotop :: Tue, 08/23/2011 - 12:04am

Thanks for the quick response. Unfortunately, I'm still seeing the same issue w/the new sheet as the macro is still pointing to the following line of code as the issue:

ActiveWorkbook.Worksheets("Dynamic Rankings").AutoFilter.Sort.SortFields.Clear

Not sure if anyone else is still having the issue, but just wanted to provide an update. Maybe the wrong file was uploaded or the macro adjustment wasn't saved?

Thanks for the help.

22
Re: Dynamic Drafting Spreadsheet
by tally :: Tue, 08/23/2011 - 1:06pm

Sorry. Still working with jimcrist on the issue. I'm pretty sure the errors are resulting from added functionality in 2007 that isn't present in earlier Excel versions. Since I don't have an earlier version of Excel to vet the issues, I've been having to send it to him to vet.

21
Re: Dynamic Drafting Spreadsheet
by spujr :: Tue, 08/23/2011 - 12:58pm

I think this is a really cool app. However, I am trying to figure out how best to use it with keepers where each manager gets to keep 1 but must draft that one in the same round as last year. For example, A. Foster was drafted R3 so the owner has to draft him in R3 of this year.

Again, great app!

23
Re: Dynamic Drafting Spreadsheet
by tally :: Tue, 08/23/2011 - 1:14pm

I assume you forfeit that keeper pick pre-draft, correct? In that case, you would use normal VBD to determine value and if you want to keep that player (e.g., Foster's top 3 RB overall, so you would use that R3 pick for him). Then indicate those players as picked when you launch this spreadsheet. You would also have to tweak the numbers a little to account for teams that have already used their pick for that round, since that reduces the number of players that would have been selected.

34
Re: Dynamic Drafting Spreadsheet
by spujr :: Thu, 08/25/2011 - 12:07pm

Right on all points. I am more trying to figure out how to best use the estimated needs with this factor. For example, in round 3, I already know 6 of the 12 managers are going to pick their keepers who happen to be QBs in later rounds (7+). Thus, I'm thinking I could put "6" under the QB estimated needs knowing no more than 6 QBs will be selected in the next couple of rounds.

26
Re: Dynamic Drafting Spreadsheet
by tally :: Wed, 08/24/2011 - 10:26am

Okay, I rewrote the macro for Excel 1997/2003 and it seems to work. Also deversioned the file, since the original fix didn't resolve the issue.

Excel 97/03, v3.11: http://www.mediafire.com/?3g6r03zh86sfgl0

You may also need to let Excel install an Analysis Toolpak Add In in order for it to recognize some of the formulas such as ISODD, apparently. That is the source of the #NAME errors.

Thanks a bunch to jimcrist for helping me to troubleshoot this version on his Excel, which would have been impossible for me with the version that I have.

EDIT: Made a booboo, hopefully fixed (for real).

24
Re: Dynamic Drafting Spreadsheet
by fotop :: Wed, 08/24/2011 - 7:42am

Really appreciate you guys trying to help out with this. Seems like I'm the only one still having issues. On the plus side, it's a new issue. When I open the sheet, it asks me if I want to update links, when I click yes, it says they cannot be updated. Presumably, that's issue #1, and related to the 2nd issue (as the links are pointing to the same sheet referred to in the formula below). The 2nd issue is when I run the macro, the forumla that's returned on the "dynamic rankings" tab looks like it's pointing to a sheet on someone else's pc...if that makes sense. For example:

=OFFSET('C:\Documents and Settings\\Desktop\[Dynamic Drafting 3.2.xls]Draft'!Z250,0,7)

If my assumption is correct, I think I should be mapping this to my own workbook but my VBA/excel skills are clearly lacking.

Tally, I've emailed you my sheet to see if you can help out any further, and so I won't spam the board up. Apologies for being such a pain.

25
Re: Dynamic Drafting Spreadsheet
by tally :: Wed, 08/24/2011 - 10:25am

No, I had a brain fart and copied the code over from a different worksheet.

This should be the correct one (fingers crossed): http://www.mediafire.com/?3g6r03zh86sfgl0

29
Re: Dynamic Drafting Spreadsheet
by Hawke :: Thu, 08/25/2011 - 2:57am

I'm on Excel 2003 and have been having the same problem. Name error in FPOB column where player and points are populated, Estimated Need cells and in cell A3. I just downloaded your latest version and same error.

32
Re: Dynamic Drafting Spreadsheet
by tally :: Thu, 08/25/2011 - 11:00am

You need to install the Analysis Toolpak since Excel 2003 doesn't seem to have all the formulas loaded by default.

http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-H...

27
Re: Dynamic Drafting Spreadsheet
by tamburgy :: Wed, 08/24/2011 - 8:14pm

I'm having issues with getting this setup properly. On the draft page, it's showing #REF! as the FBOB for about 75% of my positions, and giving me the actual FBOB numbers for the remaining 25%.

Any idea what I did wrong?

Thanks!

28
Re: Dynamic Drafting Spreadsheet
by tally :: Wed, 08/24/2011 - 8:43pm

Are you using the xlsm or xls version and do you have all the pre-draft cells filled out?

30
Re: Dynamic Drafting Spreadsheet
by tamburgy :: Thu, 08/25/2011 - 10:37am

Disregard my previous post - I had downloaded the wrong version. However, when I tried downloading the correct file, a zip file is saved to my desktop. However, when I open the file with WinZip, it gives me a bunch of files and folders (doesn't start the program). Is there something in the zip file that I should click?

Thanks.

31
Re: Dynamic Drafting Spreadsheet
by tally :: Thu, 08/25/2011 - 10:59am

No, there shouldn't be a zip file. The link above should be an Excel file. I just tried it, and it doesn't produce a zip file for me.

33
Re: Dynamic Drafting Spreadsheet
by tamburgy :: Thu, 08/25/2011 - 11:10am

I got it to open, but after I entered all of the pre-draft information, I went to the draft page to put in the players and values.

But,I get "#Value!" in the cell underneath "Enter Current Draft #" on the draft page after doing the pre-draft information.

And everything else below that is blank.

(BTW, I entered 1 next to Enter Current Draft #).

Any advice?

Thanks.

35
Re: Dynamic Drafting Spreadsheet
by tally :: Thu, 08/25/2011 - 1:50pm

Assuming you have entered Current Round #, Number of Teams, and your Draft Position, then it means that your version of Excel cannot interpret the ISODD function. That is something you can access by loading the Analysis Toolpak: http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-H...

36
Re: Dynamic Drafting Spreadsheet
by hialeah007@gmail com :: Fri, 08/26/2011 - 10:50am

I'm confused. Even when I put zero on the number of starters for the positions, it keeps telling me that I should basically have Gates, Witten, Rivers, and some defenses in my top ten. What am I doing wrong?

37
Re: Dynamic Drafting Spreadsheet
by tally :: Fri, 08/26/2011 - 12:09pm

I'm not sure I understand. If you put 0 for number of starters, then why are you inputting those players into the spreadsheet?

Or are you talking about inputting 0 for Estimated Selections? If you input 0, then the highest ranked player at that position will be 0 FPOB and shouldn't be at the top.

38
Re: Dynamic Drafting Spreadsheet
by JRHaggs :: Fri, 08/26/2011 - 2:20pm

Any way this can be adapted to a 16-team league?

39
Re: Dynamic Drafting Spreadsheet
by Dean :: Fri, 08/26/2011 - 2:47pm

Set the number of teams to 16.

40
Re: Dynamic Drafting Spreadsheet
by JRHaggs :: Fri, 08/26/2011 - 5:39pm

Huh. Imagine that.

Cell B9 of Instructions sheet: "This spreadsheet can accommodate leagues of up to 14 teams."

It does appear to work with 16, however.

Thanks.

41
Re: Dynamic Drafting Spreadsheet
by tally :: Fri, 08/26/2011 - 9:43pm

Actually, I think some of the calculations--mostly those estimating need--will be wrong if you input 16 teams. It's something I can add in and correct with a little time though.

42
Re: Dynamic Drafting Spreadsheet
by tally :: Sun, 08/28/2011 - 3:46am

Latest update allows up to 16 teams. Not a difficult thing to do in itself, but trying to get it to work with any number of teams was tricky, so I didn't include that for now.

Excel 97/03: http://www.mediafire.com/?y0xvsbd8cd8f5vw
Excel 07/10: http://www.mediafire.com/?92gvabgagi22b2g

43
Re: Dynamic Drafting Spreadsheet
by JRHaggs :: Sun, 08/28/2011 - 1:52pm

Thanks, tally!

You are a machine!

My 16-team league draft is tonight. Perfect.

44
Re: Dynamic Drafting Spreadsheet
by JRHaggs :: Sun, 08/28/2011 - 10:00pm

Uh oh.

Getting no estimated needs values (#NAME?). Same with FPOBs. As well as "turns until next pick", or whatever.

45
Re: Dynamic Drafting Spreadsheet
by tally :: Sun, 08/28/2011 - 10:30pm

If you're using Excel 2002 or before, you need to install the Analysis Toolpak Add In as described above.

46
Re: Dynamic Drafting Spreadsheet
by JRHaggs :: Mon, 08/29/2011 - 11:39am

Of course, damn it.

The other machine I use has the add-in installed. ...Not this one though. I'm a flake.

The draft didn't go too badly, considering.

Thanks, tally.

47
Re: Dynamic Drafting Spreadsheet
by ktismael :: Wed, 08/31/2011 - 1:52pm

Tally, just wanted to say, I used this for my draft and it worked great! Thanks for the work you put into this.

50
Re: Dynamic Drafting Spreadsheet
by oceankyle :: Thu, 09/01/2011 - 1:52pm

I may be an idiot, but I cannot get the Kubiak list to paste properly into the 07 spreadsheet lol.

51
Re: Dynamic Drafting Spreadsheet
by tally :: Thu, 09/01/2011 - 2:18pm

Make sure to use paste values. Other paste methods may override the filters.

52
Re: Dynamic Drafting Spreadsheet
by oceankyle :: Thu, 09/01/2011 - 2:30pm

Yep I'm a dummy. There isn't a way to use this on google documents is there?

53
Re: Dynamic Drafting Spreadsheet
by tally :: Thu, 09/01/2011 - 2:33pm

Sorry, I didn't vet it for any conflicts with Google Docs, which I'm not too familiar with anyway. Resolving issues between Excel versions was itself already pretty complicated.

54
Re: Dynamic Drafting Spreadsheet
by oceankyle :: Thu, 09/01/2011 - 4:43pm

It's all good, the spreadsheet is awesome! Appreciate it. Thanks

55
Re: Dynamic Drafting Spreadsheet
by jschwets :: Sat, 09/03/2011 - 7:56pm

Nevermind, I figured it out. Awesome spreadsheet!

56
Re: Dynamic Drafting Spreadsheet
by BigTex :: Thu, 08/16/2012 - 10:40am

Is there an updated version of this?

57
Re: Dynamic Drafting Spreadsheet
by Jgr514 :: Sun, 08/19/2012 - 9:45am

When I type in the baseline for QB's, that baseline overrides all the other positions. For example, If I believe that 4 QB's will be taken by my next pick, the #4 ranked players at all positions become the baseline. Anyway to fix this?

58
Re: Dynamic Drafting Spreadsheet
by JRocker (not verified) :: Sun, 08/26/2012 - 7:45pm

Whenever I enter an odd numbered draft round it works fine, but when I enter an even numbered draft round I get "Err:504" in all the FPOB fields. Any idea what's causing this?

59
Re: Dynamic Drafting Spreadsheet
by JRocker (not verified) :: Sun, 08/26/2012 - 7:47pm

Actually, sorry, it's "Err:509"

Login or register to post comments