Excel spreadsheet formula anyone? - Last bit of help needed.

Moderator: Baseball Moderators

To get the values for my players, I added their standard deviations from the mean in all five categories, then calibrated a system that rewards players based on how far they are from the mean in every category combined.

This means that a big base stealer with hardly any other tools still can carry value, while a player that is average at everything is that exactly in my rankings. Then, someone who is a stud in a variety of categories really stands out.
Mike Pelfrey > Matt Garza
shortsavage
General Manager

Posts: 2946
Joined: 25 Aug 2003
Home Cafe: Baseball
Location: UC Davis

Re: Statistical Analysis - Excel spreadsheet formula anyone?

DaveClay12 wrote:I'm trying to put together a draft sheet that ranks available players, taking into account all the statistical categories my league has in approximately equal proportion. I'd like to come up with a "final number" in order to rank the players' value in my league after taking into account all the statistical categories. Has anyone tried this before?

Dave,

This is what I do, almost every single year for both basketball (I use per-game averages) and baseball (raw totals from last year).

What I do is this... and if you can't follow along you can PM me. I copy all the stats onto an EXCEL sheet. Then, I sort each stat column involved (5x5, sort those stats) and find out what the lead was for that stat, in my data sample...

Lets take the hitting categories... your leaders were 133 R, 48 HR, 150 RBI, 70 SB, and a .372 AVG. I take a number that is higher than any of those numbers, say, 200 in this case. Then, I take each lead number and divide it into the 200... so for R you would get 200/133=1.5037 and for HR you would get 200/48=4.1666... Now, use the Excel formulas to multiply each respective player's stat by their magic number... so, for instance, the Sean Casey line would be 101*1.5037xxx in the first cell and 24*4.1666xxx... in the second cell. You can easily copy these formulas down for all players. Finally, I add those five new numbers up, and come up with a total. If you rank this total from top to bottom, you have a good list of how the stats contributed to ROTO success. What kind of order do I get doing this?: Pujols, Beltran, Vlad, Bonds, Abreu, Beltre, ARod, Manny, Tejada, and Edmonds in my top 10 using last years raw totals. To use it as a predition tool, you have to do the mind work. There are a million ways you can look at stats and analyze, this is but one, simple way that I use.

With pitching, it is a bit tougher, but I guess it could be done. But I seperate my pitchers into three lists: 1. Established closers, ranked by saves last year 2. Lower end closers and top setup guys, ranked by Saves if they should close, and then strikeouts if they won't and will only be "top middle men" and finally 3. Strikeouts for starting pitchers. Usually I filter out all the pitchers with an ERA of over 5. But, its up to how I feel. To me, it is much easier to analyze hitting stats than pitching, and pitchers tend to hold many more questions. Finally, their IP really determines the effect that their ratios have and you can quantify all you want, but it is darn tough to predict that Peavy will drop almost 2 ERA points, Ollie will increase his k's by 100 and halve his ERA, and that Prior will only win 6 games. I guess that is the main reason why pitchers don't get drafted as quickly as hitters do, but there are 2 million reasons why that is true I guess...

I hope this helps Dave and anyone else.
Skippyoz
College Coach

Posts: 292
(Past Year: 1)
Joined: 6 Mar 2003

shortsavage wrote:To get the values for my players, I added their standard deviations from the mean in all five categories, then calibrated a system that rewards players based on how far they are from the mean in every category combined.

This is the basic premise of standardizing a number. Do you take into account all players, or are there minimum levels in terms of playing time last year or playing time projected?
Skippyoz
College Coach

Posts: 292
(Past Year: 1)
Joined: 6 Mar 2003

Skippyoz wrote:
shortsavage wrote:To get the values for my players, I added their standard deviations from the mean in all five categories, then calibrated a system that rewards players based on how far they are from the mean in every category combined.

This is the basic premise of standardizing a number. Do you take into account all players, or are there minimum levels in terms of playing time last year or playing time projected?

I do not have exact barriers set up and I would not want to include everyone. My sheet includes just about anyone who I believe will be getting a significant amount of playing time in 2005.
Mike Pelfrey > Matt Garza
shortsavage
General Manager

Posts: 2946
Joined: 25 Aug 2003
Home Cafe: Baseball
Location: UC Davis

skippyoz - maybe I missed it but it appears as though ba isnt weighted properly. Beltran did not finish 2nd overall last yr with a sub .270 ba. Arod was not in the top 10 either - 11th or 12th if I remember correctly. Also - how do you factor in position scarcity?
Hall of Fame Hero

Posts: 5694
Joined: 7 Jul 2003
Home Cafe: Football

I took the Rototimes 2005 projections 460 players got their ave/stdev per all statistical categories HR RBI R SB AVG and then subtracted the ave. from thier actual divided by the stdev to come up with a value

EX Carlos Beltran
HR RBI SB AVG R
40 113 38 0.295 118

12.337 50.170 5.970 .268 52.943 AVG
10.21 29.98 8.46 .22 30.7 stdev

2.71 2.10 3.79 1.24 2.12 11.95 Value

Is this correct? Than I'd do this for all players using whatever projections.
Wyatt
College Coach

Posts: 202
Joined: 28 Jan 2005
Home Cafe: Baseball
Location: Tennessee

you need to have AB numbers to properly weigh batting average but you are on the right track for everything else
mbuser
Major League Manager

Posts: 1197
(Past Year: 21)
Joined: 21 Feb 2004

How does AB figure into the Ave. part? Rototimes has Carlos projected at 583 AB w/ .295 ave.

The ave. for the total 461 players is .268 The stdev .22 How do you come up with his AVG. value
Wyatt
College Coach

Posts: 202
Joined: 28 Jan 2005
Home Cafe: Baseball
Location: Tennessee

One more thing If what i did is on the right track , how do you determine your list of players to figure your AVG & Stdev on. Alot of them from the rototimes that I used to see if I was on the right path had alot of bottom feeders thats not really going to get much playing time. How do you determine the cutoff?

Thanks
Wyatt
College Coach

Posts: 202
Joined: 28 Jan 2005
Home Cafe: Baseball
Location: Tennessee

Wyatt wrote:How does AB figure into the Ave. part? Rototimes has Carlos projected at 583 AB w/ .295 ave.

The ave. for the total 461 players is .268 The stdev .22 How do you come up with his AVG. value

((.298 - .268)/.22)*(583/average AB's)

and for your last question that is really subjective -- you could figure everyone's value and then eliminate all players with a negative value. then you would only be left with the players who were 'above average' last season.
mbuser
Major League Manager

Posts: 1197
(Past Year: 21)
Joined: 21 Feb 2004