The women’s World Cup is currently underway in Canada and at the time of writing have just finished the group stages of the competition.
This blog post provides a Women’s World Cup Schedule spreadsheet to automatically calculate the group stage rankings, and knockout stage schedule as the results are entered.
Download the Womens World Cup spreadsheet.
This spreadsheet is ready for use. All you need to do is enter the fixtures and results and it will take care of the rest.
What’s in the Workbook
The workbook is made up of 7 worksheets. Some of the worksheets are hidden as they are not needed when using the workbook. You can unhide these sheets by right clicking a sheet tab and selecting Unhide.
The following worksheets can be found in this World Cup schedule.
Teams – Stores the list of teams competing in the competition split into their groups.
Data – The number of points awarded for a win and draw in the groups stages.
Group Fixtures – The fixtures list and results during the group stages of the competition.
Calculations – Calculations for the group stage league table statistics such as number of wins, draw, losses goals for etc. It also calculates the teams league table rankings.
Third Place Rankings Table – Used to calculate the four best third placed teams during the group stages. It also calculates which teams they will face in round 16. This was quite complicated calculations and an explanation of how the schedule works can be seen below.
Group League Tables – The final group stage league tables. The VLOOKUP function is used to pull the information from the Calculations sheet, and use the ranking to list the teams in the correct order.
Knockout Stages – The fixtures and results for the knockout stage of the tournament. Formulas are used to determine which team progresses to the next round as results are entered.
How the Women’s World Cup Schedule Works
The first round is a group stage. Twenty four teams are divided into six groups of four teams. Each team plays one match against the other teams of the group. The group winners and runners-up progress along with the best four third-placed teams.
The ranking for each team in the group is determined by points, then by goal difference and then by goals scored.
The spreadsheet also contains a table to calculate the best four third-placed teams. They are ranked in their own table using the same criteria.
This is similar to other sports tournament schedules I have created in Excel. You can learn all the skills used for this in our online course.
Scheduling the Best Four Third-Placed Teams
What really made this tournament schedule exciting to create were the tournament regulations for who the best four third-placed teams play in the next round.
These four teams are to be matched with the winners of groups A, B, C and D. However the group winners they play is dependent on which group the best four teams are from.
The image below of the ‘Third Placed Ranking Table’ sheet shows the rank order of the best four third-placed teams and the group winners they are paired against. This schedule is from section 28 of the tournament regulations.
Row 18 displays the top four third-placed teams. A formula was used in column F to then calculate from this the correct schedule to use. This formula made use of the IF, NOT, ISERROR and FIND functions.
This combination was then extracted to a different area of the sheet using INDEX and MATCH. The ‘Knockout Stage’ schedule sheet then pulled the relevant team name across.
Leave a Reply