Ranking of Football Teams in a League Table

[JamesAppleby]

Hello all,

I am working on a league table in excel which follows these set rules to determine the rank of the teams.

1. Points obtained in all group matches;
2. Goal difference in all group matches;
3. Number of goals scored in all group matches;
4. Points obtained in the matches played between the teams in question;
5. Goal difference in the matches played between the teams in question;
6. Number of goals scored in the matches played between the teams in question;

I have had no issues ranking the teams based on points 1 - 3 however the final 3 are stumping me.

In a league table there are 4 teams who all play each other once, 3 points for a win, 1 for a draw, 0 for a loss. With certain conditions 3 teams can all have the same number of points, same goal difference and same number of goals scored.

I currently have a terrible IF function that says does team 1 rank equal teams 2 rank, if yes did team 1 beat team 2 then team 1 scores higher, if no does team 1 rank equal team 3 rank, and so on.

This only allows me to compare 2 teams against each other against the criteria for the 4th point, and also does not consider the inevitable 3 team tie up.

Any ideas on how to go about this problem would be greatly appreciated. Also I would really love to stay away from VBA as I would like my workbook to remain macro free!

Cheers!
James

 

[JamesAppleby]

Hello all,

I am still not having any look with this. To help I have uploaded some images showing what I have so far. In the grey boxes you can see my ranking calculations to meet criteria 1 to 3.

I have attached two sets of images one not showing formulas and one showing.

I really appreciate you spending your time helping me.

Thanks, James

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Struggling to rank teams in a league table
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[zago12]

tsk ustadlar sagol

 

[Fluff]

@zago12 please post to this board in English only. Thanks

 

[hykos1045]

Hello all,

I am still not having any look with this. To help I have uploaded some images showing what I have so far. In the grey boxes you can see my ranking calculations to meet criteria 1 to 3.

I have attached two sets of images one not showing formulas and one showing.

I really appreciate you spending your time helping me.

Thanks, James

James,

For your request, you are going to want to create a formula that promotes the highest value tiebreaker to the first position, then so on down the line. For example, multiply the number of points earned by 100,000,000 and then multiply the goal difference by 1,000,000, and sum these together

This may result, for two teams with 28 points but one had a +4 GD and another a -3 GD, in that they would get "ranked" differently based on the GD tiebreaker. However, if the team with -3 GD were to get a 29th point, they would be put ahead of the team with 28 pts because it's a higher power. You can build this formula from the lowest tiebreaker to the highest.