I am looking to implement grading into a database that I have but I need to get league tables ranking correctly.
This is how I have the table setup..
League Date Team Played Won Drawn Lost GF GA GD Pts Rank E0 26/10/2013 Arsenal 8 3 1 4 9 10 -5 10 E0 26/10/2013 Aston Villa 8 3 1 4 9 10 -1 10 E0 26/10/2013 Cardiff 8 2 2 4 8 13 -5 8 E0 26/10/2013 Chelsea 8 5 2 1 14 5 9 17 E0 26/10/2013 Crystal Palace 8 1 0 7 6 17 -11 3 E0 27/10/2013 Everton 9 5 3 1 14 10 4 18 E0 27/10/2013 Fulham 9 3 1 5 9 12 -3 10 E0 27/10/2013 Hull 8 3 2 3 7 9 -2 11 E0 27/10/2013 Liverpool 9 6 2 1 17 8 9 20 E0 27/10/2013 Man City 8 5 1 2 20 9 11 16 E0 02/11/2013 Man United 9 4 2 3 14 12 2 14 E0 02/11/2013 Newcastle 9 3 2 4 12 16 -4 11 E0 02/11/2013 Norwich 9 2 2 5 6 13 -7 8 E0 02/11/2013 Southampton 9 5 3 1 10 3 7 18 E0 02/11/2013 Stoke 9 2 2 5 6 10 -4 8 E0 30/11/2013 Sunderland 12 2 1 9 8 24 -16 7 E0 30/11/2013 Swansea 12 4 3 5 17 16 1 15 E0 30/11/2013 Tottenham 12 6 2 4 9 12 -3 20 E0 30/11/2013 West Brom 12 3 6 3 14 14 0 15 E0 30/11/2013 West Ham 12 2 4 6 9 14 -5 10
What I need to do is first of all rank if, I need to rank by the Points column if the League and Date match which at the moment I am using
=SUMPRODUCT(--([League]=[@League]),--([Date]=[@Date]),--([@Pts]<[Pts]))+1
Which gives me the ranking but I need those on the same points to then be ranked by a second condition (GD) and then if still equal by a third (GF).
So to sum it up Rank by Points if the League and Date Match
If duplicates then rank by GD and then GF.
Any help would be much appreciated as I am pulling my hair out at the moment!
I have attached a copy of my workbook.
Bookmarks