Can this be done? (Excel help...) - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

Can this be done? (Excel help...)

Moderator: Baseball Moderators

Postby roninmedia » Mon Jun 12, 2006 4:24 pm

I have a spreadsheet format I did for my league that does everything needed for roto, including doing the appropriate averages if teams tie.

You just use rank with a simple modifier to account for ties.
roninmedia
Major League Manager
Major League Manager

User avatar
CafeholicEagle Eye
Posts: 1647
Joined: 16 Jul 2003
Home Cafe: Baseball

Postby gostanford07 » Mon Jun 12, 2006 4:34 pm

roninmedia wrote:I have a spreadsheet format I did for my league that does everything needed for roto, including doing the appropriate averages if teams tie.

You just use rank with a simple modifier to account for ties.


If it's not too much of a trouble, do you mind elaborating on the "simple modifier" and/or sharing your entire spreadsheet format? Does it automatically update itself witch cumulative stats?
Image
gostanford07
Minor League Mentor
Minor League Mentor

User avatar
Eagle Eye
Posts: 546
Joined: 23 Feb 2005
Home Cafe: Baseball
Location: 707... The home of Jonny Gomes and Jason Lane

Postby The Loveable Losers » Mon Jun 12, 2006 4:39 pm

gostanford07 wrote:Interesting, but why do you subtract .5 for two teams and 1 for 3? Am I to assume that if 4 teams were tied i would subtract 1.5?

Also, is it necessary to do the 13-x step?


You're right on the 4 teams...it's just teams at that rank - 1 multiplied by 0.5. So one team is (1 - 1) * 0.5 = 0. Two teams is (2 - 1) * 0.5 = 0.5. Five teams is (5 - 1) * 0.5 = 2 and so on.

The 13 - x step is not necessary if you take the approach that you took.
The Loveable Losers
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterWeb Supporter
Posts: 7290
Joined: 30 Mar 2005
Home Cafe: Baseball
Location: Cubs Win!!! Cubs Win!!!

Postby roninmedia » Mon Jun 12, 2006 4:59 pm

=((COUNT(B2:B13)+1-RANK(B2;B2:B13;0)-RANK(B2;B2:B13;1))/2)+RANK(B2;B2:B13;1)

Replace the red values with the data range you want to be ranked.
Replace the green values with the cell whose value you want to be ranked.

For the blue value, 1 is to rank the highest value to be the best.
For the blue value, 0 is to rank the highest value to be the worst.
roninmedia
Major League Manager
Major League Manager

User avatar
CafeholicEagle Eye
Posts: 1647
Joined: 16 Jul 2003
Home Cafe: Baseball

Postby J35J » Mon Jun 12, 2006 5:06 pm

Just a question. Every now and then I will see how my league is doing via roto scoring but it really makes no sense in doing this because drafting and playing the game in H2H leagues is quite a bit different than roto so just because you may or may not be doing well in a roto format means nothing because they are 2 different games! :-? Its actually quite misleading........


Jason
J35J
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterPick 3 Weekly Winner
Posts: 10467
(Past Year: 398)
Joined: 26 Jan 2004
Home Cafe: Baseball

Postby Big Pimpin » Mon Jun 12, 2006 5:24 pm

Yeah I was doing something with arrays first before I just abandoned it and went with the count function and then some nested if statements to adjust for how many teams were tied... Not too difficult once I thought about it for a minute... ;-D

And you're right about the rankings, you can do it worst to best and then subtract .5 or 1 or whatever, or do it best to worst, add the .5 or 1 or whatever, and then do 13-whatever. Either way works but it's a useless step...
Big Pimpin
Mod in Retirement
Mod in Retirement

User avatar
EditorCafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterEagle EyeWeb SupporterMatchup Meltdown ChampionPick 3 Weekly WinnerLucky Ladders Weekly Winner
Posts: 13710
(Past Year: 4)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

Postby gostanford07 » Mon Jun 12, 2006 11:26 pm

J35J wrote:Just a question. Every now and then I will see how my league is doing via roto scoring but it really makes no sense in doing this because drafting and playing the game in H2H leagues is quite a bit different than roto so just because you may or may not be doing well in a roto format means nothing because they are 2 different games! :-? Its actually quite misleading........


Jason


Oh, I know, I'm definitely aware. I'm just extremely bored during the day and I need something to do lol.
Image
gostanford07
Minor League Mentor
Minor League Mentor

User avatar
Eagle Eye
Posts: 546
Joined: 23 Feb 2005
Home Cafe: Baseball
Location: 707... The home of Jonny Gomes and Jason Lane

Postby gostanford07 » Tue Jun 13, 2006 3:23 am

roninmedia wrote:=((COUNT(B2:B13)+1-RANK(B2;B2:B13;0)-RANK(B2;B2:B13;1))/2)+RANK(B2;B2:B13;1)

Replace the red values with the data range you want to be ranked.
Replace the green values with the cell whose value you want to be ranked.

For the blue value, 1 is to rank the highest value to be the best.
For the blue value, 0 is to rank the highest value to be the worst.


For some reason, when I plug this in, it does not work. When I click 'Ok' on the error message, it highlights that first green value... any ideas? Where did I mess up?
Image
gostanford07
Minor League Mentor
Minor League Mentor

User avatar
Eagle Eye
Posts: 546
Joined: 23 Feb 2005
Home Cafe: Baseball
Location: 707... The home of Jonny Gomes and Jason Lane

Previous

Return to Baseball Leftovers

Who is online

Users browsing this forum: apz and 8 guests

Forums Articles & Tips Sleepers Rankings Leagues


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

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

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