Ranking Problem

Steve 1962

Active Member
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
ABCD
1RankPlayerTOTAL
21A24.1
3here17B1.7
43C16.3
5OK9D6.0
6OK10E6.0
7OK11F6.0
818G-1.0
9here15H2.5
1012I5.1
112J17.4
124K14.6
1313L3.6
14here15M2.5
156N7.7
167O7.7
178P7.7
185Q8.2
19here17R2.5
Sheet1

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Ranking is working, I suspect that cells 5 6 and 7 are different but rounded down to 1 decimal place.

The formula in cell B2 is

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

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

Lock the whole of the COUNTIF

COUNTIF(\$D\$1:D1,D2)

Seem to have a problem with cells A3 and A19 now.
Excel Workbook
ABC
1RankPlayerTOTAL
21A24.1
317B1.7
43C16.3
59D6.0
610E6.0
711F6.0
818G-1.0
915H2.5
1012I5.1
112J17.4
124K14.6
1313L3.6
1415M2.5
156N7.7
167O7.7
178P7.7
185Q8.2
1917R2.5
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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)

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

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

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

I now have 2 x 18 and no 16.

Book21.xlsx
ABCD
1RankPlayerTOTAL
21A24.1
317B1.7
43C16.3
59D6.0
610E6.0
711F6.0
818G-1.0
914H2.5
1012I5.1
112J17.4
124K14.6
1313L3.6
1415M2.5
156N7.7
167O7.7
178P7.7
185Q8.2
1918R2.5
Sheet1

Replies
8
Views
191
Replies
17
Views
256
Replies
7
Views
122
Replies
5
Views
356
Replies
1
Views
169

1,202,983
Messages
6,052,905
Members
444,611
Latest member
ggwpnore

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back