Excel League Rankings

[VBAX]

I am trying to rank these teams in order of points, then goal difference and then goals scored, but I don't know the formula,

I used to use a formula with rank and sumproduct, but I have forgotten it.

I will attach a spreadsheet I compiled in Office 2010 x64

As soon as I know how to add the attachment, I will

 

[VBAX]

Then why do you have different positions for Villa (13) and Fullham (14) when they have identical points, identical goal difference and identical goals scored?

Teams are then ranked alphabetically.

 

[Peter_SSs]

Teams are then ranked alphabetically.

Then the question remains: Why is Villa ranked ahead of Fullham?

 

[VBAX]

Then the question remains: Why is Villa ranked ahead of Fullham?

I don't know, How can I fix this?

 

[MARK858]

Probably because it should be Aston Villa?

 

[Peter_SSs]

I don't know, How can I fix this?

Do you want Fullham before Villa because Fullham comes before Villa alphabetically?

Perhaps Villa is actually Aston Villa and so it should come first?

Put whatever names you really want in column B and then use this formula in column K.

You can either ..

a) Keep the formulas in column A as is and keep sorting all columns, or

b) Just put the numbers (not formulas) 1-20 in column A and just sort columns B:K if the data changes

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	POS	CLUB	P	W	D	L	GF	GA	GD	PTS	Rank On
2	1	Liverpool	20	15	3	2	56	16	40	48	480630056.12
3	2	Man Utd	20	14	3	3	49	20	29	45	450520049.10
4	3	Spurs	19	13	3	3	36	20	16	42	420390036.06
5	4	Chelsea	20	11	4	5	39	25	14	37	370370039.15
6	5	Arsenal	20	11	3	6	36	28	8	36	360310036.19
7	6	Man City	20	9	7	4	24	18	6	34	340290024.11
8	7	New Utd	20	9	6	5	29	25	4	33	330270029.09
9	8	Sto City	20	8	5	7	22	31	-9	29	290140022.05
10	9	Norw City	20	6	7	7	30	35	-5	25	250180030.08
11	10	Sunderland	20	6	6	8	27	23	4	24	240270027.04
12	11	Everton	19	7	3	9	20	22	-2	24	240210020.14
13	12	Swa City	20	5	8	7	20	23	-3	23	230200020.03
14	13	Aston Villa	20	5	8	7	22	26	-4	23	230190022.18
15	14	Fulham	20	5	8	7	22	26	-4	23	230190022.13
16	15	WBA	20	6	4	10	19	28	-9	22	220140019.02
17	16	Wolves	20	4	5	11	22	36	-14	17	170090022.00
18	17	QPR	20	4	5	11	19	35	-16	17	170070019.07
19	18	Bol Wand	20	5	1	14	25	43	-18	16	160050025.16
20	19	Wigan	20	3	6	11	18	41	-23	15	150000018.01
21	20	Bburn	20	3	5	12	29	43	-14	14	140090029.17
Position

 

[Peter_SSs]

Probably because it should be Aston Villa?

Mark

I hadn't seen your post while I was composing my previous one.


VBAX
If you wanted to keep the abbreviations in column B but get the sorting/ranking done on the full name then you could add another column, say column L (or perhaps add a new column between B & C) and enter the corresponding full names. Again this extra column could be hidden/white text.

If you do this ..

1. Adjust the last part of my latest column K formula to point to the full name column rather than column B, and

2. Make sure you include this new column in any sorting you do.