Looking to do a sports league total rank sorted/based on - 1.seed, 2. team name, 3. total points

007Mikey

New Member
Joined
Feb 15, 2018
Messages
1
Hello
I created a Microsoft excel sheet for a sports league I play in.
There are 3 divisions the league, each win being worth more points the higher up. Every 3 weeks, the top 4 teams on the top of each division (except the top division) will move up, while the bottom 4 will move down (except the bottom division) – or a re-tier if you will. Exceptions can be made on if there will be 4 or less based on executive decision or team balance reasons. This is done to add balance and more opponents for each team. The season lasts for 10 weeks.
So I have a column of each team for the 3 re-tiers, all at the same level across the board, with a multiplier column for the points, as a win in each tier is different. For example, in Tier 1, a win is worth 3 points. So if a team gets 15 wins, they get 45 points for that week. A win in Tier 2 is worth 2, and Tier 3 is worth 1. So each week, I can key in the wins and I have a formula based on the multiplier to get the total points for that week. Some teams can drop all the way down from Tier 1 to Tier 3 and vice versa, although not too often.
*Note, this chart is fairly big, I’m only showing Tier 1.
So, besides this I’ve done a rank formula for a column at the far right, which has done the job so far. =RANK(M4,$M$4:$M$55). This has helped a lot so far.
***What I wanted to do now is sort the teams on the far right (or the last portion below) for the playoffs based on rank (where they will be seeded), team name, and their final points. This is the last goal I’m looking for so I don’t have to manually cut and paste (or use the equals function) and also key in the final scores. This would take place in the section below marked “Playoff Tiers”
If you see below, you will see the headings, and a row of what a team’s totals would look by. I could do this by week, but due to a re-tier, it’s just easier to wait until it happens, as that would change the multiplier.

So, if there’s a formula that would capture the text of the team names AND sort their rank AND the final point total for the entire season, that’s what I’m looking for. It’s the Rank/Seed, team name, and the points on the far right (or the last portion below) if you look below.
*If you have trouble seeing this chart below, I can e-mail it upon request.

