S.C.S.L. — How it works (2)

Now that the fixture list has been created, we need to be able to see how the teams are performing once we start playing the game, so we have to have a league table. There are simple ways of creating one but in order to have full realism and a properly ordered table we have to put a bit of work in. The first thing we need is a way of finding out the results and converting these into the usual statistics shown in a league table, and then we need to put the data into the correct order for display.

Creating the League Table

To collate all the values needed for a league table I use a hidden sheet called "Table_Calc". Here is a screenshot of this sheet.

As you can see the sheet is divided into sections relating to the different data that needs to be calculated. The parts that collect the results data are held in the purple "Home" and blue "Away" sections. I have selected a cell in the "Home" section, which is the cell for the total of home wins for the first team in the list, in this case "Celtic", so that we can see the formula that is used. The function used in this case is the countifs formula, which works by counting the number of cells in a range that satisfy more than one criterion. The first two parameters relate to the first criterion, which is any fixture where the home team is "Celtic". The named range HomeTeams1 refers to the cells in column 'D' on the "Fixtures" sheet, which contains the home team names for each fixture, which is the range D5:D405. The second parameter, "="&Table_Calc!$D4 is the criterion, and specifies any cell in the range defined by HomeTeams1 that equals the text in cell 'D4' on the "Table_Calc" sheet, i.e. "Celtic".

The third and fourth parameters relate to the result of the fixture concerned. The parameter HomeRes1 is a named range referring to cells that hold the result of the fixture concerned, so for example, for the fixture in row '5' of the "Fixtures" sheet there is a cell that has the formula =if(or(E5 = "",F5 = ""),"",if(E5 > F5,3,if(E5 = F5,1,if(E5 < F5,0)))), which basically inserts a '3' in the cell if the home score (E5) is greater than the away score (F5), a '1' if these values are equal and a '0' if the home score is less than the away score. The last parameter, "=3" picks out the cells where this value is '3'. So, the cell will show the number of times that the value in column 'D' is "Celtic" and the value in the result column is '3', which in this cell gives the value of '9', i.e. Celtic have had 9 home wins. The remaining cells for home draws, home losses, home goals for, home goals against and the same data for away results are calculated in the same way, and these are then totalled up in the pink "Overall" section, which holds the basic data needed for the league table.

Getting the data in the correct order

The table created contains all the data that we need to display the table, but it is not in the correct order. To do this we need to rank the teams according to their points, goal difference and goals scored, in that order. The following screenshot shows a cell selected with the formula to perform this calculation shown in the formula bar, which is made up of a complex combination of the rank and sumproduct functions. The inital rank function merely ranks the team according to column 'M', i.e. the points total. The three sumproduct functions add further criteria to the formula to additionally rank a club by goal difference or goals scored if any of the previous criteria are equal for more than one club.

We now have a list of values from 1 to 12 representing league positions, but there is still a remote possibility that two or more clubs could have identical records, in which case we would end up having two or more rank numbers the same, and one or more that are missing. To resolve this, albeit remote, possibility a second column, is needed to add a second formula to prevent this from happening. The formula in this column merely uses the default order of the entries on the sheet to ensure that each value is unique. The formula that is used to do this is =$B4+COUNTIF($B$4:$B4,$B4)-1 (when this formula is copied down into the other cells in the column, the $B4 reference increments to $B5, $B6 etc). This column is the one that we use to display the data for the league table in the correct order on the "Table" sheet, and is the column highlighted in orange headed "Adj".

Displaying the League Table

To display the league table we use the "Table" tab, screenshot of which is shown below.

On this sheet it is important that the league positions numbered from 1 to 12 are shown in the left-hand column. This not only aids in reading the table, but is also used to pull the data required from the "Table_Calc" sheet using, yes you've guessed it, the vlookup formula. For illustration, the formula for the first team's total of wins is shown, and as you can see the first parameter gets the position number from the left-hand column. This is then used to index the appropriate row from the cells in the named range LEAGUE_POSITIONS, i.e. C4:AA15 on the "Table" sheet. The third parameter is '5', which pulls the value from the fifth column in the range, i.e. column 'G', which is the column for the total number of wins for each club. By using this formula for the remaining cells we can display the table with the teams in the correct order.

Next, we will look at how the table of results is created for easy reference to the results for each club. Click the following link to continue: How it works (3)

Back to Home Page