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

Moderator: Baseball Moderators

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

This might be a simple question, and it might be unanswerably difficult:

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? Can it work (how would I take into account categories with zero's (e.g. a starting pitcher has zero saves, so any multiplication formula would come out with zero as the answer)? How? Any advice would be greatly appreciated.

Off Cat: BA, HR, SB, R, RBI, OPS
Pit Cat: W, ERA, WHIP, K/IP, S, L-QS

Thanks a lot,

Dave
Last edited by DaveClay12 on Thu Feb 03, 2005 6:54 pm, edited 1 time in total.
DaveClay12
Major League Manager

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

I would think you are adding up the value of categories not multiplying, but you could assign a value of like 1 to all 0's that way it doesn't increase them at all
There are 3 ways to do things, the right way, the wrong way, and the Max Power way!
pgbridge2000
Softball Supervisor

Posts: 98
Joined: 20 Sep 2004
Home Cafe: Baseball
Location: My Computer

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

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

Right... The problem, is, though, that you can't add batting average to HR's... 1 HR would count 3 times as much as a .333 hitter, which makes no sense. That's what I'm trying to conquer... How do you make a .300 hitter as numerically important as a 25 HR guys, and how do you add, or multiply, or whatever, the two statistical categories (as an example, in fact it's 6 categories) to have them all have equal weight?
DaveClay12
Major League Manager

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

you dont want to make the stats themselves have a value -- you want to have the players stats relative to others have a value. with averages and stdev it accomplishes that for all of them
mbuser
Major League Manager

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

mbuser -

have you done this before? how is it best accomplished, especially for a guy who hasn't seen or dealt with a standard deviation in about 8 years?

Thanks a lot,

Dave
DaveClay12
Major League Manager

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

thats the beauty of excel -- click a cell, click the fx (function) button and select stdev from the list. it will prompt you to select the cells to calculate if from and there you have it.
mbuser
Major League Manager

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

You've done this before then? You don't happen to have an example I could work with, do you?
DaveClay12
Major League Manager

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

Yeah, I made a sheet like this about a month ago. The data entry isn't fun, but the formulas are not too bad.

Subtracting the mean of a category from the players total then dividing by the standard deviation is all that you have to do. It isn't that tough.

You may want to read an article on standard deviation just so that you understand what you are doing, but excel or a comparable program will do all of the work for you.

Utilizing standard deviation and sufficient data, a .300 hitter should balance with a similar power threat.

I believe that this sort of method is ideal for calibrating player values for rotisserie systems, making roto significantly more difficult to prepare for than points leagues. Just make sure to save enough time and energy to amend your raw data, as the numbers cannot do all of the talking.

If you do not know enough about excel to do this, try getting help from a friend or playing around with the program on your own. Every elite fantasy owner should be able to effortlessly formulate their ranking system with a database or spreadsheet program.

That or know how to contract someone who can do so for them (I charge \$8 an hour).
Mike Pelfrey > Matt Garza
shortsavage
General Manager

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

for 3 players who have HR totals of 35, 25, and 20

the average is 26.667
the stdev is 7.638

the values are:
(35 - 26.667)/7.638 = 1.09
(25 - 26.667)/7.638 = -0.218
(20 - 26.667)/7.638 = -0.873
mbuser
Major League Manager

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