Jump to content

Visit Rotoworld.comRotoworld Forums  
Rotoworld: MLB | NFL | NBA | NHL | NASCAR | CFB
Sports Talk Blogs: PFT | HBT | PBT | CFT | PHT
  Visit NBCSports.com

Photo
- - - - -

2013 Auction Tools


This topic has been archived. This means that you cannot reply to this topic.
73 replies to this topic

#1 ZidaneValor

ZidaneValor

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 1,927 posts

Posted 06 July 2013 - 09:50 PM

Introduction

Last year, I experimented with calculating the auction values/position rankings for fantasy football players.  I developed a series of calculations and spreadsheets which I found to be quite helpful during my drafts.  This year, I have a series of adjustments to my calculations based on my experiences last year.

The biggest changes that I have made for this year:

1) I customized my projections a bit more.  Last year, I simply took the averages of several prominent fantasy football websites like Rotoworld, Yahoo/Accuscore, ESPN, CBS Sports, et cetera.  This year, I used a weighted three-year average formula and then went through each player with a fine-tooth comb and adjusted each projection up and down based on new circumstances like new team or gaining/loss weapons.

2) My biggest mistake last year was not factoring in supply and demand.  When using straight VBD, you figure out the baselines based on the number of starting players.  However, in most leagues, players will not trade the 20th-best RB for the 20th-best WR.  So in response, I set the RB baseline much lower.

The Excel XLSX Workbook

This year, I thought I would make it much easier for you guys to modify the values/rankings for your own leagues.

First things first, here is the base template.  It is an Excel Workbook.

http://heartless-ang...2013-07-06.xlsx

A quick guide: Each Excel sheet has instructions that you can follow to adjust the values to fit your own needs.  However, I will take some time in this post to explain what each spreadsheet does.

"Projections" Spreadsheet

You can adjust the individual projections in the "Projections" spreadsheet.  If you disagree that Tom Brady will only get 4200 passing yards or Adrian Peterson will only get 35 catches, make your changes there.

You can adjust the projections like yards, TDs, touches, et cetera, but you can also make adjustments based on 'Yards per Rush' (YPR) or 'Yards per Catch' (YPC).  The rushing yards and receiving yards are computed by default by multiplying the attempts by the yards per attempt (YPR/YPC).  You can edit the yards manually or you can change the attempts and/or the YPR/YPC to get new values.

If you want to drop a player’s stats across the board, change the percentage (Column D).  The percentage will not change the YPR or YPC.  So if you think for some reason, Adrian Peterson will miss 2 games, you can set his percentage to 87.5%.  If you think Tom Brady’s stats will go down by 5% due to the loss of some weapons, set his percentage to 95%.

Important Note:  If you look at the numbers in each cell, you’ll notice that each number has a formula that is essentially ‘=Stat*$E#’.  If you edit that cell and take out the *$E#, then if you go back and adjust the percentage, that cell will not be updated.  So if you are going to change Tom Brady’s passing yards, it’s recommended that you change the formula from ‘=4200*$E7’ to ‘=4500*$E7’ just for the sake of consistency.

"Scoring System" Spreadsheet

You can set up your league's scoring system in the "Scoring System" page.

For example, the total projected points for ESPN standard-scoring fractional leagues are found with this formula:

=($E2/25)+($F2*4)+(($I2+$L2)/10)+(($J2+$M2)*6)+($K2*0)+(($G2+$N2)*-2)

The formula is, in order, passing yards are 25 per point, passing TDs are 4 points each, rushing and receiving yards are 10 per point, rushing and receiving TDs are 6 points each, receptions are worth 0 points, and turnovers are worth negative-two points.

Change the formula to meet the scoring criteria of each of your leagues, with one column per league.  I have set up four scoring systems so far: ESPN standard scoring, ESPN 1-PPR scoring, a league with return yards & passing/rushing/receiving bonuses scoring (CUSTOM1), and another scoring system for a friend (CUSTOM2).  Copy the base formula from cell U2 or V2 into Row 2 of a new column, adjust the fractions and TD points, and then copy that formula all the way down the column.  The title of the column (Row 1) is what will be used to find the player rankings on the “auction values” page.

CUSTOM1 in Column W is an example of a league that scores return yards, return TDs, and bonuses.

Astute observers will notice that I could have combined the “Projections” spreadsheet and the “Scoring Settings” spreadsheet.  However, I separated them in this workbook because I thought it would be easier to follow.

"Auction Values" Spreadsheet

