• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

League table - Ranking

M

matexcel

New Member
Hi, I would like to make excel table for ordinary tennis league, but if 2 teams are equal with points than it should take into account their mutual match, so who won the match is higher ranking.
So, I have 12 teams, namely:
Team 1
Team2
Team3
Team4
Team5
Team6
Team7
Team8
Team9
Team10
Team11


After entering the results for each round, let Excel itself sort the Teams in the following order:
a) Team with most points is highest ranking.
b) If 2 teams with the same nr. of points, the head-to-head is then watched and whoever won is higher ranking. If the teams have not yet played each other or have result for draw, then skip to c).
c) If the teams are still the same, then they are judged by goal difference - higher difference is higher ranking.
d) If the teams are still the same, then see who received fewer goals - who received less goals is higher ranking..
e If the teams are still the same, then see who scored more goals - who scored more goals is higher ranking.
f) If the teams are still the same, then they occupy the same ranking place.

Here is link for demo file:
docs.google.com

PD League-Demo.xlsx

Data Round,Choose,Choose,Combined,#,Team,P,W,D,L,+,-,+/-,P,Team 1. round,TEAM11,TEAM10,0,:,6,0 : 6,1.,TEAM5,6,5,1,0,26,10,16,16,TEAM1 1. round,TEAM6,TEAM1,3,:,3,3 : 3,2.,TEAM10,5,4,0,1,20,10,10,12,TEAM2 1. round,TEAM7,TEAM2,/,:,/,/ : /,3.,TEAM6,6,3,2,1,22,14,8,11,TEAM3 1. round,TEAM3,TEAM9,2,:,4...
docs.google.com docs.google.com

If you have any solution / formula, pls let me know

Regards
 
You will have Power Query built-in to Excel 2016 so this should work.
In the attached is your score table adjusted slightly. I've used Home and Guest and H and G for Home score and Guest score. It's a proper Excel table, needed for Power Query. A blank cell signifies nothing played; a zero means a score of zero in the match.
The orange table at cell G1 is the result.
In the background I work out the columns shown (which seem to agree with your calculations).
Also in the background I sort by columns in the following order: Points (desc), GoalDiff (desc), GoalsAgainst (asc) and Score (desc).
With your sample data the sort is established at the GoalDiff sort, the subsequent columns' sorting does not affect the result.
Then I added a plain index on the left (1,2,3, etc.) so this is NOT ranking (yet).
What I have NOT done is:
matexcel said:
the head-to-head is then watched and whoever won is higher ranking
Click to expand...
which I'll have to think on a bit more.

The result won't update by itself just yet, you have to right-click that orange table and choose Refresh.
 

Attachments

  • Chandoo48332PD League-Demo.xlsx
    39.9 KB · Views: 10
Thanks for reply,

I checked but this is not working properly. Pls check desctription on 1st post I sent...

Best regards
 
Hi, I would like to make excel table for ordinary tennis league, but if 2 teams are equal with points than it should take into account their mutual match, so who won the match is higher ranking.
So, I have 11 teams, namely:
Team 1
Team2
Team3
Team4
Team5
Team6
Team7
Team8
Team9
Team10
Team11


After entering the results for each round, let Excel itself sort the Teams in the following order:
a) Team with most points is highest ranking.
b) If 2 teams with the same nr. of points, the head-to-head is then watched and whoever won is higher ranking. If the teams have not yet played each other or have result for draw, then skip to c).
c) If the teams are still the same, then they are judged by goal difference - higher difference is higher ranking.
d) If the teams are still the same, then see who received fewer goals - who received less goals is higher ranking..
e If the teams are still the same, then see who scored more goals - who scored more goals is higher ranking.
f) If the teams are still the same, then they occupy the same ranking place.

Here is link for demo file:
docs.google.com

PD League-Demo.xlsx

Data Round,Choose,Choose,Combined,#,Team,P,W,D,L,+,-,+/-,P,Team 1. round,TEAM11,TEAM10,0,:,6,0 : 6,1.,TEAM5,6,5,1,0,26,10,16,16,TEAM1 1. round,TEAM6,TEAM1,3,:,3,3 : 3,2.,TEAM10,5,4,0,1,20,10,10,12,TEAM2 1. round,TEAM7,TEAM2,/,:,/,/ : /,3.,TEAM6,6,3,2,1,22,14,8,11,TEAM3 1. round,TEAM3,TEAM9,2,:,4...
docs.google.com docs.google.com

If you have any solution / formula, pls let me know

Regards
 
