Ranking Problem

[Steve 1962]

Hi

Can't understand why cells B3 and B14 are showing the same value (ranking) when cells B5 to B7 are working fine. There should be no duplicate values in column B.

Thanks

Steve

Excel 2007
	A	B	C	D
1		Rank	Player	TOTAL
2		1	A	24.1
3	here	17	B	1.7
4		3	C	16.3
5	OK	9	D	6.0
6	OK	10	E	6.0
7	OK	11	F	6.0
8		18	G	-1.0
9	here	15	H	2.5
10		12	I	5.1
11		2	J	17.4
12		4	K	14.6
13		13	L	3.6
14	here	15	M	2.5
15		6	N	7.7
16		7	O	7.7
17		8	P	7.7
18		5	Q	8.2
19	here	17	R	2.5
Sheet1

 

[Dryver14]

Ranking is working, I suspect that cells 5 6 and 7 are different but rounded down to 1 decimal place.

 

[Steve 1962]

The formula in cell B2 is

=RANK(D2,$D$1:$D$19)+COUNTIF(D$1:D1,D2)

 

[Steve 1962]

Cells D5 to D7 are identical values. No rounding issues.

 

[Dryver14]

Lock the whole of the COUNTIF

COUNTIF($D$1:D1,D2)

 

[Steve 1962]

Seem to have a problem with cells A3 and A19 now.

Excel Workbook
	A	B	C
1	Rank	Player	TOTAL
2	1	A	24.1
3	17	B	1.7
4	3	C	16.3
5	9	D	6.0
6	10	E	6.0
7	11	F	6.0
8	18	G	-1.0
9	15	H	2.5
10	12	I	5.1
11	2	J	17.4
12	4	K	14.6
13	13	L	3.6
14	15	M	2.5
15	6	N	7.7
16	7	O	7.7
17	8	P	7.7
18	5	Q	8.2
19	17	R	2.5
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A2	=RANK(C2,$C$1:$C$19)+COUNTIF($C$1:C1,C2)
A3	=RANK(C3,$C$1:$C$19)+COUNTIF($C$1:C2,C3)
A4	=RANK(C4,$C$1:$C$19)+COUNTIF($C$1:C3,C4)
A5	=RANK(C5,$C$1:$C$19)+COUNTIF($C$1:C4,C5)
A6	=RANK(C6,$C$1:$C$19)+COUNTIF($C$1:C5,C6)
A7	=RANK(C7,$C$1:$C$19)+COUNTIF($C$1:C6,C7)
A8	=RANK(C8,$C$1:$C$19)+COUNTIF($C$1:C7,C8)
A9	=RANK(C9,$C$1:$C$19)+COUNTIF($C$1:C8,C9)
A10	=RANK(C10,$C$1:$C$19)+COUNTIF($C$1:C9,C10)
A11	=RANK(C11,$C$1:$C$19)+COUNTIF($C$1:C10,C11)
A12	=RANK(C12,$C$1:$C$19)+COUNTIF($C$1:C11,C12)
A13	=RANK(C13,$C$1:$C$19)+COUNTIF($C$1:C12,C13)
A14	=RANK(C14,$C$1:$C$19)+COUNTIF($C$1:C13,C14)
A15	=RANK(C15,$C$1:$C$19)+COUNTIF($C$1:C14,C15)
A16	=RANK(C16,$C$1:$C$19)+COUNTIF($C$1:C15,C16)
A17	=RANK(C17,$C$1:$C$19)+COUNTIF($C$1:C16,C17)
A18	=RANK(C18,$C$1:$C$19)+COUNTIF($C$1:C17,C18)
A19	=RANK(C19,$C$1:$C$19)+COUNTIF($C$1:C18,C19)

 

[Dryver14]

=rank(c2,$c$2:$c$19,0)+countif($c$1:c1,c2)

 

[Steve 1962]

Thanks for being patient but unfortunately it's still not co-operating.

 

[Dryver14]

did you change the $C$1:$C$19 to $C$2:$C$19

 

[Steve 1962]

I now have 2 x 18 and no 16.

Book21.xlsx
	A	B	C	D
1	Rank	Player	TOTAL
2	1	A	24.1
3	17	B	1.7
4	3	C	16.3
5	9	D	6.0
6	10	E	6.0
7	11	F	6.0
8	18	G	-1.0
9	14	H	2.5
10	12	I	5.1
11	2	J	17.4
12	4	K	14.6
13	13	L	3.6
14	15	M	2.5
15	6	N	7.7
16	7	O	7.7
17	8	P	7.7
18	5	Q	8.2
19	18	R	2.5
Sheet1