Wtd Avg 2007 Projection Spreadsheet -- 8/19/07

[rhino]

This spreadsheet has been updated with the latest projections found on the 'net. Download and enjoy.

 

Because this spreadsheet contains "HME" (Huddle Member Exclusive) data, you are asked to keep this spreadsheet for yourself, and not share the location nor the sp-sheet with non-huddlers. Your effort in this matter is appreciated.

 

 

To get a TE-REQD spreadsheet -- Go here.

To get a NON-TE speadsheet -- Go here.

 

In each of these sites (directories), you'll want the file:

 

Wtd Avg 2007 Projections 081107 NON-TE.zip, or

Wtd Avg 2007 Projections 081107 TE-REQD.zip

 

If you can't get this file to open or work, then try the *v97.zip file. And if that doesn't work, then try the *v95.zip file.

 

Good luck.

 

Couple enhancements:

 

1. The following numbers were updated:

 

a. Huddle Projections, as of 8/17/07

b. FFToday Projections, as of 8/16/07

c. Fantasy Sharks Projections, as of 8/15/07.

d. Rototimes Projections, as of ??? (copied on 8/19/07).

f. Fantasy Sports Central Projections, as of ??? (copied on 8/19/07)

g. Sport Fanatics Projections, as of 8/14/07.

h. Myfantasyleague avg pick data updated, as of 8/19/07, using drafts started between 8/15/07 and 8/24/07.

i. Huddle Depth Charts, as of 8/19/07.

 

2. The profiles for the WR, TE, and PKs have yet to be updated. Therefore, some of their data (age, weight, experience) may be a bit off. QBs and RBs were updated during the 8/11/07 release.

 

Let me know if you have any problems, or have any suggestions. Thanks!

[rhino]

Been wanting to put a "user guide" together, but haven't made the time. But, here's a quick skinny... (copied from other thread [8/11/07])

 

1. Open the file with macros enabled.

 

There are a lot of sorting options in this spreadsheet. Instead of trying to select the full table, and then entering the sorting criteria, a lot of buttons were created that utilize macros to do the sorting for you. Without these, you will not be able to easily and quickly re-sort your rankings the way you want them.

 

2. Enter your scoring rules

 

After opening the file, the first thing one should do is to enter their own scoring rules on the "Scoring" tab. It is set up so that one can enter different scoring rules for each position. For example, a passing TD for a QB can be 4 pts, and a passing TD for a RB can be 6 pts. This step should be pretty quick to do.

 

2.1 Recalculate the players projected fantasy points

 

Based on your scoring rules, players could be ranked differently than the default order you received with the sp-sheet. *** ALSO, AUTOMATIC COMPUTATION IS TURNED OFF in this sp-sheet. *** Therefore, to compute the players' projected fantasy points BASED ON YOUR SCORING RULES, hit the <F9> recalculate key. You may want to go to a "Rank" tab before doing this, to see the points being recalculated. For example, go to the "QB Rank" tab before hitting the <F9>.

 

3. Resort the rankings.

 

Just because the players' projected fantasy points are recalculated, doesn't mean they have been automaticallly sorted. This is because one can sort on 15 different criteria. So, to get a handle on things, simply go to each "Rank" tab (not the "Rank II" tabs), and hit the "YR PTS" sort button, to resort the players by projected fantasy points.

 

4. Update (recalculate) the rankings on subsequent pages.

 

Now, the new rankings need to be "copied" (ie, "recalculated") to the "Rank II" and "One Pager" tabs. This is just another "<F9> recalculate function.

 

That's the basics. Get that down, and move onto the next level...

 

5. WEIGHT your projections

 

There are several different sources included in their projections for the players. Look them over, and determine if you like one or the other. For example, go to the "QB" tab. Just SUPPOSE you like the Huddle's projections a lot. So, let's give them a heavy weight, like a 10 or something, by changing the value in column E6 from "1" to "10". Then, you notice your least-favorite website is included -- Sportfanatics.com, for example. You want to give them a light weight, like a .000001. So, enter ".000001" in cell N6.

 

