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 RynMan » Thu Feb 03, 2005 12:09 am

I have been trying to do something similar for the past couple of weeks. I am basically trying to come up with a formula that best describes a player's value as a number, taking into account their categories (the standard 5x5 hitting cats.). The way I found that mlb.com did it is this:

(R x 5) + (H x 4) + (HR x 15) + (RBI x 5) + (SB x 15) - AB

I used this formula to run over the projections i have each player for the 2005 season. This takes into account batting average by subtracting AB's, while giving 5 points for a hit. There was a bit of contraversy about whether or not the values that the categories are multiplied by are fair. The above equation ranks HR's and SB's are equal, however steals are rarer, but HR's contribute to BA, HR, R, and RBI.

The reason I want to develop a formula like this is simply to rank my position players....by position (ie top 15 1B etc.). What I did last night (but am not sure if it is relevant) was calculate how many of each of these categ. occurred per at bat for 2nd basemen.

Total Runs / Total AB (for 2B players) = 0.0356 (just guessing)

I then used the inverse (by dividing 1 by ^^ that number, 0.0356), and it gave me a value - 28 for the above example. Then I plugged that number into the equation that mlb.com had, and then ran it through my projections for each player, hence giving me my rankings. I dont know if this helps, but it is similar im guessing. I dont even know if what i have done is right, or that I will even run by the rankings that the result gives me. Just throwing it out there....
Image
RynMan
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerAward-Winning Graphics ExpertMock(ing) DrafterEagle Eye
Posts: 8891
Joined: 4 Feb 2004
Home Cafe: Baseball
Location: Oztailia

Postby mbuser » Thu Feb 03, 2005 12:14 am

seems to me if you got averages and stdev's for the actual number of each stat (i mean league totals in HR, RBI, SB, etc), you could figure a more precise value to multiply the stats by :-?
Image
mbuser
Major League Manager
Major League Manager

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

Postby DaveClay12 » Thu Feb 03, 2005 12:24 am

Any place anyone knows about to input stats from?
DaveClay12
Major League Manager
Major League Manager

User avatar

Posts: 1352
Joined: 20 Mar 2003
Home Cafe: Baseball

Postby DaveClay12 » Thu Feb 03, 2005 12:38 am

mbuser wrote:actually starting pitchers would have a negative value in saves if you group RP with SP (since the group average would be a positive number)

(player value - group averages)/group stdev = save value

for integer categories you can use the above formula

for batting average value try:

((player BA - group avg BA)/group stdev BA)*(player AB/group avg AB)

for BA, OPS, and ERA, WHIP you can use the above formula. for pitching just replace AB with IP



OK, I think I'm starting to get it... Three questions -

1) Is it possible to get a negative number for the value of a given player in a given category?

2) Is it strange that the StDev and Averages of some of the categories are very similar?

3) To figure out the overall value of a player, do you multiply all their category values?
DaveClay12
Major League Manager
Major League Manager

User avatar

Posts: 1352
Joined: 20 Mar 2003
Home Cafe: Baseball

Postby Grouperman941 » Thu Feb 03, 2005 12:40 am

I don't use a formula, but I assign point values manually to my projections which I sum to give each player a 'score'.

I use a 10-point scale (previous years used a 5-point but this is better I think). I figure out what the average contributor on my team should have in the cat. For example (arbitrary) 81-90 RBIs. That gets 5 points. Then I determine a max for a category (121-130 RBIs) which would get 10 points. Then I figure the lower end (41-50 is 1 point and make steps in between. I mess with the figures to make sure that they rate players the way I want on a few stars like Tejada or Vlad and I try Pierre to see where a base stealer ends up in relation to them.

I then go line-by-line with projections based on weighted 3-year avgs and objectively plug in the #s. Sometimes I use sorts and fill, but line-by-line lets me study the players a little, too, and rethink the projections if necessary.

So far so good, I think.
Grouperman941
Major League Manager
Major League Manager

User avatar
Cafeholic
Posts: 2079
Joined: 2 Jan 2004
Home Cafe: Baseball
Location: Rays Country

Postby Amazinz » Thu Feb 03, 2005 12:41 am

1. Yes

2. I don't use this exact method but I would think you'd average them.
Amazinz
Mod in Retirement
Mod in Retirement

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyeWeb SupporterPick 3 Weekly WinnerSweet 16 SurvivorLucky Ladders Weekly Winner
Posts: 18800
Joined: 16 Mar 2004
Home Cafe: Baseball
Location: in Canada, toughening up figure skaters

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

Yeah, that mlb.com way just sounds like a shortcut for people who do not want to take the time to input data and understand the relationships between each of the categories on their own (a solution to which has already been described in this thread).

Here is a link that wveres posted last year to help with raw data input Dougstats.
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 mbuser » Thu Feb 03, 2005 12:44 am

RynMan wrote:(R x 5) + (H x 4) + (HR x 15) + (RBI x 5) + (SB x 15) - AB

i wonder why they use those values, when it appears that from only positional players there were roughly

22245 R
42173 H
5278 HR
21231 RBI
2494 SB

recorded last season. i can't imagine you'd want to give a number value to SB roughly 17X what you give H, but there were 17X as many hits as there were SB :-?
Last edited by mbuser on Thu Feb 03, 2005 12:45 am, edited 1 time in total.
Image
mbuser
Major League Manager
Major League Manager

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

Postby Cornbread Maxwell » Thu Feb 03, 2005 12:45 am

rototimes value calculator is a good place to start if you are looking for a model.

yes - players can absolutely have a negative value. If they actually hurt you then they shouldnt have a positive value.
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 mbuser » Thu Feb 03, 2005 12:48 am

DaveClay12 wrote:OK, I think I'm starting to get it... Three questions -

1) Is it possible to get a negative number for the value of a given player in a given category?

2) Is it strange that the StDev and Averages of some of the categories are very similar?

3) To figure out the overall value of a player, do you multiply all their category values?

1. yes - it essentially means they are 'below average' in the category

2. no

3. add them all to get a 'total value'
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: Laicekl and 7 guests

Forums Articles & Tips Sleepers Rankings Leagues


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

Toronto at NY Yankees
(1:05 pm)
Washington at Miami
(1:10 pm)
indoors
NY Mets at Atlanta
(1:35 pm)
Boston at Baltimore
(1:35 pm)
Milwaukee at Pittsburgh
(1:35 pm)
Chi White Sox at Tampa Bay
(1:40 pm)
indoors
Cleveland at Minnesota
(2:10 pm)
Detroit at Kansas City
(2:10 pm)
Seattle at Houston
(2:10 pm)
LA Dodgers at Chi Cubs
(2:20 pm)
Texas at LA Angels
(3:35 pm)
Philadelphia at Oakland
(4:05 pm)
Arizona at Colorado
(4:10 pm)
San Francisco at San Diego
(4:10 pm)
Cincinnati at St. Louis
(8:05 pm)

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