This is the hardest spreadsheet to explain, but also the most valuable as it is the one that holds all the values/rankings for the players.  I will try to do the best I can with this explanation.

First, you'll find the auction values pages each have long complicated names like "10TM 1Q 2R 2W 1T 1WRT ESPN-STD".  That's because I wanted to clarify what the league roster configurations were when I found my values.

I did my best to put all of the values that you would have to customize in a light-orange color.  There are detailed instructions starting in Row 28-29.

As you may have guessed, this spreadsheet finds the 'Auction Values' for each player.  If you are in a serpentine, just list the players from most expensive to least expensive.  In the event of a tie, use the number of points in one of the three columns to the left of the players, with the column in the left taking precedence to the middle column, which takes precedence to the end column.

Step #1: Type the column name of the league scoring settings that you created in the "Scoring System" spreadsheet that want to use in the merged-cell B9:C9.  In the first sample, the column name is 'ESPN STD', which is the same as cell U1 of the "Scoring System" spreadsheet.  A column number between 20 and 30 inclusive will be shown in cell D9 of the "Auction Values" sheet automatically.  Do not change this number, as it is needed for the VLOOKUP formula.

Step #2: Enter the correct number of teams in cell D8.

Step #3: If you are in an auction draft where everyone starts with a number other than $200, enter it in the D10 formula ‘=(D8*200)-50’.  If you believe the players in your league will spend an amount other than $50 on all kickers and defenses combined, edit that part of the formula.  If you are in a snake draft, don't edit that cell.

Step #4: Highlight the player name and project score columns for one position, and sort from largest to smallest.  Do this for each position separately.  For example, highlight columns K&L and then sort by L.  Do the same for columns Q&R, W&X, and AC&AD.

Step #5: This is the trickiest part.  Determine the base cutoff values for each position and list them in cells B2 through E4.  The easiest way to do this is by setting the PREMIUM to the cell below the number of starting spots, the BENCH to a good amount of bench players (one below the last good bench player), and the FLIERS to the $1 or late-round players (the value set to the first player you don't think will be drafted).  So in a 10-team 1QB, 2RB, 2WR, 1TE league, set cell B2 to a value of '=L12', which is one spot below the 10th ranked QB, set C2 to '=R22', one spot below the 20th ranked RB, and so forth, set D2 to a value of '=X22', and set E2 to a value of '=AD12'.  As you will see, I have not done that in this example.  If I feel that the true 'cutoff' is higher or lower due to supply and demand, I will set the value to that spot.  So even though this is a 10-team league, I set the PREMIUM value to '=L10' because I feel Stafford is the cutoff there.  I set the REGULAR value to ='L14' because I feel there is a big drop-off from Romo to Freeman.

The values for Step #5 are all a matter of preference and projected 'tiers'.  They're really a best guess.  If you are one higher or one lower than me, that won't affect your values too much.

Step #6: Type in the number found in B6 into B5.  Do the same for C6 into C5, D6 into D5, and E6 into E5.  These are the 'base salaries' for each position.  They have to be typed in manually because of 'Excel Circular Reference' errors when calculating each player's total value.

At this point, you will have each player’s auction value for that specific league.  If it’s a serpentine, just take the players from highest to lowest.  Even in a serpentine, this could be a good tool to use when proposing and evaluating trades.

It's complicated at first, but once you do it a time or two, you should get the hang of it.

A Note about the Auction Values

Note that these are just season-long projections.  They may not represent the order in which I would draft players, especially bench players.  When it comes to the RB31-RB60 for example, a guy that scores 80 total points doing it 4-6 points per game is going to have less value than someone that scores 70 points, but scores 15 points in each of 4 games because the starter in front of him went down (otherwise known as a handcuff.)  That's why in the middle-to-late rounds, I'll go against my own projection, and rank guys based on upside, or in other words, a projection of what they could do if they got the starting position instead of a projection of their total season.  Keep that in mind.

Conclusion

So that's it.  I hope that you find this information useful and easy to customize to fit your own needs.

If you have any questions, feel free to ask me, either through PM or this thread.

Thanks.

#2 Theoneupper

Theoneupper

    On the Ballot

  • Established Members
  • PipPipPipPipPipPipPipPip
  • 4,849 posts

Posted 06 July 2013 - 09:59 PM

Loved it last year! Good stuff Zidane.

#3 Proteus

Proteus

    Hall of Famer

  • Established Members
  • PipPipPipPipPipPipPipPipPip
  • 5,539 posts

