Creating Sports League Tables and Tournaments in Excel
4.9 (25 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
134 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Creating Sports League Tables and Tournaments in Excel to your Wishlist.

Add to Wishlist

Creating Sports League Tables and Tournaments in Excel

Learn how to set up spreadsheets to calculate league table rankings and other sports competitions in Excel.
4.9 (25 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
134 students enrolled
Created by Alan Murray
Last updated 11/2016
English
Current price: $10 Original price: $30 Discount: 67% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 3 hours on-demand video
  • 2 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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.
View Curriculum
Requirements
  • An basic to intermediate knowledge of Excel
Description

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.

Who is the target audience?
  • Anyone who needs to calculate sports league table rankings or tournament fixtures
  • Those involved in organising sports competitions and updating league tables
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 27 Lectures Collapse All 27 Lectures 02:52:43
+
Introduction
1 Lecture 01:09
Introduction
01:09
+
Create a Sports League Table (Football/Soccer Example)
10 Lectures 01:02:59

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.
Preview 09:55

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.

Preview 10:46

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.

Calculating Games Drawn, Goals For and Goals Against
06:36

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.

Calculating Points Earned and Goal Difference
04:59

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.

Rank Teams by Points
03:31

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.

Uniquely Rank Teams that are Tied
05:42

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.

Rank Teams by Points, then Goal Difference and Goals Scored
07:49

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.

Including a Points Deduction
02:52

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.

Creating the League Table
03:27

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.

Extra League Table Example (Aviva Premiership - Rugby Union)
07:22
+
Create a Sports Knockout Tournament (Snooker Championships Example)
8 Lectures 01:00:49

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.

Setting up the Spreadsheet
06:01

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.

Randomise the Cup Draw
04:03

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.

Assign a Number to each Player or Team
03:14

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.

Populate the Cup Draw Sheet with Player or Team Names
13:35

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.

Simulate the Cup Draw
13:32

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.

Calculate Tournament Results
06:56

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.

Clear Tournament Data
09:10

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.

Extra Knockout Tournament Example (Tennis Grand Slam)
04:18
+
Create a Cricket Tournament Spreadsheet (World Twenty20 Example)
7 Lectures 47:10

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.
Overview of the Spreadsheet Setup
08:18

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.

Calculate Net Run Rate (NRR) 1
08:19

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.
Calculate Net Run Rate (NRR) 2
05:01

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.
Calculate Net Run Rate (NRR) 3
10:20

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.

Calculate Net Run Rate (NRR) 4
02:22

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.

Calculate Bowling Strike Rate (SR) 1
07:22

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.

Calculate Bowling Strike Rate (SR) 2
05:28
+
Further Learning
1 Lecture 00:35
Bonus Lecture: Download your FREE Ebook "27 Excel Hacks to Make you a Superstar"
00:35
About the Instructor
Alan Murray
4.5 Average rating
327 Reviews
3,552 Students
5 Courses
Founder of Computergaga

Alan Murray is a Microsoft Office trainer and consultant. He has been training and consulting for the past 15 years for businesses around the world.

He is the founder of Computergaga and regularly writes on the Computergaga blog to share tips, tutorials and templates.

Alan uses a fun and relaxed style of training that gets to the point, and uses real world practical examples uncovered from his experience of training and developing software for businesses.

Files are provided to follow along and exercises used to recap on topics covered. Alan is always eager to help and will be there for you when needed. He will do his best to answer any question or query related to his courses within 48 hours.