Excel Templates | Excel Spreadsheets | Create Sports League Standing Table

Create Sports League Standing Table

Posted on March 29, 2009
Filed Under Tutorial | 47 Comments

Finally I finished my tutorial on creating sports league standing table using Microsoft Excel, as promised. You can use this tutorial not only for creating soccer or football competition like my sample, but also you can use it for any competitions that follow the same schemes.

Step 1 : Define Participant Teams
At the beginning, you need to define the number of teams that participate in the competition. I define 8 teams as participants in this tutorial

Step 2 : Define Competition or League Type
You have to define the competition type. You will need this to control your fixtures table. Although this one has no direct effects in the formula, it will give you additional concerns when you need additional factor to decide the competition winner, just in case there is a tie position for some teams when all competition matches are completed. For example, if you pick full competition type where each team will meet twice in home and away game, you can make the goal made by away team in away game have more weight than goal in home game as decision factor to rank the team.

Step 3 : Define Competition Rule
You have participants, you already defined the competition type, now, you have to define rules needed to rank the team . And in this tutorial, I use the following rules to define the standing position :

Competition Basic Rules :
1. The final rank will be decided after all matches are finished
2. A team will get 3 points for winning, 1 point for draw and no points for defeat

Competition Standing Rule :
1. One team will be above other teams if it has more points than other teams
2. If there are two or more teams have the same points, the higher rank will be decided by better goal differences
3. If there are two or more teams have the same points and the same goal differences, the higher rank will be decided by better goals scored for
4. If there are two or more teams have the same points, goal differences, and goals scored for, higher rank will be decided by better away goal scored

You can add more rules for the competition to define the ranks, because once you know how to formulate it, any rules can be interpreted into excel formula easily.

Step 4 : Create Blank Dummy League Standing Table Sheet
Create a dummy league standing table worksheet. Why ? Because you need fixed columns for teams to be used as formula references, where all values picked and calculated from fixtures result will have fixed cell places inside the table. By doing that, it will be easier to manage all formula needed to get your team ranking based on any rules you specified. Is it possible to have just one table ? It is possible, but it is more complicated because you have to make one long formula with many conditions within one cell.

You can see my blank dummy standing sheet below. As I mention above, this is a full competition worksheet with home and away matches.

PICTURE 1

The following abbreviations are common football abbreviations used in league standing tables :
P : Played, W : Win, D : Draw, L : Lose, F : Goal Scored For, A : Goal Scored Against, GD : Goal Difference, Pts : Points

Step 5 : Create Blank Fixtures Worksheet
Now, you have to create blank fixtures worksheet where you can setup your league fixtures and fill the matches result. It is easier to make it in a separate worksheet because you can alter or change the layout later without affecting other table formulas.

One thing you have to keep in mind is, once you defined the column, you should not alter the position of the columns, because it will affect the formula references in other worksheet, except you are not forgetting to change the reference.

There will be 56 matches that will be calculated in the table. The number come from this calculation:
- There will be 4 matches per week or per some period of time,.
- Each team will face 7 other teams twice

And this 56 matches can be interpreted into 56 matches rows in your table.

PICTURE 2

Step 6 : Create Blank Standing Table Sheet
And now, you can create the standing table, with the same structure with Step 4, except for this one you put position number as your fixed reference. Just make the same table and put position number 1 – 8 in column A as seen the picture below.

PICTURE 3

Step 7 : Fill Team’s Name in Dummy League Standing Table Worksheet
All worksheets and basic tables are ready. Before proceeding to prepare the formula needed to automate all the standing process based on competition rules in Step 3, fill your dummy table with your team’s name. My sample worksheet can be seen in picture below.

PICTURE 4

Step 8 : Fill Dummy Result in Fixtures Worksheet
The reason why you should fill dummy result in fixtures worksheet is to ease your understanding on how the formula will work in dummy table worksheet. You can fill all columns or just some parts of them. In my example, I fill 14 matches with random results.

