Create Sports League Standing Table
Posted on March 29, 2009
Filed Under Tutorial |
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.
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.
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.
- Part 1 : Define Competition Detail Information
- Part 2 : Create Blank League Table Form Worksheets
- Part 3 : Fill Dummy Matches Results
- Part 4 : Create Formula for Collecting Home Team Matches Results
- Part 5 : Create Formula for Collecting Away Team Matches Results
- Part 6 : Create Formula for Collecting All Team Matches Results
- Part 7 : Create Formula for Team Ranking based on Competition Standing Rule 1 - Points
- Part 8 : Create Formula for Team Ranking based on Competition Standing Rule 2 - Goal Differences
- Part 9 : Create Formula for Team Ranking based on Competition Standing Rule 3 - Goal Scored For
- Part 10 : Create Formula for Team Ranking based on Competition Standing Rule 4 - Away Goals
- Part 11 : Create Formula for Standing Table Worksheet
- Part 12 : Simulate Full Matches Competition
You can read and download my other templates below :
- None Found
Comments
31 Responses to “Create Sports League Standing Table”
Leave a Reply
First of all i would like to thank you for this. It has been very helpful.
There is one small problem that i have found and that is if the clubs are still ranked in the same position after all formulas have been used an error occurs where clubs are missing and N/A appears in the box.
Thanks once again and would be grateful if you could help with this small error.
Hello
First, Thanks a lot for this great tutorial
second, I am trying to make a table for English Premier League for next Season.
The thing is
in the English League they take “points, goal difference and goals scored” only to decide the rank, and if all those things are equal the teams share the position
how can i share the position ?
Hi Lee,
Since this is the basic tutorial, you should add some coeffient which different for each club or add some tricks. You can see my template at http://exceltemplate.net/sports/create-your-own-soccer-league-fixtures-and-table/ to see it.
Regards
@ Jafar : Be careful not to publish English Premier League Fixtures List when you finish your template or you will get a warning from English FA.
If you want them to share the same position, the easiest way is add new column in standing table worksheet next to position column, and put “if function” to compare your criteria between two rows. If your criteria is met, get the position information and make those two rows position the same.
Regards
Hi,
These spreadsheets have been extremely useful! fair play on the design, layout and sharing of these ‘applications’! they are extremely useful - thank you!!
Ive been trying to incorporate a last 5 overall, last 5 home and last 5 away table from the main overall tables per your spreadsheet but I cannot seem to manage it?!
have you tried to do this yourself or can you please offer some suggestions on how to implement this into your customisable league spreadsheet so that you could have the usual overall home and away and an additional tab or section for the same thing but just to represent the last 5 games played?
Cheers again and any help or assistance would be greatly appreciated!!!
Tomsyn
I have the last 5 games working - sorry - its just the goals part that im lacking!!!
I really like this way of doing things, it seems a lot cleaner than the way I was doing things.
I set up a similar spreadsheat, but I divided the goal difference by 10000 and then added it to the points and then took the rank from that. If a team had a +10000 Goal Difference in a season it would be worth a point.
It would be interesting to know about how you go about a knockout competition.
Hello
Thanks a million for this rather inspiring and informative tutorial. Often people with knowledge and expertise like you would rather take it to the grave with them than share it with those less fortunate. Thanks for teaching me “how to fish” a folklore in my country. Do continue the great work you are doing. Every good wish and God bless.
Very much appreciated
Jeffrey
Hello Again
What would be the formula or tutorial to effect..
a) Change of font and cell to Bold letters when typed and change of cell background well score is entered in cell.
b) To get a score result to read and change automatically in a single cell eg 5-2, five goals for and two against. I would appreciate very much help in this.
Thanks God bless
Jeffrey
Hi Jeffry,
Thanks for your comments. My answers to your comments are as follows :
a. You can do that using conditional formatting, go to menu > format > conditional formatting. You can read in excel help how to use this function in detail and you can see the sample of the usage from my other football template.
b. I don’t recommend to have only one cell as the result cell, because it is easier to process the result if the goals are separated in different cells, like in my football template.
Regards
Greetings, your spreadsheets have been fantastic. There refinement puts my pages to shame. I have been working with your pages try to configure a additional table, and I though you might be able to help. What I would like to see in an additional table is a clubs W/D/L standing in relation to their opponents standing in the table. Example: Dividing the table into three catigories; top 6, middle 7, bottom 7. What is “Milan’s” W/D/L standing when they play against clubs that are at the top or middle or bottom the the table? This way I could see at a glance how each club is doing home and away against the strong, average and low quality clubs throughout a season. Do have any idea for a formula based on your spreadsheet that would quickly calculate these results in a table? Because right now I am tracking by hand which is, over a season, a lot of lengthy work. Any help you might have on a formula would be great.
Thanks
Hi Bernie,
I still trying to understand your point here. The easiest way I can suggest based on my understanding is as follows :
a. Create additional 3 tables (top-6, middle-7, bottom-7) that separated from the main table.
b. Put an equal formula for the team name in that 3 tables the same with the team name from main table based on their latest position
c. Put Milan in the bottom of each table, so the tables will consist Milan as its 7th or 8th team
d. Put comparison formula that will prevent Milan for shown twice if in those tables (you can use if condition).
e. Do the same step as in my tutorial post to find out the W/D/L condition and rank position
And you will have Milan record compared to other teams in those 3 tables.
I hope I interpreted your meaning correctly and this steps can solve your problems.
Regards
here,i have a 16 teams to plays a football tournament,so i want to make a knock out system.thats why i have a small problem on make a fixture
I have been able to follow every step till the last one. I am stuck at the last step and can’t figure out your formula for standing table worksheet. I am not been able to generate results by entering the above mentioned formula.
pls help
hi saurabh,
could you inform me more detail information about your steps on applying the formula? Or you can email me.
regards
Greetings and thanks for a wonderful tutorial. I have used it to create my own EPL spreadsheet. Here is the problem I have. Say two(or three) teams are equal in points, goal difference and goals scored. Now I want to place (rank) those equal teams based on Alphabet. I have read and read many examples, but I can not figure it out.
I would appreciate any help with this issue
And again thanks for a great tutorial
@Bob Mcneil: The easiest way is just to put the coefficient number based on the order of alphabet of those 20 teams and put it into position calculation.
Regards
Hello again
I am facing this problem now
when I type the names of clubs in the fixtures sheet
without any score. the dummy sheet calculates a draw for the whole week
how can I solve this ?
Thanks in advance
Well, problem solved, i used double )) instead of one )
still having problem in sharing the ranks
I mean even if they don’t share the rank, how can I get rid of the #N/A that appears
if you need a screen shot you have my mail, I’ll try to explain more
thanks in advance
all problems are now solved
by a little bit of thinking
this tutorial was so useful for me
thanks a lot
Have you considered something similar for the National Hockey League? Thirty teams, six divisions and 82 games each and the added complications of a shoot-out to decide a winner?
This looks great and will be really useful. In the World Cup 1st round groups (Football/Soccer), teams that are level are split based on their head-to-head record (after points, GD and GF). If this involves 2 teams then it relies on them not having drawn their game, but if three teams are level, it needs to reflect the H2H of the three teams including GD and GF in those games only.
Can you think of a way that this can be done - it is quite a challenge I have found!
Many thanks
Hi,
I’ve followed this tutorial through, and seem to have done everything right, but, when I come to do step 22 in Part 11, it doesn’t automatically sort the data. How do I set it up to do that?
Thanks.
hi,
thanks for this info its been terrific help, but im only doing a small league of snooker teams, 10 teams so the ranking system is not sorting all my teams out into correct order, because alot of the teams are on identical points, any help on adding a ranking system formula to this to sort out into alphabetical order would be appreciated? thanks
This is great! Thank You very much! How can I learn this formulas? There are so much formulas in one cell. I wish to create my league with 16 or… teams.10-4!
I have learn loads from this, thanks
by the way, pictures 28 & 29 do not expand when you click them, i get a 404 error.
Sorry the pictures are 38 & 39 that are missing when i click them
Hi I have been asked to run a patence competition, what I need is a template to work like this, round 1 a,b,c,d play against e,f,g,h and so for up to 48 players. round 2 to be a,f,j,x play against b,y,k,l IE no player in same team twice Scoring is up to 9, 11 or 15 depending on how many playyers and time. Ie if a wins 11 7 and loses 4 11 then he would have atotal of 15 points Help please
Regards
Jac
Hi, I am having the same problem as saurabh (August 8th, 2009 6:24 pm). Everything was working great until the last step, it ranks the teams in the Standings Table correctly, but can’t get the games’ details (P,W,D,Pts,etc) into the Standings Table. Please help.
Hi, this is a really useful guide, is there any way to set this up with overtime results as my ice hockey competition does not have any draws if the game is tied at 60 mins, there is overtime and a shootout if necessary at the winner gets the 2 points but the loser still gets 1 point, like the NHL.
Any advice would be great.
Thanks.
How can i add a head-to-head points when teams are tied.