In the world of sports, ranking teams on overall performance can sometimes be a complex process. It's not just about wins and losses; both strength of schedule and margin of victory can also play a significant role. Let us explore a simple algorithm using Excel for ranking sports teams based on their match results (with a 'nod' to Ramanujacharyulu (1964)).
To illustrate the algorithm, we will use the Rugby Union Six-Nations Championship (2023) results (only six teams and fifteen results).
| No | Step |
|---|---|
| 1. | Load CSV results listing file into cell range $B$5:$K$20 in worksheet. |
| 2. | Define four named ranges: HomeTeams ($D$6:$D$20), AwayTeams ($E$6:$E$20), HomeScores ($F$6:$F$20), and AwayScores ($G$6:$G$20) (use column values, not headers). |
| 3. | Create a score-differences table ($B$22:$I$29) below the results listing with both row and column headers in the same alphabetical order. Also, add row sums ($I$22:$I$29) and column sums ($B$29:$H$29). |
| 4. | In the cell England-England ($C$23) enter the formula:
=IFERROR(SUM(INDEX(HomeScores,MATCH(1,(HomeTeams=$B23)*(AwayTeams=C$22),0)),-INDEX(AwayScores,MATCH(1,(HomeTeams=$B23)*(AwayTeams=C$22),0))),0)
|
| 5. | Drag the formula from $C$23 down to $C$28 and then across to $H$28. |
| 6. | Sort table ($B$31:$I$37), using the 'Row Sums' column, in descending order. |
| 7. | Using cell $B$40 as the anchor, enter
=TRANSPOSE($B$31:$H$37) in the formula bar and press
<Return>. |
| 8. | Sort table ($B$40:$I$46), using the 'Row Sums' column, in ascending order. |
| 9. | Using cell $B$58 as the anchor, enter
=TRANSPOSE($B$49:$H$55) in the formula bar and press
<Return>. |
| 10. | Our near-optimal (sometimes, optimal) final ranks are in cells $B$59:$B$64. |
Note that step 6. sorts the teams by 'margin of victory' and, subsequently, step 8 sorts them by a proxy for 'strength of schedule' thereby giving us a near-optimal set of final rankings.
Enjoy!