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

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

Postby gostanford07 » Sun Jun 11, 2006 2:41 pm

Ok, so in my H2H league I want to further analyze the teams to see whos been getting lucky, etc. If I have all of the cumulative stats in all of the 5x5 categories, is there a way for me to plug them in to Excel and have it spit back what the standings would be if it were a roto league? i.e. is there a way to assign 12 points for first in runs, 11 for second... etc, etc for all of the categories and then compile a list of overal rankings? Thanks guys
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 Amazinz » Sun Jun 11, 2006 2:48 pm

Yes you can use the RANK function to give you the roto points.
Image
Maine has a good swing for a pitcher but on anything that moves, he has no chance. And if it's a fastball, it has to be up in the zone. Basically, the pitcher has to hit his bat. - Mike Pelfrey
Amazinz
Mod in Retirement
Mod in Retirement

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyeWeb SupporterPick 3 Weekly WinnerSweet 16 SurvivorLucky Ladders Weekly Winner
Posts: 18800
Joined: 16 Mar 2004
Home Cafe: Baseball
Location: in Canada, toughening up figure skaters

Postby red_foot » Mon Jun 12, 2006 3:54 am

I do this is my league. Excel is definitely up to the task. Maybe you did not know that the CBS interface IS Excel, it just looks prettier. Excel is a very sophisticated program and I have never known someone who understands all the different possibilities that it is capable of. What I do know is that there is very little that it cannot do. It's biggest limitations are with compiling huge amounts of data. For that, you should use an actual database. The biggest limitation to Excel is the person using it.
red_foot
College Coach
College Coach


Posts: 240
Joined: 26 Apr 2006
Home Cafe: Baseball
Location: Yo Mama

Postby Chicago RedSox » Mon Jun 12, 2006 10:56 am

It'll require some patience and hard work on your part to get it all working, but you should be able to do it using the IF function in Excel.

I don't have a template you can copy and paste from, but I use several excel features to track my teams successes and shortcomings.
Chicago RedSox
Major League Manager
Major League Manager

User avatar

Posts: 1436
Joined: 30 Mar 2005
Home Cafe: Baseball
Location: SanDiego

Postby gostanford07 » Mon Jun 12, 2006 2:32 pm

red_foot wrote:I do this is my league. Excel is definitely up to the task. Maybe you did not know that the CBS interface IS Excel, it just looks prettier. Excel is a very sophisticated program and I have never known someone who understands all the different possibilities that it is capable of. What I do know is that there is very little that it cannot do. It's biggest limitations are with compiling huge amounts of data. For that, you should use an actual database. The biggest limitation to Excel is the person using it.


ie Me !+) lol. I guess I just need to play around witht the program a little bit to figure out some stuff. Thanks to all who responded
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 Big Pimpin » Mon Jun 12, 2006 3:02 pm

Like Amazinz said, it's actually pretty easy. You could do it in three tables. The first one holds the cumulative stats for all of the teams. Then I'd re-create the table and use the RANK function to rank each person's Runs, HRs, etc 1-12 (or however many teams). With careful anchoring of the cells, you could just do the formula once and copy it. Then, in the third table, you could just subtract each person's rank in each category from one more than the number of members in your league (so if it's a 12-teamer, subtract from 13).

This way:

First = (13 - 1) = 12
Second = (13 -2) = 11
Tie for third = (13 - 3.5) = 9.5
etc.

Actually instead of recreating it, just PM me your email and I'll send you a template. It will take me two minutes to put it together. Then you can play with it and see what I mean. ;-D
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 Big Pimpin » Mon Jun 12, 2006 3:17 pm

The crappy thing is that ties don't count as 3.5 for instance, but as 3. I'll figure out a workaround... ;-D
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 3:34 pm

Lol I actually came on to say that I've started to do this using your method. However, instead of a third table, I just use the rank function, but with ascending totals to give my highest totals a rank of 12. However, I'm experiencing the same problems with the 3.5 instead of 3, etc. Let me know what you come up with, I'm pretty new to Excel. Thank you for the help
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 3:42 pm

Big Pimpin wrote:The crappy thing is that ties don't count as 3.5 for instance, but as 3. I'll figure out a workaround... ;-D


Do a count for each rank (I'd have to look at Excel to figure it out but I'm sure there's a way to count how many of the same rank you have) and deduct 0.5 from the total score for each of that rank above 1.

For instance, let's say we've got team A in sole first place, team B and C tied for second, team D in sole 4th and team E, F and G tied for 5th in a 12 team.

A is ranked 1st = 13 - 1 = 12 points
B/C tied for 2nd = 13 - 2 - 0.5 (two teams same rank) = 10.5 points
D is ranked 4th = 13 - 4 = 9 points
E/F/G tied for 5th = 13 - 5 - 1 (three teams same rank) = 7 points
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 gostanford07 » Mon Jun 12, 2006 3:58 pm

The Loveable Losers wrote:
Big Pimpin wrote:The crappy thing is that ties don't count as 3.5 for instance, but as 3. I'll figure out a workaround... ;-D


Do a count for each rank (I'd have to look at Excel to figure it out but I'm sure there's a way to count how many of the same rank you have) and deduct 0.5 from the total score for each of that rank above 1.

For instance, let's say we've got team A in sole first place, team B and C tied for second, team D in sole 4th and team E, F and G tied for 5th in a 12 team.

A is ranked 1st = 13 - 1 = 12 points
B/C tied for 2nd = 13 - 2 - 0.5 (two teams same rank) = 10.5 points
D is ranked 4th = 13 - 4 = 9 points
E/F/G tied for 5th = 13 - 5 - 1 (three teams same rank) = 7 points


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? In my preliminary chart, I just have the rank function sort by ascending for most of the stats, which gives a value of 12 to the team with the highest number of HR. Then for ERA and WHIP I do descending so the lowest number results in a 12. Wouldn't this process avoid having three seperate tables? Then again, if there is no other way to work around the tie senario, then I guess you have to use this method.

Finally, if I want to keep this data updated throughout the season, is there a way for Excel to automatically update it if i copy it from the Yahoo website? Or do I have to plug in all the stats by hand?
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

Next

Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 6 guests

Forums Articles & Tips Sleepers Rankings Leagues


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

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

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