PICTURE 5

After finished filling dummy results in fixtures worksheet, go back to dummy table worksheet. Here you will have to create formula to grab all the results in fixtures sheet into the dummy table worksheet.

Step 9 : Create Formula for Home Matches Win Results
The first formula I create in dummy table worksheet is formula to grab all home matches results. There is 8 defined columns in fixtures sheet. In this tutorial, we are going to only use four columns, columns D to G. You might need other columns If you plan to expand your league to include analysis, charts or other functions.

Now, I will create formula for Liverpool home matches, the first team in the team column in dummy table worksheet. As you can see in fixtures worksheet, we can summarize Liverpool home matches as follows :
- Liverpool played twice
- Liverpool won twice
- Liverpool scored 3 goals
- Liverpool got scored against 1 goal

PICTURE 6

Based on that Liverpool home matches summary, we have to find correct logics to interpret these results and find the suitable excel functions to transform them into correct cell values. There are some excel functions with some combination that can be used to interpret them. I have made some experiments of those excel function combination before I decided to use Sumproduct function, which I think this is the shortest one.

You can see the basic structure of sumproduct function below :

Cell Value = sumproduct((ConditionA)*(ConditionB)*(ConditionC)*()…)

If the condition is met, the value will be 1, if the condition is not meet, the value will be 0. If all conditions is met, it means the multiplication of those conditions will be 1. This will work in loop until all set of conditions are verified. And the Cell Value will be the sum of those set values.

If you read the explanation about sumproduct in excel help, probably you won’t get the explanation for this kind of purposes, because excel help just provide you basic information about that function. You can google for this function if you need more explanation.

And the formula for this example to be put in cell L4 using this function is :

L4 value = SUMPRODUCT((Fixtures!$D$2:$D$52=$B4)*(Fixtures!$E$2:$E$52>Fixtures!$F$2:$F$52))

We need only two conditions to get the value.

Condition A = Fixtures!$D$2:$D$52=$B4 is created to find a team named Liverpool in column D
Condition B = Fixtures!$E$2:$E$52>Fixtures!$F$2:$F$52, is created to find cells where the value of column E is bigger than column F which mean where Liverpool win.

The usage of $ sign is to prevent the cell reference change when you copy the formula to other cells.

L4 value = will be the sum of values from multiplication conditions of those two conditions

If you do it correctly, the value should be 2.

PICTURE 7

Step 10 : Create Formula for Home Matches Draw Results

Now, move the cell M4 where you have to grab Liverpool draw results in that cell. And like the step 9, there are two conditions that have to be met, but there is one condition that have to be considered, as you can see it here :

