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

[DaveClay12]

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

[pgbridge2000]

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

[mbuser]

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

[DaveClay12]

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?

[mbuser]

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

[DaveClay12]

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

[mbuser]

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.

[DaveClay12]

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

[shortsavage]

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).

[mbuser]

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