Fredd's free downloadable draft excel spread sheet

[BrAinPaiNt]

A zoner on the Forum has created a downloadable spread sheet in excel with plenty of information on players for this years draft.

Feel free to download and discuss the information.

Please thank the user for doing so much of the work, putting it all together and making it free for us all to download and share.

The User's name is Fredd and I would like to start off the Thank You mantra by personally thanking him for all of the hard work he has done and for sharing it with us fellow draft nuts. Thanks again FREDD.

The file can be downloaded in spreadsheet format at:
DRAFT SPREAD SHEET

I also added a zipped format of it as well:
DRAFT SPREAD SHEET IN ZIP FORMAT

 

[mickswag]

Thank you Fredd!

 

[ethiostar]

This is completely awesome.

Thank you for all the work you put in to this.

 

[diehard2294]

Sweet! Thanks guys

 

[tm1119]

Yeah this is definitely great. Thanks, I'll probably have this open in front of me while the draft is going on.

 

[Phoenix]

Wow! The time and effort spent on this...boggle! THANK YOU FREDD!

 

[Idgit]

Yeah, I'd recommend everybody check this out. If for no other reason that to see all the impressive work that went into it. Thanks, Fredd.

 

[Fredd]

You guys flatter me, thank you. We are all draft freaks, and I hope this makes your draft experience more enjoyable...there are a million fieatures in the file, so ask away if you have questions.

Thanx!

 

[Parcells4Life]

are these your rankings or did you compile info from scouts? if it is your rankings how did you go about compiling these?

 

[Fredd]

I did the rankings using rankings from 16 different mocks up to March 23rd (most of whom everyone in here would know)...I gave points for 254 for #1, 253 for #2, etc...after I got the info from that, I tallied the points for each player and stacked the players in order of who had the most points. In total, there were 331 players that received points.

doing it this way made this very unbiased from me. For example, I personally have Star as the #3 guy after Joeckel and Fisher. I wouldn't even have one QB in my first round. I would have Ansah somewhere in the 20's.

 

[Muhast]

Thank you so much for this! Especially love the rankings page at the end that has the players/combine stats etc

 

[Muhast]

Is there any excel or printable draft layouts that have the entire draft,round 1-7 with the team drafting, the pick, the trade value and empty spaces to fill in? I had a few last year, but can't seem to find it anymore.

Basically just a spreadsheet I guess that has all the picks per round, with the team who is drafting?

 

[1LoyalCowboyFan]

Thank You kind Sir

 

[Fredd]

Is there any excel or printable draft layouts that have the entire draft,round 1-7 with the team drafting, the pick, the trade value and empty spaces to fill in? I had a few last year, but can't seem to find it anymore.

Basically just a spreadsheet I guess that has all the picks per round, with the team who is drafting?

I used to put that in my spreadsheet but with trades, it was changing too much...I use the ranking page to put the team in and then filter by that team throughout the draft to see what they have picked and look at what they might pick (position)..if you want to see the draft order/postions, then I think that the NFL.com draft site is pretty good...it has every pick, but you can select a team and see what picks they have.

Thank You kind Sir

My pleasure!

 

[Muhast]

One last question, is there any value chart for future picks?

I'm curious bc the Ravens are speculated to want to move up high for Lane Johnson. They have pick 32 this year(590) points. If they trade a first from next year, it is reduced value, but how much? Is it closer to the first few picks of round 2, or a mid 2nd value?

 

[NinePointOh]

I'm confused by the "Value" formula on the Ranking sheet. If the Chiefs take Joeckel 1st overall, (ie. enter a "1"), the value is 800. But if Joeckel goes 2nd, the value goes down to 798? Shouldn't getting the same player with a lower pick mean the team got better value, not worse? I get about the same Value for taking Joeckel with Pick 200 as I get for taking Dion Sims with Pick 1, so I'm unsure what it's telling me.

 

[Fredd]

One last question, is there any value chart for future picks?

I'm curious bc the Ravens are speculated to want to move up high for Lane Johnson. They have pick 32 this year(590) points. If they trade a first from next year, it is reduced value, but how much? Is it closer to the first few picks of round 2, or a mid 2nd value?