Posted 06 July 2013 - 11:31 PM

Are you from the future?
QB: Ryan Leaf
RB: Lawrence Phillips
RB: Ki-Jana Carter
WR: Charles Rogers
WR: Desmond Howard
TE: David LaFleur
W/R: David Wilson
K: Ray Finkle(Einhorn)
D: Minnie

BN: Andre Ware/Jared Cook/Tony Mandarich/Curtis Enis/Travis Henry/Brian Bosworth/CJ Spiller/Trent Richardson

facebook.com/WhyYouBeasting

#4 ZidaneValor

ZidaneValor

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 1,927 posts

Posted 07 July 2013 - 05:37 PM

View PostProteus, on 06 July 2013 - 11:31 PM, said:

Are you from the future?

Yes, indeed I am.

Anyone have any questions or need something explained a little better?

#5 gordo4s

gordo4s

    Allstar

  • Established Members
  • PipPipPipPipPipPip
  • 866 posts

Posted 07 July 2013 - 07:15 PM

This is great! Thank you!!

I play in a league where quarterbacks get six points for passing touchdowns and there are no points  for reception, so it's great to be able to adjust for scoring, too.
12 Team Auction Keeper - 6 point passing TDs, 0 ppr

QB: Drew Brees, Russell Wilson
RB: Marshawn Lynch
RB: Chris Johnson
FLEX: Joique Bell, Jeremy Hill, Shonn Greene, Bernard Pierce, Lorenzo Taliaferro, Jerrick McKinnon
WR: Davante Adams
WR: Andrew Hawkins, Jordan Matthews
TE: Martellus Bennett
DST: Patriots
K: Cody Parkey

#6 RespectMyAuthority

RespectMyAuthority

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 2,156 posts

Posted 07 July 2013 - 08:54 PM

View Postgordo4s, on 07 July 2013 - 07:15 PM, said:

This is great! Thank you!!

I play in a league where quarterbacks get six points for passing touchdowns and there are no points  for reception, so it's great to be able to adjust for scoring, too.

Don't read much do you?  He clearly details how to change the scoring, all you need is a remedial understanding of Excel to know how to do it.

#7 jtaylor39

jtaylor39

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 2,415 posts

Posted 07 July 2013 - 08:57 PM

View PostRespectMyAuthority, on 07 July 2013 - 08:54 PM, said:

View Postgordo4s, on 07 July 2013 - 07:15 PM, said:

This is great! Thank you!!

I play in a league where quarterbacks get six points for passing touchdowns and there are no points  for reception, so it's great to be able to adjust for scoring, too.

Don't read much do you?  He clearly details how to change the scoring, all you need is a remedial understanding of Excel to know how to do it.

Do you read much?
NABL 12 team ppr $$ League
2011 Champion
2012 3rd Place
2013 2nd Place


FFAH League
2009, 2010, 2011 Champion

I cash checks.

#8 ZidaneValor

ZidaneValor

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 1,927 posts

Posted 07 July 2013 - 08:57 PM

View PostRespectMyAuthority, on 07 July 2013 - 08:54 PM, said:

Don't read much do you?  He clearly details how to change the scoring, all you need is a remedial understanding of Excel to know how to do it.

I don't think you read gordo4s correctly.  He/she was happy that the scoring on the spreadsheet could be changed.

#9 RespectMyAuthority

RespectMyAuthority

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 2,156 posts

Posted 07 July 2013 - 08:59 PM

View Postjtaylor39, on 07 July 2013 - 08:57 PM, said:

View PostRespectMyAuthority, on 07 July 2013 - 08:54 PM, said:

View Postgordo4s, on 07 July 2013 - 07:15 PM, said:

This is great! Thank you!!

I play in a league where quarterbacks get six points for passing touchdowns and there are no points  for reception, so it's great to be able to adjust for scoring, too.

Don't read much do you?  He clearly details how to change the scoring, all you need is a remedial understanding of Excel to know how to do it.

Do you read much?

Touche,  I apologize, I saw a "d" instead of an "s",

I read it as "It'd be great", instead of "It's great"



Awesome work Z.

#10 chriscarman

chriscarman

    Double-A

  • Members
  • PipPip
  • 30 posts

Posted 08 July 2013 - 12:47 AM

