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

gostanford07

Minor League Mentor

Posts: 546

Joined: 23 Feb 2005

Home Cafe: Baseball

Location: 707... The home of Jonny Gomes and Jason Lane

Yes you can use the RANK function to give you the roto points.

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

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

gostanford07

Minor League Mentor

Posts: 546

Joined: 23 Feb 2005

Home Cafe: Baseball

Location: 707... The home of Jonny Gomes and Jason Lane

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.

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

gostanford07

Minor League Mentor

Posts: 546

Joined: 23 Feb 2005

Home Cafe: Baseball

Location: 707... The home of Jonny Gomes and Jason Lane

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

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

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

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?

gostanford07

Minor League Mentor

Posts: 546

Joined: 23 Feb 2005

Home Cafe: Baseball

Location: 707... The home of Jonny Gomes and Jason Lane