Duplicate Numbers in Ranks
If you applied the above formula
to the modified list below, you would get
the results in the second column.
Note that 5.25 repeats 4 times and that
rank 6, 7 and 8 are missing:
1356 9.00 6.00
1394 12.00 9.00
1252 5.25 5.00
1141 2.00 2.00
1374 10.00 7.00
1388 11.00 8.00
1151 3.00 3.00
1252 5.25 5.75
1252 5.25 5.50
1252 5.25 5.25
1158 4.00 4.00
980 1.00 1.00
If you want the results in the third column,
use this formula:
=SUMPRODUCT((amt<amt 2:2)*(1/COUNTIF(amt,amt)))
+1+MOD(1/COUNTIF(amt,amt 2:2),1)*
MOD(COUNTIF(amt 2:$13,amt 2:2),COUNTIF(amt,amt 2:2))
It is assumed that your list is named "amt"
and starts in row 2 and ends in row 13.
|