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.
In this lesson we look at setting up the tables required to create our league table. This lessons covers;
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;
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;
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;
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.
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.