Create an Excel Football League Table
I love undertaking little projects in Excel, especially ones that interest me. A couple of weeks ago I set about creating an Excel Football League Table, and here it is.
The league table calculates a team’s position in the league as the results are entered. The table consists of 5 teams. Expect some related hubs over the coming weeks as the league table grows.
The Excel workbook is made up of the following sheets:
Fixtures – This sheet contains the fixtures for the season. The results are entered onto this sheet after each game and the league table and fixtures sheet for each team update themselves.
Team Sheets – These sheets contain the fixtures for the teams in the league. The results are entered automatically from the Fixtures sheet.
Data – This sheet contains the number of points earned for a win and for a draw. Change the data here to configure how the table is calculated.
Calculations – This sheet performs the calculations from each team sheet and ranks the teams for the league table.
League Table – The league table updates from the Calculations sheet.
Download the Excel Football League Table.xlsx to go through as you read this hub.
Calculating the League Data
All the working out happens on the Calculations sheet. First of all I needed to find out how many wins, draws, losses each team had so that their points total could be calculated. The following Excel functions can be found in the worksheet.
COUNT – used to count the number of games each team has played.
COUNTIFS – used to count the number of wins, draw and losses for each team at home and away. The COUNTIFS function is new to Excel 2007. In previous versions the SUMPRODUCT function should be used.
SUMIF –used to add up the number of goals scored and number of goals conceded by each team.
League Position on Points
The points are then calculated by multiplying the number of wins and draws by the data on the Data sheet.
The next step was to give each team a position based on their points total. This was done using the RANK function. The RANK function returns the rank of a number within a set of numbers, therefore returning a teams league position dependent upon their points total.
However, the RANK function returns the same rank for teams with the same number of points. So I needed to include the team’s goal difference.
League Position after Goal Difference
To include the goal difference of each team I decided to use the SUMPRODUCT function. This is an incredibly powerful and useful function that can be used to test multiple conditions and return a result on how many records met the required conditions.
In the league table spreadsheet the SUMPRODUCT function ran a test on what teams had the same points total, and of those that did, how many teams had a greater goal difference.
This effectively provided a goal difference ranking for teams that had the same points total.
League Position after Goals Scored
A third condition ranking was added to handle the situation where teams also had the same goal difference.
Once again the SUMPRODUCT function was used. This tested to see which teams had the same points total, and the same goal difference, and if so found out how many teams had scored a greater number of goals.
This provided me with a goals scored ranking.
The Final League Table
With the three rankings set up, it was time to calculate the teams overall league table position. This was performed by simply adding the rankings together:
=Points Ranking + Goal Difference Ranking + Goal Scored Ranking
On the League Table sheet the following functions were then added to calculate each team’s position throughout the season based on the data in the Calculations sheet.
VLOOKUP – used to look up and return the required data from the Calculations sheet such as team name, matches played, wins etc.
MIN – used to find the team in first place. It looks for the minimum number in the Rank column
SMALL – used to return the appropriate team and league data in the right order from teams 2-5 dependent upon their ranking