Duplicate Numbers in Ranks
 

I am trying to rank a list of 10 numbers and I know how to rank using
the RANK function. I have a problem though. When there are two (or
more) values that are the same, i want them to show .5 instead of
multiple 4s. i.e.


1356 9
1394 12
1252 6
1141 2
1374 10
1388 11
1152 3
1242 5
1256 8
1252 6
1158 4
980 1

There are two occurences of 6.. but I would them to each be 6.5 rather
than each of them being 6.

Any thoughts?

Duplicate Numbers in Ranks
 

If a number appeared 3 times would you then want the rank to be n.33333? If
4 times, then rank should be n.25?

If that's what you want, try this (with the decimal rounded to 2 places):

=ROUND(RANK(A1,A$1:A$12,1)+MOD(1/COUNTIF(A$1:A$12,A1),1),2)

--
Biff
Microsoft Excel MVP


wrote in message
...
I am trying to rank a list of 10 numbers and I know how to rank using
the RANK function. I have a problem though. When there are two (or
more) values that are the same, i want them to show .5 instead of
multiple 4s. i.e.


1356 9
1394 12
1252 6
1141 2
1374 10
1388 11
1152 3
1242 5
1256 8
1252 6
1158 4
980 1

There are two occurences of 6.. but I would them to each be 6.5 rather
than each of them being 6.

Any thoughts?

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.