Excel spreadsheet formula anyone? - Last bit of help needed. - Fantasy Baseball Cafe 2014

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

Moderator: Baseball Moderators

Why not just put dollars values on them and then rank them that way?
http://www.fantasybaseballcafe.com/forums/viewtopic.php?t=92352
`25                "Love the Padres"                              RafaelDodgers FAIL|Mets FAIL|Canada FAIL`
wrveres
Hall of Fame Hero

Posts: 31781
(Past Year: 757)
Joined: 2 Mar 2003
Home Cafe: Baseball

Cornbread Maxwell wrote:skippyoz - maybe I missed it but it appears as though ba isnt weighted properly. Beltran did not finish 2nd overall last yr with a sub .270 ba. Arod was not in the top 10 either - 11th or 12th if I remember correctly. Also - how do you factor in position scarcity?

No, AVG is not weighted properly using that assumption, but the numbers are based on two things: Either last years stats or This year's projections. If you use last year's stats to determine this year's rankings, you don't get a complete and accurate picture of an approximate order (players either get older and lose productivity i.e. Bagwell; players get older and gain productivity; i.e. Coco Crisp; or players have missed time due to injury i.e. Marcus Giles). If you use next year's projected stats, they are obviously some sort of made up, approximate number...

What I am trying to say is that this is a rough guide, and a very easy one to use, to start putting people in some sort of order based on how they contribute in certain ROTO cats.

If you want to take into account position scarcity, I would just take a look at the list itself. You could notice that among the top 100 players, x amount are OF and only a few are Catchers... or, I like to sort the list by position, and then by the final number and you see the position rankings and how the top guys' numbers stack up against the other top guys at different positions.

You can use this system with any and all stats (doubles, walks, etc.) and factor them into the equation, as most Mock Drafts and value rankings are based on 5x5 stats.

Oh well... it is not an exact and not my final/absolute rankings, but I use them only as a start.

- Skip
Skippyoz
College Coach

Posts: 292
(Past Year: 9)
Joined: 6 Mar 2003

Wyatt
College Coach

Posts: 202
(Past Year: 1)
Joined: 28 Jan 2005
Home Cafe: Baseball
Location: Tennessee

Thanks everyone... After a trial on the catchers, and messing a little with the numbers (SB were over weighted, so I divided that value by 3, and OPS was underweighted, so I multiplied that value by 3, I got an excellent ranking.

That's how I'm dealing with position scarcity... Very simple. I know my team, I know what positions I need, and I know which ones are light, and which ones are packed, and I will use that information accordingly.

Thanks again!!!

Dave
DaveClay12
Major League Manager

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

for those who care about getting batting average right, here's the formula:

define: <a> = mean( a )

effective avg = ( (AB/<AB>)*Avg + (N-1)*<Avg> ) / ( (AB/<AB>) + N - 1)

where: AB = at bats, Avg = batting average, N = number of hitters starting on your team.

This "effective average" takes into account properly the number of at bats, so taking the mean and standard deviation of it is what you want when considering the "points" for that catagory.
Cooner
Major League Manager

Posts: 1028
Joined: 2 May 2004
Home Cafe: Baseball
Location: my home is in NYC, my heart is in PNC Park

One last question:

Aren't Justin Morneau's 19 HR in 280 AB far more valuable than, say, Erubiel Durazo's 22 HR in 522 AB's? How do I account for that (in integer categories)? Should I multiply by AB/Avg. AB?

Thanks,

Dave
DaveClay12
Major League Manager

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

DaveClay12 wrote:One last question:

Aren't Justin Morneau's 19 HR in 280 AB far more valuable than, say, Erubiel Durazo's 22 HR in 522 AB's? How do I account for that (in integer categories)? Should I multiply by AB/Avg. AB?

Thanks,

Dave

well, here's the problem- if you're basing this off of last year's stats, you really need to project how many at bats that player would get this season (preferably you should be projecting everything). However, I'll assume you're working with projections for this year.

In that case, then yes and no. If you know when Morneau will be playing, and substitute someone into his spot when he's not, then the answer is yes, of course. But, if you don't have someone to put in his place and instead play Morneau full time, even if he's not playing, then no, obviously 22 HRs is better than 19.

Really, you'd need to consider which would be better, Morneau and his replacement or Durazo. That's almost impossible to consider. However, when your final numbers come out, keep in mind that you can always bump up Morneau if you think he'll play half the season and you're willing to keep two guys to play one position. Hope that helps...
Cooner
Major League Manager

Posts: 1028
Joined: 2 May 2004
Home Cafe: Baseball
Location: my home is in NYC, my heart is in PNC Park

Thanks Cooner - my question is a little different. It's about accounting for late callups (Morneau, Francis, etc.) and injuries (Reyes, Big Hurt, Magglio, etc.)... I want to get the value of these players based on their stats from last year being projected to what they would have been if they had played the whole year. After that, I can assess whether it would be worth having both them and a backup, you know? Is there any way you can think of for me to do that?

For example, I'm a huge Reyes fan. I want to know what his value would have been had he played the whole season at the rate he did. I'm thinking his value would have been high enough to justify keeping him and drafting a backup.. Does that make sense?

Thanks again,

Dave
DaveClay12
Major League Manager

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

I always do a percent total stats thing, basically (any stat/total of all the stats in the majors (like among the top 246 players, their batting averages totaled 67 I think) so it would be .333/67 gives the percent of the total stats they earn. The advantage of this is once you tweak the valuations, by deleting players as they get selected you can get the spreadsheet ot revalue every player based on the new total stats (like if another owner grabs Crawford, then someone like Juan Pierre's value will jump because their are less stolen bases available and Pierre has a lot of the ones left.
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

that's what i meant by projecting at bats and generally projecting things.

If you project everything, guessing how many in each catagory each player will get, then it's straight forward.

If you don't want to do all that, or don't trust the sites that do that and post their projections, then you should at least project at bats for each player whose plate appearances you think will change a lot (like Morneau or Reyes).

By that I mean, say you expect that Morneau gets 500 at bats this season, as opposed to his 280 last year. Then just replace all Morneau's numbers by 500/280*the number.

eg: replace Morneau's HR total by 19*500/280 = 34 HRs

Then continue as usual.

You don't want to do something like HRs/AB because some guys will always get more at bats than others... I mean, can you image what Barry Bonds' HRs would be like if he had 500 official at bats? But he often gets walked, so he DOESN"T have 80 HRs a year.
Cooner
Major League Manager

Posts: 1028
Joined: 2 May 2004
Home Cafe: Baseball
Location: my home is in NYC, my heart is in PNC Park

PreviousNext

### Who is online

Users browsing this forum: No registered users and 7 guests

Forums Articles & Tips Sleepers Rankings Leagues

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

 • Cincinnati at Baltimore(7:05 pm)
 • Detroit at Cleveland(7:05 pm)
 • Boston at NY Yankees(7:05 pm)