*** NEVER GIVE A WEIGHT OF ZERO ***, as this may result in numerous "divide by zero" errors.

 

6. Add your own projections

 

There is a column on each position tab (ex: "QB") to provide your own projections. There's also columns available to add projections from other pay sites you may have access to. Simply enter these in.

 

7. Recalculate, and resort, ...

 

Now that you've changed the "weight of the projections" -- one of the key values that determines a players' projected fantasy points -- and possibly added additional projections, then projected fantasy points for each player needs to be recalculated. (Hit <F9> like we did in step 2 above.) And then the rankings need to be re-sorted. (Go to each "Rank" tab like we did in step 3 above.) And then the resorted rankings need to be copied to the subsequent pages (Rank II and One pager tabs). (Repeat step 4 above.)

 

Now you're beginning to get a jist of this sp-sheet. Let's keep moving.

 

8. Unaccounted scoring rules

 

Suppose the Scoring tab does not cover all your scoring rules. For example, your league may have bonus points for a QB that throws for 300+ yds. Or, you may award PKs 4 pts for a 40+ yd field goal. Since there's not a lot of projections stats out there for these types of scoring rules, I don't include them. However, I do allow you to ADJUST a players' ANNUAL projected fantasy points on the "Rank" tabs, to accommodate these shortcomings. Let's use the "QB Rank" tab as an example.

 

a. Let's use PManning as an example.

b. Let's assume your league gives 5 extra points when a QB throws for 300+ yds.

c. You project that PManning will throw for 300+ yds on 4 different occassions this year.

d. 4 games at 300 yds, at 5 pts apiece is an additional 20 fantasy points for the year.

e. Enter 20 in the "Annual Points Adj" column for PManning.

 

Of course, when you do this, you'll have to do the <F9> recalculate, resort, and <F9> thing again.

 

Using this for a draft tool...

 

9. Printing your cheatsheet...

 

Go to the "One Pager" tab. This is your cheatsheet, based on all the values you input above (scoring rules, new projections, your projections, weights for each source of projections, adjusted fantasy points, etc). This cheatsheet has a lot of info on it -- Name, team, bye week, depth chart, 2006 consistency, yrs experience in the league, current age, projected fantasy points (weekly), and average draft position.

 

If you want all of this information on your cheatsheet, then simply print this page. It should be set to print on one page (tiny font!).

 

If you don't want all this data printed on the page, then simply "HIDE" the columns you want. Suppose you don't care how old the players are. Right-click column "H", and choose "Hide". Repeat this for columns "R" and "AC". So, if you want this data (age) in the future, you simply "Unhide". Never delete columns in this "One Pager" tab.

 

10. Enter team names...

 

Enter your league's team names, in draft order near the top of the "Draft" tab, in columns C and K.

 

11. Size the draft to your league size...

 

The maximum size this sp-sheet will accommodate is a league size of 18 teams. If you have a smaller league (most league sizes are 12 these days), then let's remove some of the unneeded cells and formulas. If your league size is 12, as an example, delete all columns to the right of "AW". Now, delete the columns, not just the data in the columns.

 

Next, let's remove the teams in the "Summary Area". Assuming your league size is 12 teams, then select cells K6 thru P11, right click in that selected range, and choose "Delete...", "Shift cells left".

 

12. Enter the draft picks.

 

As each pick is made, enter the pick into the team's roster area. So, team 1 will choose Ladainian Tomlinson, I'm guessing. Then, enter "Tomlinson, Ladainian" in cell C21 (which is their RB area). If you get the spelling wrong, then it will not allow you to continue until you have the spelling matching what is set in the sp-sheet. Then, enter the draft pick : "1.01". Hit <F9>.

 

After hitting <F9> (recalculate), you'll notice that the players' team, bye week, and the weighted avg projected value (weekly) is updated for the given player.

 

