I have an auction value list from a website I trust, but it seems to be for a much larger league than mine. Mine is a 12-team, $200 salary cap, 16-roster spot league. Is there an easy way to adjust the cheat sheet to fit my league?
- Isaac C., San Francisco, CA
The key to a good auction cheat sheet is that it allocates the right amount of money to the right number of players. In Isaac's league, for example, 12 teams are spending $200 each on 192 players (12 teams x 16 roster spots); so the dollar values of the top 192 players should all add up to $2400. So what if the top 192 players add up to $2600? The cheat sheet is, overall, overvalued by $200. To fix this, all you really need to do is multiply the value of each player ranked from 1 to 192 by $2400/$2600. The 193rd ranked player and up should all be zero. If you do this, the sum of the top 192 players will then add up to the appropriate $2400.
Similarly, if the top 192 players only add up to $2250, the cheat is undervalued by $150. To fix it, multiply the value of each player ranked from 1 to 192 by $2400/$2150. Player #193 and up should also all be zero. Like the example above, the sum of the top 192 players will normalize to $2400.
So the multiplier you use should be:
(# of teams x salary cap) / (sum of all players sold, which is # of teams x # of roster spots)
Now Isaac didn't ask how; he asked if there was an easy way to do this. If you're pretty proficient with Excel, then you can just copy and paste any auction value list (like our own Average Auction Values) into a spreadsheet and go to work. If you've never used Excel, however, I give an Excel tutorial at the very end of this article. For now, here are a few things to consider when using this adjustment method:
- After adjusting the whole list, look through it to make sure you agree with the new adjusted valuations. Note that the higher-ranked players will be affected considerably more than the lower-ranked players – something with which you might not completely agree. In the second example above, the multiplier is $2400/$2150 = 1.1163. This means that every value on the list will go up by 11.63%. 11.63% of a $72 valuation is $8.37 while 11.63% of $1 is 11.63 cents. Since the lowest ranked players usually go for $1 no matter what, a 12-cent bump is probably right. Also, if you are going to allocate more money to the players, it makes sense to give more of it to the more valuable players. All that said, you should still take a look and make adjustments as necessary. Just make sure that when you do, the top 192 players still add up to $2400.
- While this adjustment method takes into account the number of teams and roster spots, it isn't sophisticated enough to take into account different starting lineup requirements or scoring systems. If you only have to start two WRs and not three, for example, that will definitely make WRs less valuable. Or if you award a point per reception, that will obviously make WRs, TEs, and pass catching backs more valuable. You will have to make these adjustments on your own.
- If you use a different salary cap, you just need to do one more adjustment after the one above. Let's say, for example, that you use a $150 salary cap. When you have done the adjustment and the top 192 players add up to $2400, you just need to multiply every number again by $150/$200.
- What if you're in a keeper league? How do you adjust for that? The thing about keeper leagues is that (theoretically) everyone is getting bargains on their keepers. Otherwise, why keep them, right? Because of the bargains people are getting from their keepers, that means that the non-keepers' prices should be inflated because there is now more money to go around. To make the necessary adjustments, you first need to figure out how many keepers are being kept and how much all their keeper prices add up to. Let's say, for example, that everyone in Isaac's league above keeps three players each and their prices add up to $400. This means that $2000 ($2400 less $400) is now going into 156 players (192 players less 36 keepers). What you want to do is remove the 36 keepers from the list (again, do this in Excel). You should then add up the remaining top 156 players's values. Again, since the keepers are going for a bargain, the remaining 156 players' values will add up to much less than $2000. Let's say it adds up to $1791. You should then multiply each value by $2000/$1791. This multiplier will essentially allocate the extra money saved up from the keeper prices into the non-keepers that will be sold in the auction.
For those of you proficient with Excel, you can probably skip the rest of this article, which outlines a simple tutorial for those new to Excel. Please let us know at support@fantasyauctioneer.com if you have any feedback or questions about our adjustment method. Happy auctioning!
Excel Tutorial - How to Do The Adjustments Above in Excel
1. First, let's use our Average Auction Values (AAVs) as a starting point. Go to that page and copy all the data in the AAVs table by highlighting the whole table (including column headers) and holding the "Ctrl" and "C" keys down simultaneously.
2. Open up Excel and put your cursor on cell A1.
3. Now hold down the "Ctrl" and "V" keys down simultaneously to paste the AAV data onto the spreadsheet. You should now see a copy of the table in the spreadsheet. If all the information is dumped into one cell and not separated into a table structure, try copying the table from Internet Explorer and not Firefox. For some reason Firefox (from what I've seen) does that.
4. The "Rank" column header should now be on A1, "Name" should be on B1, "Pos" should be on C1, and "AAV" should be on D1. You can widen or narrow the columns if necessary by placing the cursor on the lines between the letters at the top of each column, clicking and dragging to the right or left.
5. Place your cursor on cell F1 and enter the following formula in it: "=SUM(D2:D193)". This formula will add all the AAVs from the top ranked player to the 192nd ranked player. As of this writing, tha AAVs of the top 192 players on that list (from Larry Johnson to Santonio Holmes) add up to $2,319.
6. Now place your cursor in cell F2 and enter the following formula in it: "=2400/F1". (If it comes out as $1.03, that's okay. You can change the cell format if you choose.) 2400/2319 = 1.035, so all you need to do at this point is multiply each AAV by 1.035.
7. Now place your cursor in cell E1 and enter the column header "AdjAAVs". These will be our "adjusted AAVs."
8. Place your cursor in cell E2 and enter the following formula: "=D2*F$2". The dollar sign is not a typo; it's actually very important.
9. While cell E1 is still highlighted, place your cursor in the bottom right corner of the cell until the cursor turns into a small black cross and double click your mouse. This will copy the formula in cell E2 all the way down to the last row of the table. Another way of doing this is to copy and paste the cell into all the cells in the E column. The reason the $ is important in the formula in #8 above is that it freezes the multiplier to cell F2 as it is copied down the table. Try it without the dollar sign and you'll see the problem.
10. Place your cursor in cell E194 and enter zero. Again, place the cursor in the bottom right corner of the cell until the cursor turns into a small black cross and double click. This will zero out all the players ranked 194 and lower.
11. Place your cursor in cell F3 and enter the formula "=SUM(E2:E193)". Now you'll see that it does add up to $2400.
Hopefully you've learned enough about Excel in this brief tutorial that you can start using this very useful tool. Good luck!
George del Prado is a syndicated fantasy sports writers for FantasyAuctioneer.com, home of the first and only real-time auction-draft software on the Internet. In developing and maintaining the website, he is convinced he has participated in more fantasy auctions than anyone on the planet and hope you can learn from his experience. |