PDA

View Full Version : Solved: Football Team Record Ranking



j19_2002
07-22-2010, 09:45 AM
Hello All,
Thank you for taking the time to view this. I am trying to rank from top to bottom each team based on their records. I would manually input the record and from there would like to run a macro to automatically rank the team in their perspective bracket from 1 to 10 for Contact and 1 to 9 for CO-ED. I have attached the workbook I am doing this on. Any suggestions would be helpful. Tried researching online but couldnt find much that fit my parameters.

Thanks!!

Aussiebear
07-22-2010, 11:02 AM
Which teams fall into Contact or CO-ED? And is the ranking based on Wins plus For & Against points?

j19_2002
07-22-2010, 11:22 AM
On the attached workbook, the teams are shown in their respective league below the schedule. But here they are:

Co-Ed
Guaranteed
Cowboys & Cowgirls
Adrenaline
Fear This
Primetyme
Dallas Allstars
Sho-Nuff
All Madden
Run DFW


Contact
Beast Mode- Speed Kills
Cowboys
Hotboys
Take Over
Knights
Warriors
DPT
Blue Chips
Blue Flames
Dream team

As far as the ranking I was thinking basic win/loss ranking.
But if it comes to be a tie in the ranking before playoffs than yes for and against points would come in handy but it's not necessary.

Bob Phillips
07-22-2010, 03:15 PM
=RANK(D40,$D$40:$D$49)

and

=RANK(D55,$D$55:$D$63)

j19_2002
07-22-2010, 03:42 PM
Isn't that only looking at the Wins though?

I tried this and it seems to work, would you please let me know if this logic is correct....or could there be a flaw later in the future.
=SUMPRODUCT((D40-E40<D40:49-E$40:E$49)+0)+1

Thank You!!

Bob Phillips
07-22-2010, 04:26 PM
As you only have wins and losses, what is wrong with just looking at the wins? The losses are immaterial.

j19_2002
07-22-2010, 05:12 PM
Well because sometimes teams dont play and there are always make up games. So during the week if just counting the wins the ranking would be off until the game is made up which could be until the end of the season.

I think I figured it out though. Thanks for your help!

If I have any issues of course I'll come back to ask =)

Bob Phillips
07-23-2010, 12:41 AM
I looked at your results uisng your formula, and it ranks a team that hasn't played at all the same as a team that has played 2 games and won one, lost one, and similarly a team with a 2-1 record is only as good as a team with a 1-0 record. That seems perverse to me.

j19_2002
07-23-2010, 09:08 AM
Yea I noticed that too, didn't notice until last night when I got home and really looked at it. But just looking at the wins also has the same effect.

If a team hasnt played all their games and is 1-3 but another one is 1-5
based on just looking at wins they would be ranked the same. Its just like the problem I am having now with my perverse code but in reverse.

Any suggestions?? Or am I going to have to do this manually every week...

Bob Phillips
07-23-2010, 09:40 AM
Well here is an option that I think is better

=IF(SUM(D40:E40)=0,NA(),SUMPRODUCT(--(D$40:D$49+E$40:E$49<>0),--(D40*2-E40<D$40:D$49*2-E$40:E$49))+1)

j19_2002
07-23-2010, 02:19 PM
Oh man!! That worked wonderfully!! and so much better than what I came up with.

Thank You XLD!!!