Fixtures List and League Table

 

Introduction

If you ever read sports magazines and the sports pages in various newspapers you will no doubt have seen those tables that show an entire league’s fixtures in just one table. For example, in a league such as the Barclays Premier Football League in England there are twenty teams and the fixtures tables is a 20 by 20 matrix.

 

Then there’s the league table: the positions and performance of every team in the league or division: again in terms of the Barclays Premier Football League that can be at least a 20 by 6 matrix,

 

The question is, how can these two tables or matrices be prepared using Excel 2007? Well, that’s what this page is here to tell you.

Fixtures List: using a Pivot Table

 

This section assumes you have read part one of the book Excel 2007 with Excel Master and chapter 4 in part two.

 

Pivot Tables are so flexible that they can be used for myriad things and they can be prepared from the simplest of tables or databases. A fixtures list is living proof of the simple origins of a Pivot Table.

 

You can download the Excel 2007 file on which this page is based from the link at the end of this page to see how we have done what we have done.

 

Here is the screenshot of the assumed fixture list of the Football League:

sporting 1

This is all we need:

  • date
  • home team and
  • away team

 

that’s it. Here is a screenshot of the fixtures table:

 

sporting 2

This table is just part of the entire table and we have not simulated the entire season, hence the gaps in the table.

How did we prepare the fixture list? Like this:

 

sporting 3

Complete the listing of dates, home and away teams ... it does not even need to be in chronological order
With your cursor in your table:

Insert ... Tables ... Pivot Table

Ensure Excel 2007 has selected the correct range for you
Click OK

You are now presented with your blank Pivot Table and the Pivot Table Field List

Select Date and Home Team and Away Team and arrange them as follows:

  • The away team is in the Column Labels area
  • The home team is in the Row Labels area
  • The away team is in the Values area

 

That’s it except that you probably need to click once on Sum of Date to format it ... bear in mind it might say Count of Date by default and you need to change that too.

 

That’s all there is to it. If a fixture’s date changes, you can just change it in the source table then click Options Refresh.

If you have added some fixtures or even deleted them, you would also have to click Options ... Change Data Source ... Change Data Source and then make the necessary changes.

 

Look at the Excel file now: look in the for_pivot and pivot_fixtures sheets. Note the links between these two sheets and the input sheet.

League Tables: programming with Excel 2007

 

A league table should tell us who leads the league, the number of games they have played and won and drawn and lost and the number of points they currently have. There are other aspects to some league tables but this is what we are demonstrating here. This table and all of the formulas within it are to be found in the Excel 2007 file on which this page is based: the link to that file can be found at the bottom of this page.

The Data

 

Whilst the data for the league table is basically the same as for the fixture list, there is a vital addition: now the games have been played and won, drawn or lost ... the basic table is now found in the for_table_1 work sheet in the Excel 2007 file. The table is on the same sheet, purely for demonstration purposes.

 

We have actually prepared two versions of the league table: using the same data preparing the same table but doing so in a different way. The following screenshot relates to version one in the for_table_1 work sheet.

 

sporting 4

We have added the home and away scores and have also shown the points won by the home and/or away team. This information is the basis of the league table.

League Table 1

 

sporting 5

A traditional league table you should agree and here are some of the formulas used: again take a look at the spreadsheet for this page to study carefully what we have done:

 

sporting 6

Note we have used both =COUNTIF(...) and =COUNTIFS(...) functions in preparing this table and you need to study them to see what we have done and why. Note also that =COUNTIFS(...) is new to Excel 2007.

Before you take a look at the second version of this league table, you should consider how you might prepare the table differently.

League Table 2

 

Firstly, the basic data sheet is different from league table 1:

 

sporting 7

In this case we have prepared additional columns from the basic results data: we have added columns to say whether the fixture resulted in

  • a home win (1 = yes and empty = no)
  • an away win
  • an NS (no score) draw
  • a score draw

 

Providing these additional columns means that the league table is prepared on a different basis as you can see now:

 

sporting 8

sporting 9

Yet again see the Excel 2007 file to see the other columns in for_table_2 that are missing from this page. Notice that we have used =IF(AND(...)), =COUNTIF(...) and =SUMIFS(...) functions and remember that =SUMIFS(...) is new to Excel 2007.

 

Conclusions

 

Whilst fixture lists and league tables are everyday tables that we are all used to seeing on a regular basis, preparing them in Excel 2007 does encourage us to use some basic Pivot Table programming for the fixture list and some relatively sophisticated programming for the league table. Moreover, we have demonstrated two methods for preparing the league table both of which, of course, give the same solution as each other but they do so in completely different ways.

 

 

 

© Duncan Williamson
August 2009

 

Download this page in PDF Format
Download the Excel 2007 file on which this page is based