This is awesome, thank you for putting it together and sharing. My question is regarding the auction values. I was using my leagues auction results from last year as a rough guide to values. I hope I'm not being too nitpicky about this or naive to the subtle differences between leagues, I've only been playing an auction league for a couple years and I'm still trying to figure out the best way to value players. The only other websites I've used (last year) to value players were FFToolbox and ESPN, and I during the draft, I realized that those were off as well. My league's settings are ESPN standard with a 200 budget, so all I did was change the # of teams and looked at the differences spit out by Excel. After doing that, I noticed that, compared to the spreadsheet, in my league...

QBs are valued higher at the top of the sample
RBs are valued WAY lower all the way through the sample
WRs are valued slightly higher at the top and considerably higher the farther down I go
TEs are valued higher for everyone except the #1


QB1, QB5, QB10, QB15, QB20 10 Team Standard 38, 16, 6, 0, 0
QB1, QB5, QB10, QB15, QB20 14 Team Standard 58, 23, 6, 1, 0
QB1, QB5, QB10, QB15, QB20 14 Team Last Year   67, 39, 12, 3, 1

RB1, RB5, RB10, RB15, RB20 10 Team Standard   66, 59, 43, 35, 30
RB1, RB5, RB10, RB15, RB20 14 Team Standard 101, 90, 65, 51, 44
RB1, RB5, RB10, RB15, RB20 14 Team Last Year 72, 52, 44, 34, 18

WR1, WR5, WR10, WR15, WR20 10 Team Standard   40, 21, 19, 10, 4
WR1, WR5, WR10, WR15, WR20 14 Team Standard   62, 31, 28, 13, 4
WR1, WR5, WR10, WR15, WR20 14 Team Last Year 68, 35, 30, 28, 23

TE1, TE5, TE10, TE15, TE20 10 Team Standard    33, 8, 2, 1, 0
TE1, TE5, TE10, TE15, TE20 14 Team Standard    52, 11, 2, 1, 0
TE1, TE5, TE10, TE15, TE20 14 Team Last Year      35, 18, 6, 4, 2

My other question is more general, why do values go up the more teams there are in the league?

Thanks to anyone who answers, I came across this board a couple months ago and it is examples like this that clearly make it the best source of info for fantasy. I only wish I'd found it sooner....

Edited by chriscarman, 08 July 2013 - 12:52 AM.


#11 ZidaneValor

ZidaneValor

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 1,927 posts

Posted 08 July 2013 - 05:51 AM

View Postchriscarman, on 08 July 2013 - 12:47 AM, said:

My league's settings are ESPN standard with a 200 budget, so all I did was change the # of teams and looked at the differences spit out by Excel.


My other question is more general, why do values go up the more teams there are in the league?

Ah, I didn't explain myself well.

If you look at Cell D18 of the "Auction Values" page, it says the number of players currently in the pool.  This number should be the number of teams * the number of roster spots.

So if you change the number of teams from 10 to 14 for example, you also have to adjust the baselines so that enough players are included in the pool.  So if a 10 team league with 15 roster spots, you need cell D18 to be 150 players.  If you have a 14 team league with 16 roster spots, cell D18 needs to be 224 players.

The reason all your player values went up is because by increasing the number of teams, all that did was increase the total pool of money.  Now that extra money is being divided up among the same group of players, so all their values went up.

The way I would recommend doing this is to adjust the FLIER baselines on cells B4, C4, D4, and E4 first.  Lower those baselines until you get the correct number of players.  Then, adjust the PREMIUM and BENCH baselines to more accurately reflect the approximate baselines/tiers.

If you need help with this, I can work out an example this evening after work/gym.

Edited by ZidaneValor, 08 July 2013 - 05:52 AM.


#12 binhdvu83

binhdvu83

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 1,417 posts

Posted 08 July 2013 - 06:35 AM

Thanks for sharing this
30 team (9-cat) H2H NBA dynasty league 1st year
G: CP3, J. Butler, J. Jack, S. Livingston, P. Prigioni
F: T. Harris, C. Frye, B. Diaw, M. Miller
C: M. Gasol, B. Wright

30 team (8-cat) H2H NBA dynasty league  6th year
PG: J. Calderon, M. Chalmers
SG: A. Bradley, D. Harris
SF: N. Batum,  J. Johnson
PF: D. Nowitzki, C. Bosh
C: Anthony Davis

#13 Du5t3r

Du5t3r

    Rookie

  • Members
  • PipPipPipPip
  • 101 posts

Posted 08 July 2013 - 08:03 AM

Thank you "Future Man"
QB- Newton Brady
RB- Charles Ridley, Woodhead, Davis, Sankey, Michael, Mason
RB- McCoy
WR- Green Hunter, Williams, Watkins, Matthews
WR- Jeffery
WR- Floyd (Michael)
TE- Rudolph, Reed, Kelce, Sefarian-Jankins