M4 value = SUMPRODUCT((Fixtures!$D$2:$D$52=$B4)*(Fixtures!$E$2:$E$52=Fixtures!$F$2:$F$52)*(Fixtures!$E$2:$E$52<>”"))

Condition A = Fixtures!$D$2:$D$52=$B4 is created to find a team named Liverpool in column D Condition B = Fixtures!$E$2:$E$52=Fixtures!$F$2:$F$52 is created to find cells where the values are the same, which means draw.
Condition C = Fixtures!E$2:E$52<>”" is created to prevent the Condition B formula take blank cells into calculation. You know, blank cells will be considered as 0 and it will give you draw results. I don’t put a formula for column F because one cell is enough to prevent it.

PICTURE 8

Step 11 : Create Formula for Home Matches Lose Results

After step 9 and 10, I think you know what you have to do in cell N4. You can copy L4 formula and change the logical sign for condition B (see step 9). The complete formula is :

N4 Value = SUMPRODUCT((Fixtures!$D$2:$D$52=$B4)*(Fixtures!$E$2:$E$52<Fixtures!$F$2:$F$52))

You got it right ? Just change the red color sign above and you have completed your formula.

PICTURE 9

Step 12 : Create Formula for Home Team Goals Scored Results

For this cell, you can skip the sumproduct function. For this one, you use sumif function because you need to add the content of your target columns. So, the formula is :

O4 Value = SUMIF(Fixtures!$D$2:$D$52,$B4,Fixtures!$E$2:$E$52)

Read basic excel help provided in Microsoft Excel if you want to learn more about sumif function.

PICTURE 10

Step 13 : Create Formula for Home Team Goals Scored Against Results

Copy step 12 formula and paste it into P4 cell. Change the E column reference to F column reference to get the goals scored against value.

P4 Value = SUMIF(Fixtures!$D$2:$D$52,$B4,Fixtures!$F$2:$F$52)

PICTURE 11

PICTURE 12

Step 14 : Create Formula for Home Team Points

The value for these cells can be calculated easily. All you have to do just multiply defined points for winning and draw with the number of wins in column L and draws in column M. Except you want to have points also for losing, you don’t have to put value from column N into calculation.

Q4 Value = L4*3+M4*1

Step 15 : Create Formula for Number of Home Team Played

The value in R4 is basically just the sum of win, draw and lose value in column L4, M4 and N4.
R4 Value = SUM(L4:N4)

You finished creating formulas for Liverpool Home Matches rows. Check and compare the results with you manual calculation.

PICTURE 13

PICTURE 14

Now, you can simplify the formula for other 7 teams just by copying that Liverpool home matches formula and paste it to 7 rows below as you can see in Picture 14.
Your Home Matches Table is completed now. The next step is creating the formula for Away Matches Table.

Step 16 : Create Formula for Away Matches Columns

In this table, try to repeat the step 9 – 15. The logic and formula used is the same, except Liverpool is an away team now. So, the Liverpool team will refer to column G in dummy table, goals scored will refer to column F, and goals scored against will refer to column E. I think for this step, you can tryto do it by yourself. I attach the picture 15 – 23 with the formula inside the picture, for you to check your formula.

PICTURE 15

PICTURE 16

PICTURE 17


PICTURE 18


PICTURE 19


PICTURE 20


PICTURE 21


PICTURE 22


PICTURE 23

Step 17 : Create Formula for All Team Matches Results

Because all team matches are sum of all team home and away matches, all you have to do just add corresponding columns to complete the table. And it only needs two steps. First, go to cell C4 and type K4+S4. Copy this cell, select cells C5 – C11, and paste it. You have your table completed now.

PICTURE 24


PICTURE 25

Step 18 : Create Formula for Ranking Position based on Points

And these are the important columns of creating standing table. This is a place where you interpret your competition rules to give you the rank you wanted.

Before go to create formula, you have to insert four new columns between column B and column C. Select column C, right click your mouse and click insert. Do this four times, so you will have blank columns from C to F.

We will interpret the rule one by one. Just to remind you, the first competition rule is
“1. One team will be above other teams if it has more points than other teams”

Go to cell C4 which belongs to Liverpool rank based on points. Manually, you can see that Liverpool is rank number 2 among 8 teams.

Because this is the first condition that have to be met, you can use the rank function provided by excel to get your team rank based on points. So, the formula will be

C4 Value = rank(N4,$N$4:$N11). Copy the formula and paste it to cells C5 to C11 to fill all columns with rank formula.

PICTURE 26

PICTURE 27

You can see that the rank function will do basic rank function. And you can see in the example that Chelsea and Arsenal are sharing the same rank, 5, because both are having same points. And you will see that there is no rank no 6. And this is the reason why making league standing table is not as easy as people think.

Step 19 : Create Formula for Ranking Position based on Goal Differences
Now, you need to differentiate which team is on top of other. So, we have to interpret rule number two to differentiate them using excel function.
Competition rule number two is :
“2. If there are two or more teams have the same points, the higher rank will be decided by better goal differences”

You cannot use the rank function here because the weight of goal difference for each team is not the same. You have to filter it. So, the most suitable excel function to interpret this is sumproduct function.

PICTURE 28


PICTURE 29

Go to cell D4. The formula we used here is D4 Value = SUMPRODUCT(($C$4:$C$11=$C4)*($M$4:$M$11>M4)).
Condition A = $C$4:$C$11=$C4, is created to find the value in column C which have the same value with C4 value
Condition B = $M$4:$M$11>M4, is created to find value in column M which is bigger than value in M4

D4 Value will be sum of multiplication between condition A and B, just like the formula used in previous steps. Copy D5 and paste it in D4-D11 to get the formula working for other cells.

After you running it, you should see that it will give you the same value for both Chelsea and Arsenal because the GD for both teams are the same as you can see in picture below.

PICTURE 30


PICTURE 31

After this step, I hope you understand how the formula worked. And before going to third column or column E, put a formula in column A to sum the ranks of those four columns to get the feeling of the ranking formula you made.

A4 Value = Sum(C4:F4). Copy A4 formula and paste it on column A5-A11. And you have your ranking position that will be used as reference in standing table worksheet. Why it has to be in column A ? Because this column will be used as the first vlookup reference in standing table worksheet, where it can only lookup the right value from the reference.

After running the formula for all teams, you can see that Arsenal and Chelsea still share the same ranking because of their points and goal difference are the same.

PICTURE 32


PICTURE 33

Step 20 : Create Formula for Ranking Position based on Goals Scored
So, we need competition rule number three to differentiate their ranking.

“3. If there are two or more teams have the same points and the same goal differences, the higher rank will be decided by better goals scored for”

And this is the function :

E4 Value = SUMPRODUCT(($C$4:$C$11=$C4)*($D$4:$D$11=$D4)*($K$4:$K$11>K4))
Condition A = $C$4:$C$11=$C4, is created to find value in column C that have the same value with cell C4
Condition B = $D$4:$D$11=$D4, is created to find value in column D that have the same value with cell D4.
Condition C = $K$4:$K$11>K4, is created to find value in column K that have value bigger than value in cell K4.

Copy E4 and paste it in cell E5 to E11.

The formula will give zero results for all teams except Chelsea and Arsenal after condition A and condition B is run. In column J, cell value of Chelsea team is bigger than Arsenal team, so the formula for Arsenal will return value 1 and put in Arsenal cell.

And now you can see the position column or column A. You see that there is no same value now. It means that the final ranking is revealed after competition rules number three. But what about if some teams still share the same points after running this rule ? You still have competition rule number four to differentiate them.

Step 21 : Create Formula for Ranking Position based on Away Goals
Competition rule number four :
“4. If there are two or more teams have the same points, goal differences, and goals scored for, higher rank will be decided by better away goal scored”

And this is the formula :

F4 Value = SUMPRODUCT(($C$4:$C$11=$C4)*($D$4:$D$11=$D4)* ($E$4:$E$11=$E4) *($AA$4:$AA$11>AA4))
Condition A = $C$4:$C$11=$C4, is created to find value in column C that have the same value with cell C4
Condition B = $D$4:$D$11=$D4, is created to find value in column D that have the same value with cell D4.
Condition C = $E$4:$E$11>$E4, is created to find value in column E that have the same value with cell E4
Condition D = $AA$4:$AA$11>AA4, is created to find value in column AA that have value bigger than value in cell AA4. Copy cell F4 and paste it in cell F5 to F11.

PICTURE 34


PICTURE 35

You completed your ranking table now, and what about is some teams still share the same position after competition rule number four ? You should defined additional rule and try to input it in your new columns. I think this is your homework now to solve it …

If you have no problem on following my tutorial until this step, I think you are ready to expand or change the competition rules to suit your needs. Or, you can create your own sports league table.

Step 22 : Create Formula to Fill Standing Table Worksheet

PICTURE 36


PICTURE 37

Once you finished step 19, working on this standing table worksheet is easy. All you have to do just put number 1 to 8 in position column or column A and fill all corresponding cells with the value in working sheets related with those numbers.

Let’s start creating the formula
1. Go to cell B5 and type formula Vlookup($A5,’Dummy Table’!$A$4:$AD$11,2,false)
2. Copy B5
3. Select cell B5 to B12 and paste it.
4. Select cell C5 and type formula Vlookup($A5,’Dummy Table’!$A$4:$AD$11,COLUMN()+4,false)
5. Copy C5
6. Select cell C5 – Z12 and paste it.

You have your standing table completed now.

PICTURE 38

PICTURE 39

Step 23 : Simulate Competition Full Matches

This is the last thing to do but the most important part of your work, just like when you sell a product and you do a quality test to make sure your product meets all the specification, you need to simulate the competition on your sheets by putting dummy team fixtures and points in fixtures worksheet and see the results. If you want to be more precise, you have to simulate all conditions that might happens to make sure that your formula is working well.

That’s all guys. Let me know if you have problems. I will answer it in my spare time, but, tell you the truth, do not expect me to answer it quickly.

NOTES :
Perhaps there is some mistyping in formula written in this tutorial. If you found one, please refer to the correct one in excel function box in images I attached. And please let me know so I can correct it. Thanks.

You can read and download my other templates below :

None Found

Tags: excel tutorial, excel tutorials, football league standing, football standing table, sport standing table, standing table tutorial

Comments

• 
  

• Search

• Categories

  • Calendar
  • Education
  • Finance
  • Health
  • Introduction
  • Other
  • Report
  • Sports
    • Formula 1
    • Soccer
  • Tutorial

• Get new excel templates on sports fixtures, calendars, calculators, etc, every weekday by entering your email address here:

  
  








• About

  This site provides hundreds of free originally designed excel templates for financial, budget, loan, sport, football, F1, calendar, planner, travel, event, and other purposes

• Recently Written

  • Simple To Do List
  • Debt to Income Ratio Calculator
  • Pregnancy Calendar
  • UEFA Champions League Fixtures 2011/2012
  • 2012 Calendar
  • Serie A Fixtures 2011/2012
  • Amortization Chart
  • Eredivisie Fixtures 2011/2012
  • Ligue 1 Fixtures 2011/2012
  • Personal Budget
  • Bundesliga Fixtures 2011/2012
  • Yes No Chart
  • Academic Calendar 2011/2012
  • La Liga Fixtures 2011/2012
  • Formula 1 2011 Schedule and Championship Tracker
  • 2011 Calendars
  • UEFA Champions League 2010/2011 Schedule
  • Ligue 1 Schedule 2010/2011
  • Serie A Schedule 2010/2011
  • La Liga Schedule 2010/2011
  • Bundesliga Schedule 2010/2011
  • Eredivisie Fixtures 2010/2011
  • World Cup 2010 Wall Chart
  • World Cup 2010 Teams Biorythm
  • Academic Calendar 2010/2011
  • 2010 FIFA World Cup Office Pool
  • Formula 1 2010 Schedule
  • World Cup 2010 Schedule and Scoresheet
  • Monthly Calendar
  • Body Fat Calculator
  • Bill Payment Calendar
  • Net Present Value Calculator
  • Waist to Hip Ratio Calculator
  • TV Schedule
  • Chinese Calendar
  • Calories Burned Calculator
  • Islamic Calendar
  • Weekly Class Schedule
  • 2010 Calendar
  • Calorie Counter
  • Household Expenses
  • Weekly Calendar
  • Calorie Intake Calculator
  • BMR Calculator
  • Binary Hexadecimal Converter
  • Temperature Converter
  • Extra Payment Calculator
  • Present Value Calculator
  • Compound Interest Calculator
  • Savings Calculator

• Admin

  • Log in
  • Wordpress
  • XHTML

•