Calculate the League Table Statistics
The Calculations sheet will perform all the working out for presentation to the League Table sheet. We need to calculate number of games won, goals scored etc.
Download the completed spreadsheet up to this point.
Number of Games Played
To calculate the number of games played in cells D3:D22, you will need the COUNT function. The example below shows adding up the games played for Aston Villa.
Games Won, Lost and Drawn
The COUNTIFS function should be used to count the number of games each team has won, drawn and lost at home and away. COUNTIFS can handle both conditions.
The example below shows the COUNTIFS function counting the number of home wins for Arsenal.
Add Goals Scored and Conceded
To add the goals scored and conceded by each team, we will use the SUMIF function.
The SUMIF function is used to add all the values that meet a condition. The example below shows the number of goals scored at home by Arsenal.
It looks in column B of the Arsenal sheet for "H" to represent a home game and adds the value in column C.
Work out the Goal Difference
The goal difference for home and away games can be calculated by subtracting the goals against from the goals scored.
To find out the overall goal difference, add the home and away goal differences together.
Calculate the Points Total for each Team
To calculate the points total for the home and away games:
- Multiply the number of wins by the cell storing the number of points awarded for a win on the Data sheet.
- Multiply the number of draws by the cell storing the number of points awarded for a draw on the Data sheet
- Add the two together
The example below shows a formula for the above. The formula calculates the points earned at home by Arsenal.
- E3 holds the number of wins
- B2 holds the points earned for a win
- G3 holds the number of draws
- B3 holds the points awarded for a draw
To find the overall points total, add together the total points earned at home and the total earned from away games.
Download the completed spreadsheet with all of the formulas above entered.
Popular Excel Tips