Then, go back to the "One Pager" tab, you'll notice that the player is "highlighted" off your cheatsheet.

 

Then, go back to the "Depth Charts" tab, you'll notice that the player is "highlighted" off your depth chart.

 

There's more to it than this, but this should give you the main components/uses of this sp-sheet. Hope it helps. Feel free to ask questions.

[Grits and Shins]

It is still a piece of crap

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[tarbell]

rhino,

Great work! I made a few changes to the spreadsheet which I thought might be good ideas for you do implement also.

 

Things I added...

A menu page and "home" links - I found that there were just too many tabs, even working on a dual screen setup, so I made a main page menu that just links to each tab and then put a home link on each individual page. This makes navigation much easier.

 

Auction Values - I added a general page with auction values into the sheet, and if I have more time I want to integrate that into the player data pages. I also added a column to the draft page to enter the purchased values, along with a new cell on each team that keeps track of money left.

 

 

Ideas for improvement which I don't know how or don't have time-

 

On the one pager - the color of the picked players is very hard to see. I searched through excel and can't figure out how to make it display a different color. Do you know how?

 

Starting Roster info - maybe team pages with the starting roster data. I know you can kind of get this data from the consistency, but keeping track of who actually plays.

 

Injury Check box - maybe a cell next to player names with a macro checkbox that will change their color on the one pager to indicate injury

 

Auction Values - some unique way of integrating auction values into the spreadsheet. I was working on an algorithm to assign calculated auction worth based on statistics but I could never get it just right, so I abandoned it.

 

Player groupings - In my and other drafts there are groupings of players ie. Quarterbacks for a specific team, where you draft the team QB instead of the player much like Def. I was trying to think of a good way to modify this to group players, especially for the drop down draft page, with no luck.

 

 

 

Anyway I just really wanted to post and tell you how amazing I think this database is and even if I had unlimited time I don't think I could come up with something as complete. Thanks a ton for this.

 

Greg

[SteelerPat]

I don't have Excel, will I be able to use Open Office to use this tool? Will the macros work? Thanks.

[Spartans91]

Wow. This is awesome. Thanks!

[rhino]

I don't have Excel, will I be able to use Open Office to use this tool? Will the macros work? Thanks.

Sorry, but no. At the same time, I don't have "Open Office", but I would think that this sp-sheet would not work (in all its glory). You can still give it a try by downloading it and opening it with "Open Office".

[rhino]

tarbell, good info there. Thanks!

 

I believe we fixed your "conditional formatting" issue with the "hard-to-read color", via pm's, right?

[Grits and Shins]

I generally make several modifications to base spreadsheet.

 

I re-arrange the tabs to my preference of order.

 

ONE PAGER MODIFICATIONS

I generally add an "x" factor column, those that do VBD probably know what this is.

I change the conditional formatting to a color I prefer (DARK GRAY with LIGHT GREY text ... except for my specific players which get a different conditional color).

 

DRAFT PAGE MODIFICATIONS

Reduce the number of teams to 12 in the summary box, putting 6 teams per column and deleting 3 lines from the spreadsheet

Modify the summary box to show the number of players each team has drafted and the number they have left to draft

Add a summary box that shows the number of players at each position teams have drafted

 

For auction leauges

 

ONE PAGER MODIFICATIONS

I generally add an "x" factor column, those that do VBD probably know what this is.

I change the conditional formatting to a color I prefer (DARK GRAY with LIGHT GREY text ... except for my specific players which get a different conditional color).

Add a column showing salary

Add a column showing % of cap

 

DRAFT PAGE MODIFICATIONS

Reduce the number of teams to 12 in the summary box, putting 6 teams per column and deleting 3 lines from the spreadsheet

Modify the summary box to show the number of players each team has drafted and the number they have left to draft

Add a summary box that shows the number of players at each position teams have drafted

Add salary information and totals