Creating Sports League Tables and Tournaments in Excel

Formatting ranges as tables can greatly simplify the way we use them in formulas. This lesson will introduce you to this technique and demonstrate some of the benefits.

This lesson will show you how to use structured references (the name given to table data references) in formulas. We will do a lot of them during the course, so this video is an introduction to what they look like and how to use them.

This guide to the SUMPRODUCT function will show you why I love this function so much, and why it forms a central part of the league table calculations. This lesson will get you up to speed with this amazing function quickly.

In this lesson we look at setting up the tables required to create our league table. This lessons covers;

• Formatting our data into tables for dynamic and structured references.
• Namimg the tables to make referencing them from formulas easier.

In this lesson we begin calculating data from the fixture list beginning with how many games each team has played, won and lost. The SUMPRODUCT function is used to perform these calculations.

In this lesson we continue with our calculations from the fixtures sheet. Up next is to calculate the number of games drawn and the number of goals scored and conceded by each team.

In this lesson we calculate how many points each team has accumulated and also the goal difference for each team. This information will be used for our league rankings.

In this lesson we perform the first ranking in our league table. The RANK function is used to determine each team's league position dependent on the number of points they have accumulated.

In this lesson we look at uniquely ranking the teams. If more than one team has the same number of points, the RANK function does not assign a unique rank, instead it skips the number.

The COUNTIFS function is used here in a clever formula to assign a unique ranking if teams are tied on points.

In this lesson we look at applying additional ranking criteria to determine the league position of a team. In this example we look at using points earned, then goal difference and then number of goals scored to determine a unique ranking for each team in the league.

In this lesson we look at including a points deduction. A points deduction may be applied if a team breaks a rule such as fielding an ineligible player, not turning up for a match or break a fair play rule.

A column is created so that any points deductions can easily be added in during a league season.

In this lesson we look at creating the league table. The previous lessons have focused on calculating data from the fixtures sheet and then determining each team or players ranking.

The league table is created by using a VLOOKUP function on the calculations table to display the teams or players in the correct order and with the information we wish to display.

In this lesson we re-enforce the techniques shown with Rugby Union league table example.

In the Aviva Premiership in England teams are ranked by points, then number of wins, then points difference, then most tries, then most game points and then tries difference.

The video also covers how to calculate number of tries scored and bonus points collected.

In this lesson we look at setting up the spreadsheet for our sports knockout tournament. We look at setting up a table for each round of the competition, and format them in a table format.

This video looks at randomising the cup draw by using the RAND function. A random number is generated and assigned for each player. This number will be used to pull the player name into the tournament draw in a random order.

The video also covers how to turn off automatic calculations so that we can control when the formulas calculate.

In this video we look at assigning an index number to each player or team competing in the tournament. This index number is based on the previous randomly generated number. The RANK function is used to create this unique index number.

In this video we look at using the VLOOKUP function to populate the cup draw sheet based on the work from the previous two videos. The VLOOKUP function will search for the index number and return the associated player or team name.

In this video we look at creating a button on the worksheet and then writing some macro code to assign to the button. The macro code is used to calculate the formulas from the previous three videos.

A button is created for each round of the draw. This button then works as a simulation. When clicked it will perform the cup draw.

In this video the IF function is used to calculate the results from a round of fixtures so that the winning player or team name can be progressed to the next round.

In this video we create another button on the worksheet that will reset or clear the tournament data ready for the next tournament.

This will require removing all tournament results, changing some text font colour to hide it and calculating specific formulas.

Simple macro code is written to perform these actions and assigned to the button.

In this lesson we demonstrate another knockout tournament example to show how the techniques from the previous lessons can be adapted to your needs.

A tennis grand slam tournament is used where games are played as the best of 5 sets. Therefore our formulas need to be altered to handle the new criteria for how to calculate which player has progressed to the next round of the tournament.

In this lesson we get an overview of the World Twenty20 Cricket Tournament spreadsheet. This includes;

• The different sheets in the workbook and the purpose of each.
• How games statistics are entered and results calculated.
• How to enter alternative methods for a results such as No Results, Duckworth-Lewis Method and Super Over.
• How rankings are determined for the league tables in the two group stages of the tournament.

In this lesson we look at how to calculate the total runs for and against for each team in the group stages of the tournament. It is the first step in calculating the Net Run Rate for each team.

The SUMPRODUCT function is used. Conditions are applied to take into account the Duckworth-Lewis Method for the team batting first.

In this lesson we create a revised overs column. When calculating the total overs bowled and faced by each team in the tournament's group stages, there are certain tests to perform.

This lesson includes;

• Checking if the game was a No Result, and if so applying 0 overs faced for the batting team.
• Checking if the batting team was bowled out, and if so applying 20 overs faced.

In this lesson we conclude looking at how to calculate the total overs bowled and faced by each team in the group stages of the tournament.

This lesson includes;

• Learning how to calculate the total overs as a fraction to then be used to calculate NRR.
• Using the INT and MOD functions.
• Effectively calculating total overs despite complications brought about by overs consisting of 6 balls.
• Rounding an answer to 2 decimal places.

In this lesson we calculate the Net Run Rate for each team in the group stages of the tournament based on data from the previous videos.

NRR is calculated by using the Runs For/Overs Faced - Runs Against/Overs Bowled formula. The result is rounded to 3 decimal places.

In this lesson we look at calculating the total balls bowled by each team at the group stages of the tournament. This is the first step to calculating the bowling strike rate for each team.

We look at how to use the overs bowled data and convert it into total balls bowled using the SUMPRODUCT, INT and MOD Functions.

In this lesson we continue our work on calculating the bowling strike rate for the teams competing in the group stages of the tournament.

We look at calculating the wickets taken by each team during the group stages. And then we calculate the bowling strike rate using the Balls Bowled / Wickets Taken formula. The result is rounded to 2 decimal places.