Create Sports League Standing Table

Posted on March 29, 2009
Filed Under Tutorial |

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.

League Tutorial 26
PICTURE 26

League Tutorial 27
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.

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

31 Responses to “Create Sports League Standing Table”

Lee on May 4th, 2009 4:24 pm

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.
Jafar Zuabi on May 8th, 2009 4:04 pm

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 ?
admin on May 9th, 2009 2:28 pm

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
admin on May 9th, 2009 2:47 pm

@ 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
Tomsyn on May 28th, 2009 7:43 am

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
Tomsyn on May 30th, 2009 6:32 pm

I have the last 5 games working - sorry - its just the goals part that im lacking!!!
Ian on July 11th, 2009 9:28 am

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.
Jeffrey Holder on July 13th, 2009 2:39 pm

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
Jeffrey Holder on July 13th, 2009 11:27 pm

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
Admin on July 14th, 2009 6:28 pm

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
Bernie on July 15th, 2009 8:30 am

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
admin on July 17th, 2009 3:05 pm

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
rakesh roshan on July 23rd, 2009 5:37 am

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
saurabh on August 8th, 2009 6:24 pm

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
admin on August 9th, 2009 4:15 pm

hi saurabh,

could you inform me more detail information about your steps on applying the formula? Or you can email me.

regards
Bob Mcneil on August 21st, 2009 11:19 pm

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
admin on August 23rd, 2009 4:43 pm

@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
Jafar on August 23rd, 2009 9:59 pm

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
Jafar on August 24th, 2009 9:56 pm

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
Jafar on September 1st, 2009 8:26 pm

all problems are now solved
by a little bit of thinking
this tutorial was so useful for me
thanks a lot
Paul on September 2nd, 2009 1:35 am

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?
Leo on September 17th, 2009 1:41 pm

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
Rob on September 24th, 2009 11:39 am

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.
richard smith on October 16th, 2009 9:47 am

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
Petar on October 16th, 2009 1:49 pm

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!
Des Watson on April 17th, 2010 2:24 pm

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.
Des Watson on April 17th, 2010 2:26 pm

Sorry the pictures are 38 & 39 that are missing when i click them
jack brown on May 1st, 2010 3:49 pm

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
Bevan on May 2nd, 2010 1:13 pm

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.
Chris on May 26th, 2010 11:41 am

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.
Chris on July 18th, 2010 1:52 pm

How can i add a head-to-head points when teams are tied.