View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Herbert Seidenberg Herbert Seidenberg is offline
external usenet poster
 
Posts: 1,180
Default 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.