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

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

Postby DaveClay12 » Wed Feb 02, 2005 11:14 pm

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

User avatar

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

Postby pgbridge2000 » Wed Feb 02, 2005 11:31 pm

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!
Image
pgbridge2000
Softball Supervisor
Softball Supervisor

User avatar

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

Postby mbuser » Wed Feb 02, 2005 11:37 pm

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
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 » Wed Feb 02, 2005 11:37 pm

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

User avatar

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

Postby mbuser » Wed Feb 02, 2005 11:39 pm

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
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 » Wed Feb 02, 2005 11:41 pm

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

User avatar

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

Postby mbuser » Wed Feb 02, 2005 11:43 pm

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.
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 » Wed Feb 02, 2005 11:50 pm

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

User avatar

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

Postby shortsavage » Wed Feb 02, 2005 11:53 pm

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
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:02 am

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

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

Next

Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 9 guests

Forums Articles & Tips Sleepers Rankings Leagues


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

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

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