[TABLE="width: 649"]
<tbody>[TR]
[TD]After Week 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]After Week 6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tier 1
[/TD]
[TD]Wins
[/TD]
[TD]Multiplier
[/TD]
[TD]Tier Total
[/TD]
[TD]Tier 1/2
[/TD]
[TD]Wins
[/TD]
[TD]Multiplier
[/TD]
[TD]1st Re-tier
[/TD]
[/TR]
[TR]
[TD]Team A
[/TD]
[TD]23
[/TD]
[TD]3.5
[/TD]
[TD]80.5
[/TD]
[TD]Team A
[/TD]
[TD]43
[/TD]
[TD]3.5
[/TD]
[TD]150.5
[/TD]
[/TR]
[TR]
[TD]Team B
[/TD]
[TD]22
[/TD]
[TD]3.5
[/TD]
[TD]77
[/TD]
[TD]Team B
[/TD]
[TD]37
[/TD]
[TD]3.5
[/TD]
[TD]129.5
[/TD]
[/TR]
[TR]
[TD]Team C
[/TD]
[TD]22
[/TD]
[TD]3.5
[/TD]
[TD]77
[/TD]
[TD]Team C
[/TD]
[TD]40
[/TD]
[TD]3.5
[/TD]
[TD]140
[/TD]
[/TR]
[TR]
[TD]Team D
[/TD]
[TD]21
[/TD]
[TD]3.5
[/TD]
[TD]73.5
[/TD]
[TD]Team D
[/TD]
[TD]41
[/TD]
[TD]3.5
[/TD]
[TD]143.5
[/TD]
[/TR]
[TR]
[TD]Team E
[/TD]
[TD]18
[/TD]
[TD]3.5
[/TD]
[TD]63
[/TD]
[TD]Team E
[/TD]
[TD]40
[/TD]
[TD]3.5
[/TD]
[TD]140
[/TD]
[/TR]
[TR]
[TD]Team F
[/TD]
[TD]18
[/TD]
[TD]3.5
[/TD]
[TD]63
[/TD]
[TD]Team F
[/TD]
[TD]37
[/TD]
[TD]3.5
[/TD]
[TD]129.5
[/TD]
[/TR]
[TR]
[TD]Team G
[/TD]
[TD]17
[/TD]
[TD]3.5
[/TD]
[TD]59.5
[/TD]
[TD]Team G
[/TD]
[TD]42
[/TD]
[TD]3.5
[/TD]
[TD]147
[/TD]
[/TR]
[TR]
[TD]Team H
[/TD]
[TD]17
[/TD]
[TD]3.5
[/TD]
[TD]59.5
[/TD]
[TD]Team H
[/TD]
[TD]35
[/TD]
[TD]3.5
[/TD]
[TD]122.5
[/TD]
[/TR]
[TR]
[TD]Team I
[/TD]
[TD]14
[/TD]
[TD]3.5
[/TD]
[TD]49
[/TD]
[TD]Team I
[/TD]
[TD]29
[/TD]
[TD]3.5
[/TD]
[TD]101.5
[/TD]
[/TR]
[TR]
[TD]Team J
[/TD]
[TD]14
[/TD]
[TD]3.5
[/TD]
[TD]49
[/TD]
[TD]Team J
[/TD]
[TD]26
[/TD]
[TD]3.5
[/TD]
[TD]91
[/TD]
[/TR]
[TR]
[TD]Team K
[/TD]
[TD]12
[/TD]
[TD]3.5
[/TD]
[TD]42
[/TD]
[TD]Team K
[/TD]
[TD]24
[/TD]
[TD]3.5
[/TD]
[TD]84
[/TD]
[/TR]
[TR]
[TD]Team L
[/TD]
[TD]10
[/TD]
[TD]3.5
[/TD]
[TD]35
[/TD]
[TD]Team L
[/TD]
[TD]23
[/TD]
[TD]3.5
[/TD]
[TD]80.5
[/TD]
[/TR]
[TR]
[TD]Team M
[/TD]
[TD]10
[/TD]
[TD]3.5
[/TD]
[TD]35
[/TD]
[TD]Team M
[/TD]
[TD]49
[/TD]
[TD]2
[/TD]
[TD]98
[/TD]
[/TR]
[TR]
[TD]Team N
[/TD]
[TD]8
[/TD]
[TD]3.5
[/TD]
[TD]28
[/TD]
[TD]Team N
[/TD]
[TD]38
[/TD]
[TD]2
[/TD]
[TD]76
[/TD]
[/TR]
[TR]
[TD]Team O
[/TD]
[TD]8
[/TD]
[TD]3.5
[/TD]
[TD]28
[/TD]
[TD]Team O
[/TD]
[TD]32
[/TD]
[TD]2
[/TD]
[TD]64
[/TD]
[/TR]
[TR]
[TD]Team P
[/TD]
[TD]6
[/TD]
[TD]3.5
[/TD]
[TD]21
[/TD]
[TD]Team P
[/TD]
[TD]49
[/TD]
[TD]2
[/TD]
[TD]98
[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 591"]
<tbody>[TR]
[TD]After Week 10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tier 1
[/TD]
[TD]Wins
[/TD]
[TD]Multiplier
[/TD]
[TD]2nd Re-tier
[/TD]
[TD]Grand Total
[/TD]
[TD]Rank
[/TD]
[/TR]
[TR]
[TD]Team A
[/TD]
[TD]42
[/TD]
[TD]3.5
[/TD]
[TD]147
[/TD]
[TD]378
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Team B
[/TD]
[TD]34
[/TD]
[TD]3.5
[/TD]
[TD]119
[/TD]
[TD]325.5
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Team C
[/TD]
[TD]44
[/TD]
[TD]3.5
[/TD]
[TD]154
[/TD]
[TD]371
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Team D
[/TD]
[TD]37
[/TD]
[TD]3.5
[/TD]
[TD]129.5
[/TD]
[TD]346.5
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Team E
[/TD]
[TD]45
[/TD]
[TD]3.5
[/TD]
[TD]157.5
[/TD]
[TD]360.5
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Team F
[/TD]
[TD]34
[/TD]
[TD]3.5
[/TD]
[TD]119
[/TD]
[TD]311.5
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Team G
[/TD]
[TD]37
[/TD]
[TD]3.5
[/TD]
[TD]129.5
[/TD]
[TD]336
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Team H
[/TD]
[TD]34
[/TD]
[TD]3.5
[/TD]
[TD]119
[/TD]
[TD]301
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Team I
[/TD]
[TD]25
[/TD]
[TD]3.5
[/TD]
[TD]87.5
[/TD]
[TD]238
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Team J
[/TD]
[TD]23
[/TD]
[TD]3.5
[/TD]
[TD]80.5
[/TD]
[TD]220.5
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]Team K
[/TD]
[TD]24
[/TD]
[TD]3.5
[/TD]
[TD]84
[/TD]
[TD]210
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]Team L
[/TD]
[TD]17
[/TD]
[TD]3.5
[/TD]
[TD]59.5
[/TD]
[TD]175
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]Team M
[/TD]
[TD]26
[/TD]
[TD]3.5
[/TD]
[TD]91
[/TD]
[TD]224
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]Team N
[/TD]
[TD]14
[/TD]
[TD]3.5
[/TD]
[TD]49
[/TD]
[TD]153
[/TD]
[TD]19
[/TD]
[/TR]
[TR]
[TD]Team O
[/TD]
[TD]27
[/TD]
[TD]2
[/TD]
[TD]54
[/TD]
[TD]146
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]Team P
[/TD]
[TD]34
[/TD]
[TD]3.5
[/TD]
[TD]119
[/TD]
[TD]238
[/TD]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 440"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Playoff Tiers Seed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tier 1
[/TD]
[TD]1
[/TD]
[TD]Team A
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]378
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]2
[/TD]
[TD]Team B
[/TD]
[TD][/TD]
[TD][/TD]
[TD]371
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]3
[/TD]
[TD]Team C
[/TD]
[TD][/TD]
[TD][/TD]
[TD]360.5
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]4
[/TD]
[TD]Team D
[/TD]
[TD][/TD]
[TD][/TD]
[TD]346.5
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]5
[/TD]
[TD]Team E
[/TD]
[TD][/TD]
[TD][/TD]
[TD]336
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]6
[/TD]
[TD]Team F
[/TD]
[TD][/TD]
[TD][/TD]
[TD]325.5
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]7
[/TD]
[TD]Team G
[/TD]
[TD][/TD]
[TD][/TD]
[TD]311.5
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]8
[/TD]
[TD]Team H
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]301
[/TD]
[/TR]
[TR]
[TD]Tier 2
[/TD]
[TD]9
[/TD]
[TD]Team I
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]238
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]10
[/TD]
[TD]Team J
[/TD]
[TD][/TD]
[TD][/TD]
[TD]238
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]11
[/TD]
[TD]Team K
[/TD]
[TD][/TD]
[TD][/TD]
[TD]224
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]12
[/TD]
[TD]Team L
[/TD]
[TD][/TD]
[TD][/TD]
[TD]220.5
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]13
[/TD]
[TD]Team M
[/TD]
[TD][/TD]
[TD][/TD]
[TD]210
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]14
[/TD]
[TD]Team N
[/TD]
[TD][/TD]
[TD][/TD]
[TD]175
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]15
[/TD]
[TD]Team O
[/TD]
[TD][/TD]
[TD][/TD]
[TD]164.5
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]16
[/TD]
[TD]Team P
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]163
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top