that is a great question and one that I had not considered. Of course, there is a value chart in the spreadsheet, but for the following year, there is no way to know the position of the pick until you know how good the trading team is going to be. But, you gave me some good food for thought. I will figure some formula and have that in there for next year.

I'm confused by the "Value" formula on the Ranking sheet. If the Chiefs take Joeckel 1st overall, (ie. enter a "1"), the value is 800. But if Joeckel goes 2nd, the value goes down to 798? Shouldn't getting the same player with a lower pick mean the team got better value, not worse? I get about the same Value for taking Joeckel with Pick 200 as I get for taking Dion Sims with Pick 1, so I'm unsure what it's telling me.

Those that are not Excel nerds like me may want to stop reading here

the way that the value works is on a 1,000 point scale with the following breakdown:
* Up to 250 going to the value of the pick (i.e.: the # where the player is selected - the value decreases with each number and goes down by 1.5625 for each selection)
* Team need: 250 for top need, then 200 for 2nd biggest need, 175, 150, 100, defaults to 50 if not a top 5 need
* Up to 200 going to the overall ranking of that player (goes down by 0.625 for each selection)
* Up to 100 each for offense/defense ranking (value decreases by 0.6024 per rank - there were 166 to grab the "weight" from), positional ranking (drops by 2.564 per rank - 39 in the weights), and functional ranking (i.e.: OL - guards, centers, tackles) - drops by 1.1236 per rank - 89 in the weights);


I am not sure where you get the 800 vs 798. When I select "chiefs" and "1" across from Joeckel's name, I get 925.000 points. If the chiefs traded down to #2 and selected him there, then his value drops a smidgen to 923.438. If, say, the Eagles traded up for him to #1 and picked him, the value to them is 850.000 points.

The formula looks at the following: the team that picks him, the position in the draft where selected, points for team need (ex: joeckel gets a value of
100 pts for the eagles as OT is their 5th most needed item, while the chiefs would get 175 as that is their 3rd most needed position).

Here is a little more of an example. Let's say Joeckel either goes first to KC, 2nd to Jax or 4th to the eagles. The points for each break down as follows:

KC = 925.000 = 175 (3rd highest position of need for them) + 250 for being the #1 overall pick + 200 (overall rank as a player is 200) + 100 (higest Off/Def rank) + 100 (highest position rank for OT) + 100 (highest functional rank for all OL) = 925
Jax = 848.438 = 100 (5th highest position of need) + 248.4375 (being the #2 pick) + 500 (the rest are the same as his rankings didn't change)
Eagles = 845.313 = the value to the eagles is almost identical; everything is the same as jax's with the exception of getting slightly less value as the #4 pick (245.3125), instead of #2 (248.4375)

the formula reads all of the info and automatically assigns the value

Here is another example. Let's look and Cooper and 3 examples. Let's say he goes to one of 3 teams, Dallas @ #18, San Diego at #11, the Cardinals @ #7

Dallas = 952.140 = 250 (highest team need value) + 223.4375 (18th pick value) + 189.375 (18th ranked player on board) + 96.3855 (7th highest Off/Def Rank) + 97.4359 (2nd highest positional rank) + 95.5056 (5th highest functional rank for OL

San Diego = 913.077 = 200 (Team need) + 234.375 (11th pick value) + 478.702 (ranking values are the same as with cowboys)

Cardinals = 769.327 = 50 (team need, if not in top 5 needs, defaults to 50 points) + 240.625 + 478.702 (same ranking values)


So, bottom line is that the value is determined by where the player is selected, the team that selects him (and their need at that position) and the rankings listed for that player. As seen with the Cooper example, the values can change a lot based on the need of the team. I felt team need was important to each selection so I gave it the weight that I did.

Thoughts? questions?

 

[ilovejerry]

not a big draft guy... feel its a waste of time .Gm is gonna pick who they want, but your spreadsheet is sick !!!! very informative thanks for all the hard work

 

[NinePointOh]

Those that are not Excel nerds like me may want to stop reading here

the way that the value works is on a 1,000 point scale with the following breakdown:
* Up to 250 going to the value of the pick (i.e.: the # where the player is selected - the value decreases with each number and goes down by 1.5625 for each selection)
* Team need: 250 for top need, then 200 for 2nd biggest need, 175, 150, 100, defaults to 50 if not a top 5 need
* Up to 200 going to the overall ranking of that player (goes down by 0.625 for each selection)
* Up to 100 each for offense/defense ranking (value decreases by 0.6024 per rank - there were 166 to grab the "weight" from), positional ranking (drops by 2.564 per rank - 39 in the weights), and functional ranking (i.e.: OL - guards, centers, tackles) - drops by 1.1236 per rank - 89 in the weights);


I am not sure where you get the 800 vs 798. When I select "chiefs" and "1" across from Joeckel's name, I get 925.000 points. If the chiefs traded down to #2 and selected him there, then his value drops a smidgen to 923.438. If, say, the Eagles traded up for him to #1 and picked him, the value to them is 850.000 points.

The formula looks at the following: the team that picks him, the position in the draft where selected, points for team need (ex: joeckel gets a value of
100 pts for the eagles as OT is their 5th most needed item, while the chiefs would get 175 as that is their 3rd most needed position).

Here is a little more of an example. Let's say Joeckel either goes first to KC, 2nd to Jax or 4th to the eagles. The points for each break down as follows:

KC = 925.000 = 175 (3rd highest position of need for them) + 250 for being the #1 overall pick + 200 (overall rank as a player is 200) + 100 (higest Off/Def rank) + 100 (highest position rank for OT) + 100 (highest functional rank for all OL) = 925
Jax = 848.438 = 100 (5th highest position of need) + 248.4375 (being the #2 pick) + 500 (the rest are the same as his rankings didn't change)
Eagles = 845.313 = the value to the eagles is almost identical; everything is the same as jax's with the exception of getting slightly less value as the #4 pick (245.3125), instead of #2 (248.4375)

the formula reads all of the info and automatically assigns the value

Here is another example. Let's look and Cooper and 3 examples. Let's say he goes to one of 3 teams, Dallas @ #18, San Diego at #11, the Cardinals @ #7

Dallas = 952.140 = 250 (highest team need value) + 223.4375 (18th pick value) + 189.375 (18th ranked player on board) + 96.3855 (7th highest Off/Def Rank) + 97.4359 (2nd highest positional rank) + 95.5056 (5th highest functional rank for OL

San Diego = 913.077 = 200 (Team need) + 234.375 (11th pick value) + 478.702 (ranking values are the same as with cowboys)

Cardinals = 769.327 = 50 (team need, if not in top 5 needs, defaults to 50 points) + 240.625 + 478.702 (same ranking values)


So, bottom line is that the value is determined by where the player is selected, the team that selects him (and their need at that position) and the rankings listed for that player. As seen with the Cooper example, the values can change a lot based on the need of the team. I felt team need was important to each selection so I gave it the weight that I did.

Thoughts? questions?

Okay, I didn't realize the team mattered, so I didn't actually have the Chiefs selected -- I had the Broncos. So that explains why I got 800 and you got 925 for Joeckel.

I would have measured Value a bit differently (ie. getting the same player with a lower pick should mean greater value; and I personally don't think the teams' ranked needs are precise or objective enough to justify the points), but that's easy for me to say because you put in all the hours and I didn't. And frankly, the sheet is fantastic overall.

I'll just make my own manual tweaks and go cook some jalapeno poppers.

 

[Fredd]

Okay, I didn't realize the team mattered, so I didn't actually have the Chiefs selected -- I had the Broncos. So that explains why I got 800 and you got 925 for Joeckel.

I would have measured Value a bit differently (ie. getting the same player with a lower pick should mean greater value; and I personally don't think the teams' ranked needs are precise or objective enough to justify the points), but that's easy for me to say because you put in all the hours and I didn't. And frankly, the sheet is fantastic overall.

I'll just make my own manual tweaks and go cook some jalapeno poppers.

thanx. I originally had a calulation in there much like you are saying, but then I thought the rankings would tell me the value of the player better...there is a lot of subjectivity in the point system and it is really there just to add a little flavor, but I did the rankings with no homerism as I took them based on 16 different mocks at the time...things have shifted in the last few weeks, so it isn't a perfect science any more...maybe if the Cowboys wanted to hire me and I could take a month off from real work, then I would be able to keep up with the moves ....I was actually going to do that the other day, but I just ran out of time.

I am going to see what I can do for next year to make this better so any suggestions, please send them my way.