DE- Ansah Lawrence
DE- Watt
DT- Richardson
LB- Ogletree Alonso, Collins, Smith
LB- Wagner
LB- Levy
LB- Hawk
CB- Revis
CB- Hall
S- Berry Quin
S- Burnett

#14 chriscarman

chriscarman

    Double-A

  • Members
  • PipPip
  • 30 posts

Posted 08 July 2013 - 02:19 PM

Thanks for the response Zidane. Yeah, I'm a little confused. My Excel skills suck.
So the steps are:

#1 change the total number of teams in D8 from 10 to 14

#2 change the total number of players in D18
So I have 17 roster spots (not counting an IR) 14 x 17 = 238
Do I not have to also adjust D15-18 to reflect this new total? (cause those totals still add up to 160)

#3 Change the baselines. This is what I'm most confused about. So first I change the Flier baselines in B4/C4/D4/E4, followed by the Bench baselines in B3/C3/D3/E3, then the Premium baselines in B2/C2/D2/E2? So what should those baselines be? Is premium the first 14 QBs in a 14 team league? Is premium for RBs the first 14 or the first 28? I tried changing those values and the numbers started fluctuating wildy (top QBs went up to just over $100).

My last question is separate from changing the values. Is there a way to highlight (fill) the cells with the player names who are in lower (shaded) tiers? For the premium tier players who have no shading in the cells, I'm able to right click the player name cell and fill it with a color (I use red, yellow, green, blue). When I do this for players in the shaded tiers, the color doesn't show up. Is there a way to remove that shading without messing with the calculations? This isn't a huge deal, if I have to I'll print it out and use highlighters on the paper.

Thanks again for your help, it is MUCH appreciated!

Edited by chriscarman, 08 July 2013 - 02:21 PM.


#15 mrjoshc

mrjoshc

    Triple-A

  • Members
  • PipPipPip
  • 82 posts

Posted 08 July 2013 - 04:18 PM

Again, like last year this thing rocks.

However... it frickin rocks even more now. I really like how you make this incredibly objective (outside of projections). A point is a point no matter where it comes from.

Thanks for all the hours of hard work!

#16 ZidaneValor

ZidaneValor

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 1,927 posts

Posted 10 July 2013 - 10:21 AM

I apologize for the delay; I am swamped with work.

I will try to get to your questions tonight.

#17 chriscarman

chriscarman

    Double-A

  • Members
  • PipPip
  • 30 posts

Posted 10 July 2013 - 12:45 PM

No rush at all, my auction draft is still two months away...

#18 shawntown

shawntown

    Just Drafted

  • Probationary Member
  • 1 posts

Posted 11 July 2013 - 10:34 AM

Great stuff, man, really. I didn't find the instructions difficult at all--you did a fine job explaining step-by-step. But I had one issue maybe you or one of the repliers might be able to help with.

I'm on a Mac, but I do have Excel X for Mac. But for some reason, my comp won't let me open it in Excel, it opens in Numbers. Weird. So--and here's my issue--I can't sort the columns as you outline in step 4 of the Auction Values sheet. It just beeps at me when I try to select the column(s) KL and sort. When I try to do it manually, from the menu at the top, the Sort commands are grayed-out. I'm not great with spreadsheets, so maybe I'm missing something obvious?

Thanks again for your work.

#19 ZidaneValor

ZidaneValor

    Superstar

  • Established Members
  • PipPipPipPipPipPipPip
  • 1,927 posts

Posted 11 July 2013 - 09:56 PM

I will answer your specific questions this weekend.  Sorry for the delay.

Until then, here is the second draft of my auction values.  I updated a couple of projections, added some deeper league players, and more importantly, gave examples of values for 10, 12, and 14 team leagues in standard and 1-PPR.

The third draft, I'll include some 0.5 PPR values.

Please inform me about any inconsistencies or issues with the Excel Workbook.  Thanks.

http://heartless-ang...2013-07-10.xlsx

#20 usurpedus

usurpedus

    Veteran

  • Members
  • PipPipPipPipPip
  • 357 posts

Posted 11 July 2013 - 11:42 PM

awesome!
12 team auction keeper league high stakes

qb: brees tannehill
rb: forte vereen thomas sproles
wr: jeffrey garcon gordon harvin moore
te: cook eiffert
k: bryant bironas
dst: bal cle