Creating Sports League Tables and Tournaments in Excel
- 3 hours on-demand video
- 2 articles
- 31 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- To automatically calculate sports league table ranking using different ranking criteria
- To calculate random draws for a knockout cup competition
- To calculate statistics for a cricket tournament such as Net Run Rate, total overs or balls bowled and bowling strike rate.
- An basic to intermediate knowledge of Excel
This course shows you how to create spreadsheets for sports tournaments and league tables. Use the power of Excel to automatically calculate cup draws and league table rankings.
Whether your league table rankings are determined by goal difference, goals scored, number of away wins or something else. You will learn how you can use this criteria to automatically determine a team or players league position.
Spreadsheets are available for download along the way for practice, or to use as a template to adapt to your needs.
The course is broken down into three sections.
Creating League Tables
Learn how to automatically calculate league table rankings when results are entered onto a spreadsheet. We look at adapting the league table to apply different ranking criterion. A football/soccer and a rugby union example are used for demonstration.
Create Cup Tournaments
Learn to create cup tournaments in Excel including random cup draws and calculating match results to automatically move a player or team to the next rounds draw. A snooker and a tennis example are used for demonstration.
Cricket World Twenty20 Tournament
A section is dedicated to learning how to calculate cricket statistics and match results due to the unique nature of the sport. The World Twenty20 tournament is used to demonstrate how to calculate match results and statistics like net run rate, bowling strike rate and total balls bowled.
Let's Do It
Although the course cannot possibly cover all sports examples, the techniques learned can be adapted for any sports competition. Let's get started.
- Anyone who needs to calculate sports league table rankings or tournament fixtures
- Those involved in organising sports competitions and updating league tables
- Anyone wanting to improve their Excel skills, while enjoying fun Excel projects.
In this course, you will learn how to create automated sports league tables and tournaments in Microsoft Excel. When results are entered the teams are automatically ranked in the league tables, and statistics updated. It also automatically completes tournament fixtures as results are entered into the 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.
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 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.
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 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 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 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.