John Maddens Lunchbox
Socialism for Dummies
mmm T-Bone
Fantasy Football - Footballguys Forums
Welcome to Our Forums. Once you've registered and logged in, you're primed to talk football, among other topics, with the sharpest and most experienced fantasy players on the internet.
ADP Averaged from 4 sites (download) (1 Viewer)
- Thread starter Tbone
- Start date
shining path
:prettyplease:
Very nice work, Tbone.Triangulator
Footballguy
Quality stuff Tbone! Thanks for sharing your hard work.
Mario Kart
Footballguy
all of this data is junk now because of vick's injury. argh.just kidding
The really funny thing about the ESPN leagues is how the scoring system they use undervalues QB's. Have you tried plugging in the ESPN scoring system into VBD or DD and see what it spits out? The first QB (McNabb) doesn't show up until pick 31, with 27 of the first 30 picks being RBs. A WR doesn't show up until 21.MarquisWow, you aren't kiddin. They love them some QBs over there, dont' they?Maybe we should all go join an ESPN money league?
TBone....I'll jump on the kudos bandwagon too....awesome job, thanks!!!One question and 2 comments:First the question:What is the P# column supposed to represent? I thought it was the rank at their position (in your spreadsheet, as opposed to some other ranking), but the data doesn't seem to indicate that. For example, why does Ahman Green have a "2" here. He's listed at #11 overall (Col A), and is the 10th RB listed. I would have expected to see a "10" in Col E (P#). Whazzup wit dat?And now for the two comments that would be nice additions:- Add a Freeze to the spreadsheet to keep the column headers always visible. I'm sure you know how to do this, but just in case....Click on Cell B2 ("Tomlinson") and select Window->Freeze Panes.- Add a simple position filter to view only the players of a given position. Again, just in case....to do this, select the POS column (D), and choose Data->Filter->Auto Filter from the menu. Voila!, you can now filter the whole sheet by each position (resizing the size of Col D to accommodate the pulldown is also ncessary.Again, awesome tool! ThanksMarquis
Tbone
Shrinkage
I didn't realize that. It kind of surprises me because I think it's uses a flex like this:1QB1RB2WR1Flex (RB or WR)1TE1PK1DTThis would lead me to believe that RB's wouldn't be quite as valuable since you MUST start only one. The QB thing is just plain weird though. Taking a QB too early is a typical rookie move, and there's a lot of first time fantasy football players on the ESPN site. That'd be my guess as to the reason.The really funny thing about the ESPN leagues is how the scoring system they use undervalues QB's. Have you tried plugging in the ESPN scoring system into VBD or DD and see what it spits out? The first QB (McNabb) doesn't show up until pick 31, with 27 of the first 30 picks being RBs. A WR doesn't show up until 21.MarquisWow, you aren't kiddin. They love them some QBs over there, dont' they?Maybe we should all go join an ESPN money league?
Opps, mea culpa.....I had messed up the starter criteria (had 2 starting RBs instead of 1). That changes things drastically.I didn't realize that. It kind of surprises me because I think it's uses a flex like this:1QB1RB2WR1Flex (RB or WR)1TE1PK1DTThis would lead me to believe that RB's wouldn't be quite as valuable since you MUST start only one. The QB thing is just plain weird though. Taking a QB too early is a typical rookie move, and there's a lot of first time fantasy football players on the ESPN site. That'd be my guess as to the reason.The really funny thing about the ESPN leagues is how the scoring system they use undervalues QB's. Have you tried plugging in the ESPN scoring system into VBD or DD and see what it spits out? The first QB (McNabb) doesn't show up until pick 31, with 27 of the first 30 picks being RBs. A WR doesn't show up until 21.MarquisWow, you aren't kiddin. They love them some QBs over there, dont' they?Maybe we should all go join an ESPN money league?
Marquis
Tbone
Shrinkage
To answer your question. I messed up on the P# (positional ranking). I have fixed it and reposted.As to your two suggestions, they are excellent and I've incorporated them. These are the type of suggestions I was looking for in my earlier post where I asked if anyone had any formatting ideas. Thanks marquis!
I will add a little note on my header post as to these changes.
Tbone
Shrinkage
You may have the first version before I added kickers and the other changes. There was an error in my first posting of 2.0 and I have corrected it as well as made two small formatting changes as per marquis suggestions. I will always edit my header post on this thread as to any updates/changes that I make.
Tbone
Shrinkage
Unfortunately it does not update automatically. I do it manually, but it's likely I will be updating at least once or twice before the 28th. If you check back at the first post in this thread once in a while you'll see when I've posted any updates.By the way, that's one scary avatar JH.
Last edited by a moderator:
PackAttack
Footballguy
Nice Work TBone!! A+ for effort. 
Nemesis
Footballguy
Nice work Tbone.
Suggestions:
ON the "information" tab........Might want to put a link to the website hosting the spreadsheet so that people can save the Excel spreadsheet and then weeks, months, or next yr, people can easily go BACK into the spreadsheet they saved to their hard drive and find the link if this becomes a regular "thing" rather than search FBGs for the info.
Suggestions for the users.........
You can get the entire 192 overall ADP to fit on one page (legal) if you know how to work Excel by copying and pasting and making the players go down both sides of the page. (players 1-100 down the left side and players 101-192 down the right side) I think you'll find this much easier than having to flip thru 4 pages at your draft or printing out a page for each position.
-Before you start to do this, I would also copy the ADP tab to a new tab (so that you keep the original data intact)
If someone needs help with this, let me know since I've already done this.
Suggestions:
ON the "information" tab........Might want to put a link to the website hosting the spreadsheet so that people can save the Excel spreadsheet and then weeks, months, or next yr, people can easily go BACK into the spreadsheet they saved to their hard drive and find the link if this becomes a regular "thing" rather than search FBGs for the info.
Suggestions for the users.........
You can get the entire 192 overall ADP to fit on one page (legal) if you know how to work Excel by copying and pasting and making the players go down both sides of the page. (players 1-100 down the left side and players 101-192 down the right side) I think you'll find this much easier than having to flip thru 4 pages at your draft or printing out a page for each position.
-Before you start to do this, I would also copy the ADP tab to a new tab (so that you keep the original data intact)
If someone needs help with this, let me know since I've already done this.
Green Lantern
Footballguy
Excellent Work Tbone. I actually combined your ADP information with VDB app 2003j so that there is an ADP column on the "Cheat" worksheet in the VBD app right next to the "Value" column for the top 60 players. I will attempt to explain how I did this and if you have any questions, I will check back.
1. Open your VDB app.
2. Open Tbone's spreadsheet in Excel.
3. While in Tbone's spreadsheet go to the "AVG" worksheet. On the menu bar, click edit/move or copy sheet. In the "To Book" field, select the file name of your VDB app. In the "Before Sheet" field, select "(move to end)" Check the "Create a copy" box. Click ok.
4. You should now have the "AVG" worksheet as the last worksheet in your VDB app. Go to this worksheet and change the names in column B to the players full name to match their name on the VDB "Cheat" page (e.g., change Tomlinson to LaDainian Tomlinson).
Note to Tbone. If you want to change this in your next update it would also make it easier to update the VBD apps, if anyone else thinks this idea has any value.
5. Delete Column C (players first names)
6. I deleted the ESPN column to get rid of the QB skew. If you don't delete the ESPN column, the fomula in #7 needs to be adjusted.
7. In column W of the "Cheat" worksheet in the VDB app type the following formula: =VLOOKUP(R3,AVG!$B$2:$I$193,8,FALSE). If you did not delete the ESPN column, the formula should use a 9 in front of false instead of an 8 (this number refers to the column to pull the ADP from. If you deleted the ESPN column, it is 8, if not it is 9.
8. Copy this formula for players 1-60.
9. If you have any errors, check the spellings of the players names.
Hopefully this will work for you. I find it helpful during the draft to be able to look at where I think a guy should be drafted and where he is usually drafted.
1. Open your VDB app.
2. Open Tbone's spreadsheet in Excel.
3. While in Tbone's spreadsheet go to the "AVG" worksheet. On the menu bar, click edit/move or copy sheet. In the "To Book" field, select the file name of your VDB app. In the "Before Sheet" field, select "(move to end)" Check the "Create a copy" box. Click ok.
4. You should now have the "AVG" worksheet as the last worksheet in your VDB app. Go to this worksheet and change the names in column B to the players full name to match their name on the VDB "Cheat" page (e.g., change Tomlinson to LaDainian Tomlinson).
Note to Tbone. If you want to change this in your next update it would also make it easier to update the VBD apps, if anyone else thinks this idea has any value.
5. Delete Column C (players first names)
6. I deleted the ESPN column to get rid of the QB skew. If you don't delete the ESPN column, the fomula in #7 needs to be adjusted.
7. In column W of the "Cheat" worksheet in the VDB app type the following formula: =VLOOKUP(R3,AVG!$B$2:$I$193,8,FALSE). If you did not delete the ESPN column, the formula should use a 9 in front of false instead of an 8 (this number refers to the column to pull the ADP from. If you deleted the ESPN column, it is 8, if not it is 9.
8. Copy this formula for players 1-60.
9. If you have any errors, check the spellings of the players names.
Hopefully this will work for you. I find it helpful during the draft to be able to look at where I think a guy should be drafted and where he is usually drafted.
Last edited by a moderator:
Tbone
Shrinkage
I tried this, it worked great. This is just the type of stuff I was hoping to hear from these boards. I will follow the full name format in the next update as you suggested.
I forgot about using the vlookup function. It will also make my updates go quicker. I won't even explain the way I was doing them before. It was creative, but too labor intensive. Anyway, I'll likely use the vlookup function to add the bye week info on the next update as well.
Green Lantern
Footballguy
I did the ADP on the cheatsheets last year manually and it was a pain because everytime the VBD app got updated, I had to change the ADP. Now it is automatic. Thanks for putting together the ADP Tbone, that helps make some choices easier.
LordHusker
Hot sauce addict
Great work, TBone.I just downloaded version 2.2. Awesome.However, I tried to do what Green Lantern suggested and I'm stuck. I don't get step 7: If I try to paste that formula into the W column of VDP's Cheat, it's way too long to even fit. I don't quite understand what it's supposed to be doing, either.Should Green Lantern's instructions be updated along with your 2.2 adjustments, TBone? I love the idea of having your ADP goods right on the VBD application. But honestly, I can't get it to work because I'm not that familiar with Excel.Maybe I'm a dimwit ...
LordHusker
Hot sauce addict
OK, I'm definitely a dimwit. I made the cell longer so I could fit the formula, but it's not working. It errors out as soon as I click onto another cell (aka the second player). Hmmm.I honestly think another run through those directions would help us, Green Lantern. Thx.
Super Hogs
Footballguy
Tbone, nice work, thanks! Anyway to devote a column to tiers (i.e. 1st 4 or 5 RBs would be Tier 1 of RBs; 6th - 11th would be Tier 2 of RBs, etc....)?? I find Tier information a great tool to have when drafting under presure. Thanks againSH
Tbone
Shrinkage
I too use tiers to put things into perspective for the draft, however I'm not sure how I'd incorporate it into this. Since tiers are based on someone's individual projections for each player and the ADP only shows the order in which players are typically being drafted, you wouldn't have your tiers in the order which YOU would take them.You could however add a column and manually type in what "tier" you would personally put each player into, and that would probably be helpful during your draft.Unless I'm somehow missing exactly what it is you had in mind? If so, maybe you could post an example of what it would end up looking like?Myself, I use a the footballguys VBD spreadsheet and modify it so that I can break each of the seperate player columns into my personal tiers. And then I have the ADP sheet (from this thread) next to me to refer to as a tool to see when specific players may or may not be available to me.But that's just how I use it. I'm sure there are a number of clever ways that the sharks on these boards have found to use the ADP sheets for their drafts as it relates to tiers ???Anyone ?
superbeck1
Footballguy
This is some great work T. Great work. Thanks for sharing with us. 
Green Lantern
Footballguy
LordHusker,I will try to explain how the formula works and hopefully that will help you. The formula is designed to lookup a players name in the overall top 60 section of the "Cheat" worksheet, then go to the "AVG" worksheet and find a match for that players name and place the players ADP on the "Cheat" worksheet. This R3 in the formula is the cell reference on the "Cheat" worksheet that contains the players name. The "AVG!$B$2:$I$193" is the range (array) where the formula finds a match for the player name on the "Cheat" worksheet. Note that this range does not include column titles or the # column, just the data. The "8" is the number of the column in the range (arrary) that the formula pulls the # from after it finds a match for the player's name. With the new 2.2 version of the Tbone's spreadsheet after deleting the ESPN column, column I is the 8th column. "FALSE" just allows the formula to look up data that is not sorted in ascending order.Here are a couple of things to look forDo the players names on the "AVG" worksheet match the names on the "Cheat" worksheet?Is the column number in the formula (i.e., 8) referencing the "AVG" column?Let me know if you need additional assistance
Tbone
Shrinkage
First off, you're not a dimwit. This procedure is not exactly Excel 101, but it can be done. I think the instructions would be a little different as of the most recent update (Version 2.2). One of the reasons that it has changed is that I have changed the player column from 2 to only 1 combining the players first and last names. So with this in mind, here are the updated instructions to do what Green Lantern is suggesting (I left out the part about removing ESPN as that is a judgement call that you'd have to make on your own, myself I think it helps to have it left in as most leagues have at least one or two "guppies" and ESPN seems to represent this group the best). Ok, again here we go:1. Open your VDB app.2. Open Version 2.2 of the ADP spreadsheet in Excel.3. While in ADP the spreadsheet go to the "AVG" worksheet. On the menu bar, click edit/move or copy sheet. In the "To Book" field, select the file name of your VDB app. In the "Before Sheet" field, select "(move to end)" Check the "Create a copy" box. Click ok.4. You should now have the "AVG" worksheet as the last worksheet in your VDB app. 5. In cell W3 of the "Cheat" worksheet in the VDB app type the following formula: =VLOOKUP(R3,AVG!$B$2:$J$193,9,FALSE)6. Copy and past this cell to cells W4 to W62 (the top 60 spots).7. I can't seem to get it to take the defensive teams data (I hope to figure out why) but in the mean time there are usually only a few in the first 60 players on your VBD sheet anyway, so you can just manually type in their ADP average.8. I then format the column so that it only shows one decimal (example - 17.3 for Randy Moss). 9. You will also need to adjust the print area (footballguys have the spreadsheet formatted so only their part prints). To do this left click on cell B1 of the VBD "cheat" page. Holding the mouse button down drag your mouse down to cell W62 to highlight everything that you want to showup when you print. Now that it is all highlighted left click on File - Print Area - Set Print Area. If you do a print preview, the ADP column should now show up on the far right.That's it. If you are still having trouble, I'd be glad to email you my VBD spreadsheet to look at, you'd just have to edit the league scoring for your league. But at least you'd see what it should look like. This is just something that Green Lantern found to be helpful, as did I, but I think the ADP pages printed as seperate sheets will be the way to go for most of the casual Excel users.On the other hand, maybe the guys here at FBG would want to incorporate a column like this into the original VBD spreadsheet. They already have ADP data put into it (they use it for Draft Dominator, it's hidden way out to the right in column AZ of the individual position sheets). Maybe they'd like to use the averaged ADP information from my ADP-AVG spreadsheet to do something like this ? Joe ? David?
Chaka
Footballguy
Clearly you went through a lot of trouble and it is a good sheet but...It seems that you simply averaged the four averages giving equal weight to each average draft. But each average draft is composed of a different number of contributing drafts (eg ANT Sports has 50 drafts contributing but ESPN may have 200). If the discrepancies between contributors is large this will certainly skew your results in favor of one sites results.Sorry to be the only downer in the post.
Tbone
Shrinkage
Chaka,Point well taken. And I would agree that the average is far from perfect. My thoughts on putting this together were that seperately each of the sites that offer ADP information tend to be scewed a little too far one way or the other. At ESPN, the QB's and players with higher name recognition tend to go very early. Xpertleagues has the RB's going much earlier than a lot of leagues (if that's even possible). Antsports is an average of not very many drafts, and MFL is a hodge-podge of all sorts of different leagues. So if you pick any one of those that is similar to your league situation then that's great (in other words if you're in an ESPN league, go ahead and use their ADP, if you're in a survivor style league you'd probably be best off using the Xpertleagues ADP, etc.).However, a lot of leagues, especially private leagues, don't really follow any ONE of these ADP formats too closely (my league for one). And it got me thinking of how I could come up with ADP data that might better represent those types of leagues. About 2 years ago I started averaging whatever ADP sites I could find thinking that it might mirror my league a little more closely (we have a pretty good mix of drafting styles that take place in my league). What I found is that the averaged ADP list tended to represent my leagues draft a little better than any one of the individual pages when I compared them after the draft.And so I came up with the average list and decided to post it if anyone else thought it might have value to them as well. It likely has value for some and for others it likely does not.But it's available to you if you think you'd like to check it out.
Last edited by a moderator:
Is there a chance that the process of using the latest/greatest results from all four sites could be semi-automated via Excel 2002's "Data > Import External Data > New Web Query..." feature?Theoretically, once the four web queries were set up, you'd just need to use the "Data > Refresh Data" option to re-load the "raw" data from each of the four sites, and, your local spreadsheet would be automagically updated.
Tbone
Shrinkage
It wasn't covered. An interesting idea, have to think about that one. One thing that comes to mind is that the magazine info was written several months ago. It won't be changing based on events that occur during pre-season. Also it is not an average draft, but rather rankings.The ADP reflects current actual drafts (and mocks), which is a little different than rankings. It is also dynamic and will change to reflect actual events (injuries, training camp battles, etc.)However, there are a certain number of people out there (god bless them) that buy a magazine and show up for a draft and use it as their cheat sheet, so maybe factoring them into an ADP average has merit.I'll have to think about that one some more. I'd be interested to hear what others think about this idea. In the mean time, it'd be pretty easy to add a column into the spreadsheet on your own and manually enter the data from the FBG article.
I thought about that right after I typed it. So what I did was go back and compare those rankings with your ADP list. For QB's it's almost identical (scary)Same thing for RB's except for Canidate was underrated in the mags compared to ADP (7 spots).WR's are a little more spread out (but not that much.) I stopped there. Maybe just an extra column that displays the magazine ranking subtracted from the ADP position ranking. You could see where the variance is? Any thoughts here?
Jason Wood
Zoo York
Fantastic effort!Cheers
