Find a Teams League Position
Once the league table calculations have been done. We then need to write formulas to rank the teams in the league table in Excel.
The final league position of the teams will take into account:
- Their points total
- Goal difference
- Goals scored
Download the EnglishPremierLeague1.3.xlsx spreadsheet to follow along.
Rank the Teams in a League Table
The RANK function will be used to rank the teams in the league table.
The function below needs to be entered into cell V3 of the Calculations sheet and then copied down.
The function finds the ranking of the points total of that row in the cell range U3:U22. U3 is a relative reference so that it changes when we copy the function down. The other reference is absolute.
The Order argument of the RANK function has been omitted so that the ranking returned is in descending order.
Rank the Teams by Goal Difference
If more than one team have the same ranking, they need to be sorted by goal difference.
We will use the SUMPRODUCT function to find out if two teams have an equal ranking, and then adds a 1 to the team(s) with the greater goal difference.
The function below should be entered into cell W3 and then copied down.
Position the Teams by Goals Scored
If more than one team also share the same goal difference, we need to position the teams by goals scored.
The SUMPRODUCT function below will find out what teams have an equal points total, and an equal goal difference and then add a 1 to the team(s) with the lower number of goals scored.
Enter the function in cell X3 and copy it down.
The image below shows the formulae entered. Note Rank 16 being further ranked by goal difference and then by goals scored when there are ties.
Find the Final League Position of Teams
Now that we have built up these rankings, finding the final league position of the teams can be achieved by entering the formula below in cell B3 and copying it down.
This formula adds the teams points total, to the goal difference total, to the goals scored total.
The teams with the higher goal difference, or lower number of goals scored, ended with a higher total putting them further down the league table.
Remove Duplicates from the League Table
Before the season begins the league table will display Arsenal in every position. This is because before a ball is kicked every teams stats are the same e.g. 0 games, 0 wins etc.
This produces duplicates as every team has the same number of points, GD and GS. So the table will show the first team in every position.
We will enter the function below in A3 of the Calculations sheet and copy it down to remove the duplicates.
Download the completed spreadsheet with all of the formulas above entered.
Popular Excel Tips