matexcel
Did You notice that You copy & paste Your #1 reply?
You wrote with #5 reply I checked but this is not working properly.
... then You've noticed what is not working properly
... ... then You should give more details, what should work other ways.
... Why others should try to find - what? if You already know those?
 
I sent once again all tekst, because you did not check all. You just sent me table with bo fomulas, no improvement...
So, pls check all tekst and than you can check if you have any idea..
 
I'm not sure I will be able to address point b but I wanted a bit of clarification.

You have multiple instances where 3 or more teams have the same point totals - how would you expect those to be resolved?

For instance, if 3 beat 2 and 2 beat 1 but 1 beat 3 who gets the highest ranking?
 
Hi and tx for an interest.

I would like to make excel table for ordinary football league, where ranking includes also head to head match. So, I have 11 teams, namely:
Team 1
Team2
Team3
Team4
Team5
Team6
Team7
Team8
Team9
Team10
Team11


After entering the results for each round, let Excel itself sort the Teams in the following order:
a) Team with most points is highest ranking.

IF 2 TEAMS WITH SAME POINTS:
a1) If 2 teams with the same nr. of points, than head-to-head is then watched and whoever won is higher ranking. If the teams have not played each other yet or have h2h result for draw, then skip to b).

IF 3 TEAMS WITH SAME POINTS:
a2) If 3 teams (or more) have same nr. of points, than they are judged by goal difference just between these 3 teams - higher difference is higher ranking.
a3) If 3 teams are still the same than see who received less goals, just between these 3 teams - who received less goals is higher ranking.
a4) If 3 teams are still the same, then see who scored more goals, just between these 3 teams - who scored more goals is higher ranking.
a5) If 3 teams are still the same, than they occupy the same ranking place.

b) If the teams are still the same, then they are judged by goal difference - higher difference is higher ranking.
c) If the teams are still the same, then see who received less goals - who received less goals is higher ranking..
d) If the teams are still the same, then see who scored more goals - who scored more goals is higher ranking.
e) If the teams are still the same, then they occupy the same ranking place.

Here is link for demo file:
docs.google.com

PD League-Demo.xlsx

Data Round,Choose,Choose,Combined,#,Team,P,W,D,L,+,-,+/-,P,Team 1. round,TEAM11,TEAM10,0,:,6,0 : 6,1.,TEAM5,6,5,1,0,26,10,16,16,TEAM1 1. round,TEAM6,TEAM1,3,:,3,3 : 3,2.,TEAM10,5,4,0,1,20,10,10,12,TEAM2 1. round,TEAM7,TEAM2,/,:,/,/ : /,3.,TEAM6,6,3,2,1,22,14,8,11,TEAM3 1. round,TEAM3,TEAM9,2,:,4...
docs.google.com docs.google.com

If you have any solution / formula, pls let me know

Regards
 
Ok, I have made an attempt on this one. I could not figure out how to do the # of wins btw tied teams so I made the logic work on the number of goals scored alone. It will not work exactly as you asked.

I have added 4 helper columns in orange and a table in yellow.

Let me know how you think that worked for you.

Cheers,
 

Attachments

  • team ranking.xlsx
    20 KB · Views: 14
hi and tx for helping. Anyway, currently I am missing league table sorting. So, additional table ranking should be based on column Final Rank (column X) - team which has Final Ranking 1 is on top and with Final Ranking highest number should be on the bottom of table. Ranking table should start in let's say in Z column. Can you pls add this additional table?
 
Hi, all is working fine - also for 2 teams with same nr of points. But prb appears when 3 teams have same number and I want as I wrote on begining:


a) Team with most points is highest ranking. - OK

IF 2 TEAMS WITH SAME POINTS: - OK
a1) If 2 teams with the same nr. of points, than head-to-head is then watched and whoever won is higher ranking. If the teams have not played each other yet or have h2h result for draw, then skip to b).

IF 3 TEAMS WITH SAME POINTS: - ANY IDEA?
a2) If 3 teams (or more) have same nr. of points, than they are judged by goal difference just between these 3 teams - higher difference is higher ranking.
a3) If 3 teams are still the same than see who received less goals, just between these 3 teams - who received less goals is higher ranking.
a4) If 3 teams are still the same, then see who scored more goals, just between these 3 teams - who scored more goals is higher ranking.
a5) If 3 teams are still the same, than they occupy the same ranking place.

ANY IDEA?
b) If the teams are still the same, then they are judged by goal difference - higher difference is higher ranking.
c) If the teams are still the same, then see who received less goals - who received less goals is higher ranking..
d) If the teams are still the same, then see who scored more goals - who scored more goals is higher ranking.
e) If the teams are still the same, then they occupy the same ranking place.
 
You must log in or register to reply here.
Back
Top