Creating a multi group football tournament in Excel

Creating a multi group football tournament in Excel

As June and July are World Cup season I thought this would be a great opportunity to create a 24 team multi stage football tournament from Excel.  The main difference in my tournament and the world cup is that Spain and Germany are not going home early.

So the question is how do we randomly allocate 24 teams into four different groups for the firsts stage of the competition?

Step 1

Best practise is to put data into Table format for numerous reasons.  Once the data has been added, highlight then navigate to the “Insert” tab and select “Table”.

Step 2

The logical first step is to allocate a random number to each team this way they all have a unique identifying number.  Obviously we would hope that they all have unique names but by using a random number we are able to order these numbers at a later date.

football in excel

I have used Countries (again in honour of the world cup)  and have used the following function to assign them a random number.

=RAND().

Once you have added the function for each team be sure to copy and paste text to fix in the number.  If you fail to do this and retain the formula in the cells then the random number will change every time Excel process a change to the worksheet.

Step 3

The next step is to order the teams based on the random number they have been ascribed.  The RANK() function can be used to rank the teams based on the random numbers.

=RANK(CELL, ARRAY,1)

=RANK(Cell to rank, from these cells, 0: descending order or 1: asecending order)

For our example

=RANK(B2,B2:B25,1)

As we put the data into a table the formula will be copied down to all active rows.

excel football

So we now have a rank for the 24 teams and we need to use this rank to create the groups.  As we have 24 teams in the completion it makes sense to have four groups of six teams.  As with so many problems in Excel there are numerous solutions and ways to achieve the final result.

Option 1 – Using IF functions

The first way to achieve the grouping of the teams is to use a nested IF function to group together all teams in a certain bracket.

=IF(C2>18,4,IF(C2>12,3,IF(C2>6,2,1)))

The above formula is written to group the ranked data in the following manner

football 3

When the IF function is in place the data looks like this:-

excel football tourney

Clicking into the table and navigating to Insert Pivot we are then able to produce a simple pivot that lists the groups.

football 5

As we can see above there are 6 teams split evenly over the 4 groups.

Using the CEILING FUNCTION

The ceiling function is likely to be a function with which you are unfamiliar; I know I was until I looked into creating this football tournament.  The ceiling function is however a nice little function in it’s own right.

The ceiling function rounds a number to the nearest significant number you prescribe.  There is an additional step in this method but the overall time taken is very similar to the method above.

Returning to the ranked data

football 6

A further column should be added with the heading Group, in this column I am dividing the rank by 6, as we have decided to have groups of 6 teams. A second column is then used with the Ceiling function to round the data to the nerest significant figure.  In this case the nearest significant figure is 1.

=CEILING(D2,1)

Doing so provides a number between 1 and 4 which we will use to group the teams.

football 7

Once more we can now use this table to produce a quick pivot to show how the groups have been made.

excel skills

I hope you enjoyed this little tutorial and hopefully it will serve you well next time you are organising an amateur sporting event.

Leave a Reply