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

Moderator: Baseball Moderators

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....
RynMan
Hall of Fame Hero

Posts: 8891
Joined: 4 Feb 2004
Home Cafe: Baseball
Location: Oztailia

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

Posts: 1198
(Past Year: 22)
Joined: 21 Feb 2004

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

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

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

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

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

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

1. Yes

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

Posts: 18800
Joined: 16 Mar 2004
Home Cafe: Baseball
Location: in Canada, toughening up figure skaters

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

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

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

Posts: 1198
(Past Year: 22)
Joined: 21 Feb 2004

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.
Hall of Fame Hero

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

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

Posts: 1198
(Past Year: 22)
Joined: 21 Feb 2004