Excel spreadsheet formula anyone? - Last bit of help needed. - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

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

Moderator: Baseball Moderators

Postby shortsavage » Thu Feb 03, 2005 12:49 am

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
General Manager

User avatar
Cafe WriterWeb Supporter
Posts: 2946
Joined: 25 Aug 2003
Home Cafe: Baseball
Location: UC Davis

Re: Statistical Analysis - Excel spreadsheet formula anyone?

Postby Skippyoz » Thu Feb 03, 2005 12:55 am

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
College Coach

User avatar

Posts: 292
(Past Year: 9)
Joined: 6 Mar 2003
Home Cafe: Basketball

Postby Skippyoz » Thu Feb 03, 2005 12:59 am

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
College Coach

User avatar

Posts: 292
(Past Year: 9)
Joined: 6 Mar 2003
Home Cafe: Basketball

Postby shortsavage » Thu Feb 03, 2005 1:13 am

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
General Manager

User avatar
Cafe WriterWeb Supporter
Posts: 2946
Joined: 25 Aug 2003
Home Cafe: Baseball
Location: UC Davis

Postby Cornbread Maxwell » Thu Feb 03, 2005 1:19 am

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?
Image
Cornbread Maxwell
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertPick 3 ChampionSweet 16 Survivor
Posts: 5694
Joined: 7 Jul 2003
Home Cafe: Football

Postby Wyatt » Thu Feb 03, 2005 1:28 am

Is this what your talking about MBUSER?
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
College Coach


Posts: 202
(Past Year: 1)
Joined: 28 Jan 2005
Home Cafe: Baseball
Location: Tennessee

Postby mbuser » Thu Feb 03, 2005 1:29 am

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

User avatar
Cafeholic
Posts: 1197
(Past Year: 23)
Joined: 21 Feb 2004
Home Cafe: Basketball

Postby Wyatt » Thu Feb 03, 2005 1:36 am

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
College Coach


Posts: 202
(Past Year: 1)
Joined: 28 Jan 2005
Home Cafe: Baseball
Location: Tennessee

Postby Wyatt » Thu Feb 03, 2005 1:39 am

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
College Coach


Posts: 202
(Past Year: 1)
Joined: 28 Jan 2005
Home Cafe: Baseball
Location: Tennessee

Postby mbuser » Thu Feb 03, 2005 3:00 am

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.
Image
mbuser
Major League Manager
Major League Manager

User avatar
Cafeholic
Posts: 1197
(Past Year: 23)
Joined: 21 Feb 2004
Home Cafe: Basketball

PreviousNext

Return to Baseball Leftovers

Who is online

Users browsing this forum: buiviopaufamp and 9 guests

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Thursday, Aug. 21
(All times are EST, weather icons show forecast for game time)

Houston at NY Yankees
(1:05 pm)
Cleveland at Minnesota
(1:10 pm)
Detroit at Tampa Bay
(1:10 pm)
indoors
Arizona at Washington
(4:05 pm)
LA Angels at Boston
(7:10 pm)
Atlanta at Cincinnati
(7:10 pm)
San Francisco at Chi Cubs
(8:05 pm)
San Diego at LA Dodgers
(10:10 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact