#### Topic: Variance and Standard Deviation Worksheet

This spreadsheet is a product of the conversation between DD, Xlledx, and others under the Magic Formula post linked here.

Average Projections, Variance and Standard Deviation Calc (normal size leagues)

Average Projections, Variance and Standard Deviation Calc (larger leagues)

The spreadsheet is my attempt to build on the idea that the Magic Formula is based on. For a complete explanation on what the Magic Formula is, please read the entirety of Joosbawx’s thread which I referenced and linked above. Cliff notes version: The Magic Formula calculates the difference between the top player and the “last starter” at each position. Those differences (also called ranges) are used to determine which position is deemed most valuable and therefore which position you should focus on early in the draft.

Rather than compare the ranges, DD had made the argument that comparing the players' totals to the overall mean (or average) score of their position would be a more accurate representation of each player’s value in relation to the rest of their position.

**Example:** The mean of the RB position last year in my league was 101.625. The RB1 scored 188 points, so his “replacement value”, or the value of RB1 when compared to the mean, is 188-101.625 or 86.375. Conversely, RB20 scored 70 points, so his replacement value would be 70-101.625 or -31.625

Once these replacement values are calculated, we can use them to determine a position’s variance and standard deviation.

* What the heck is variance and standard deviation*?

Ok, Quick statistics recap:

Variance measures how the data is distributed within a range, or how much the data differ. If every score were the same in the set of data, then the variance is 0, otherwise there will be some level of variance, the larger the variance the larger the disbursement. Technically speaking, variance is the sum of the squared differences between each score and the mean average of all scores or (X-Mean)^2.

Standard Deviation similarly measures the variation of the data from the average. A low standard deviation indicates that the scores tend to be very close to the mean for the position, whereas high standard deviation indicates that the scores are spread farther out over a large range. Technically speaking, standard deviation is the square root of the variance.

**So what does that have to do with Fantasy Football?**

Glad you asked.

The Variance and Standard Deviation helps us to determine the disparity among the scores of a given position. The higher the variance and standard deviation, the more spread out the scores are along the range and the farther the scores are away from the mean of the position. Therefore, the positions with a high variance and high standard deviation will generally be more valuable than the the positions with a low variance and low standard deviation.

**Example:** Using 2010 data from my league, the RB position had a variance of approx. 1177 and a standard deviation of 34. The WR position had a variance of approx. 501 and a standard deviation of 22. Using those stats, RBs overall were more valuable than WRs.

**I already knew RBs were more valuable than WRs. That seems like a lot of work to figure that out.**

True. But these stats can tell you so much more. By using the standard deviation and the replacement value, you can have a baseline to compare individual players at difference positions using positional value. Not sure when QB or WR should begin to enter the Round 1 discussion, This can help.

Going back to the above example, using the 2010 stats, here are the replacement values for the top 5 RBs and WRs (actual stats from my league)

Player Total Pts X-Mean Player Total Pts X-Mean

RB #1 219 114.6667 WR #1 148 53.27778

RB #2 144 39.66667 WR #2 142 47.27778

RB #3 138 33.66667 WR #3 137 42.27778

RB #4 138 33.66667 WR #4 125 30.27778

RB #5 130 25.66667 WR #5 125 30.27778

Obviously, RB #1 is the most valuable of the players, but surprisingly, WR #1 is the next valuable, scoring 53 more points than the average WR. Here are the 10 players listed in order, based on replacement value. (Disclaimer: my league scoring is much more balanced among the positions than most. I do not consider my league to have “standard scoring”)

Player Total Pts X-Mean

RB #1 219 114.6667

WR #1 148 53.27778

WR #2 142 47.27778

WR #3 137 42.27778

RB #2 144 39.66667

RB #3 138 33.66667

RB #4 138 33.66667

WR #4 125 30.27778

WR #5 125 30.27778

RB #5 130 25.66667

**But you’re using old data, how does that help me for the 2012 draft?**

Another good question! By averaging the stats of the last 4 years, we can generate projections for the 2012 season without worrying about guessing yards or TDs. Now considering that last year’s stats are more meaningful than stats form 3 or 4 years ago, we use the following formula to generate the 2012 projections:

2012 projections = 2011 scores(40%) + 2010 scores(30%) + 2009 scores(20%) + 2008 scores(10%).

So in 2012, I can predict that the #1 RB will scores approx. 210 points (188 * 40% + 219 *30% + 252 *20% + 185*10%)

**OK, OK, I’ll bite. How do I use the spreadsheet?**

There are two spreadsheets. One has the capabilities to do 12 QBs and TEs along with 36 RBs and WRs. The other is for deeper leagues with the availability of 24 QBs and TEs along with 48 RBs and WRs. To decide the number of each position to use, simply multiply the number of teams by the number of players you are able to start at each position:

**Example:** 12 team league with a starting lineup of 1 QB, 2 RB, 3 WR, and 1 TE would use 12 QBs, 24 RBs, 36 WRs, and 12 TEs.**Flex Example:** If your league uses a flex, I would count the flex as an extra spot for all allowed position. This will allow you to determine which position is best to use as your flex. So 10 team league with a lineup of 1QB, 2RB, 2WR, 1TE and 1 flex (RB/WR/TE) would use 10 QB, 30 RB, 30 WR, and 20 TE.

Once you have the number of positions, simply enter those players’ totals for years 2008-2011. Who actually scored the points does not matter. 2011 RB#1 is the RB who scored the most points whether it was Rice, Foster or whoever. As you enter the values, the spreadsheet will automatically begin to calculate the mean, the replacement value (X-mean), the variance and the standard deviation.

**Important note: Only enter data for the number of positions that you calculated above. If you are only using data for 10 QBs, leave QB 11 and QB 12 BLANK. If you enter any values in those rows (even 0), those values will be used in the calculations and ultimately skew the results.**

At this point, the spreadsheet is designed for 4 years worth of data. If you do not possess that much data, you would need to manually change the formula to recalculate the 2012 projections to use less than 4 years of data. Both spreadsheets already have my league’s data included as an example.

I hope you find the spreadsheet useful. Please feel free to offer feedback.

*Last edited by Devo49 (2012-08-11 21:55:29)*