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.
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
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.
| After Week 2 | After Week 6 | ||||||
| Tier 1 | Wins | Multiplier | Tier Total | Tier 1/2 | Wins | Multiplier | 1st Re-tier |
| Team A | 23 | 3.5 | 80.5 | Team A | 43 | 3.5 | 150.5 |
| Team B | 22 | 3.5 | 77 | Team B | 37 | 3.5 | 129.5 |
| Team C | 22 | 3.5 | 77 | Team C | 40 | 3.5 | 140 |
| Team D | 21 | 3.5 | 73.5 | Team D | 41 | 3.5 | 143.5 |
| Team E | 18 | 3.5 | 63 | Team E | 40 | 3.5 | 140 |
| Team F | 18 | 3.5 | 63 | Team F | 37 | 3.5 | 129.5 |
| Team G | 17 | 3.5 | 59.5 | Team G | 42 | 3.5 | 147 |
| Team H | 17 | 3.5 | 59.5 | Team H | 35 | 3.5 | 122.5 |
| Team I | 14 | 3.5 | 49 | Team I | 29 | 3.5 | 101.5 |
| Team J | 14 | 3.5 | 49 | Team J | 26 | 3.5 | 91 |
| Team K | 12 | 3.5 | 42 | Team K | 24 | 3.5 | 84 |
| Team L | 10 | 3.5 | 35 | Team L | 23 | 3.5 | 80.5 |
| Team M | 10 | 3.5 | 35 | Team M | 49 | 2 | 98 |
| Team N | 8 | 3.5 | 28 | Team N | 38 | 2 | 76 |
| Team O | 8 | 3.5 | 28 | Team O | 32 | 2 | 64 |
| Team P | 6 | 3.5 | 21 | Team P | 49 | 2 | 98 |
<tbody>
</tbody>
| After Week 10 | |||||
| Tier 1 | Wins | Multiplier | 2nd Re-tier | Grand Total | Rank |
| Team A | 42 | 3.5 | 147 | 378 | 1 |
| Team B | 34 | 3.5 | 119 | 325.5 | 6 |
| Team C | 44 | 3.5 | 154 | 371 | 2 |
| Team D | 37 | 3.5 | 129.5 | 346.5 | 4 |
| Team E | 45 | 3.5 | 157.5 | 360.5 | 3 |
| Team F | 34 | 3.5 | 119 | 311.5 | 7 |
| Team G | 37 | 3.5 | 129.5 | 336 | 5 |
| Team H | 34 | 3.5 | 119 | 301 | 8 |
| Team I | 25 | 3.5 | 87.5 | 238 | 9 |
| Team J | 23 | 3.5 | 80.5 | 220.5 | 12 |
| Team K | 24 | 3.5 | 84 | 210 | 13 |
| Team L | 17 | 3.5 | 59.5 | 175 | 14 |
| Team M | 26 | 3.5 | 91 | 224 | 11 |
| Team N | 14 | 3.5 | 49 | 153 | 19 |
| Team O | 27 | 2 | 54 | 146 | 24 |
| Team P | 34 | 3.5 | 119 | 238 | 9 |
<tbody>
</tbody>
| Playoff Tiers Seed | |||||
| Tier 1 | 1 | Team A | | | 378 |
| | 2 | Team B | 371 | ||
| | 3 | Team C | 360.5 | ||
| | 4 | Team D | 346.5 | ||
| | 5 | Team E | 336 | ||
| | 6 | Team F | 325.5 | ||
| | 7 | Team G | 311.5 | ||
| | 8 | Team H | | | 301 |
| Tier 2 | 9 | Team I | | | 238 |
| | 10 | Team J | 238 | ||
| | 11 | Team K | 224 | ||
| | 12 | Team L | 220.5 | ||
| | 13 | Team M | 210 | ||
| | 14 | Team N | 175 | ||
| | 15 | Team O | 164.5 | ||
| | 16 | Team P | | | 163 |
<tbody>
</tbody>
