Enter Results on the Team Sheets
The team sheets will store all the results for each team and if they won, lost or drew. This data can then be used to calculate each teams league position.
The image below shows a capture of the Wigan team sheet. Formulas have been entered to lookup the number of goals scored and conceded by the team in the relevant fixture on the Fixture Grid sheet. Another formula is then entered in column F to calculate if it was a win, lose or draw.
Download the EnglishPremierLeague1.1.xlsx spreadsheet to follow along.
Lookup the Goals Scored and Conceded
A mixture of the formulae mentioned below is needed to extract the correct data from the Fixture Grid sheet. The formulas needed to be changed dependent upon whether you needed the home, or aways teams goals. And then again with the reverse fixtures.
Using the Wigan sheet as an example, the following formula should be entered in C3 to lookup the goals scored by Wigan against Arsenal.
=IFERROR(VALUE(LEFT(INDEX('Fixture Grid'!$A$1:$U$21,MATCH($A$1,'Fixture Grid'!$A$1:$A$21,0),MATCH($A3,'Fixture Grid'!$A$1:$U$1,0)),1)),"")
Let's break this formula down:
- INDEX and MATCH - The INDEX and MATCH functions are used together to create a dynamic lookup formula.
- LEFT - The LEFT function is used to extract the first character from the left of the cells text.
- VALUE - The VALUE function is used to convert the text to a number. This is important for our league tables calculations(number of goals scored, goal difference, etc).
- IFERROR - The IFERROR function is used to prevent the #VALUE! error showing if the fixture has not been played yet.
The MATCH functions are used to find the row number of Wigan, and then the column number of Arsenal on the Fixture Grid sheet.
The INDEX function is used to return the value at the intersection of the row and column numbers returned by the MATCH functions.
The formula is then copied down to save repetition. Absolute cell references are used to hold cell references in place. The reference to the team Wigan are playing (Arsenal) is a mixed reference ($A3) so that it moves in only one direction.
Find out more on using the INDEX and MATCH functions together.
For the away teams, the RIGHT function is used to extract the first character from the right of a cells text.
Calculate the Result of the Games
Continuing with the Wigan sheet as an example we need to calculate the results of the games. The formula below needs to be entered into cell F3. And then modified slightly for the reverse fixtures.
Let's break this formula down:
- LEN - The LEN function is used to return the length of the text in a cell.
- AND - The AND function is used test if the game had been played yet (if LEN returned a length of 0 then the game has not yet been played), and also to test if it was a draw
- IF - The IF functions are used to display W (won), D (drew), or L (lost) as responses to testing the scoreline
Download the completed team sheets spreadsheet with all of the formulas above entered.
Popular Excel Tips