Ranking with Duplicates

[shocksterman]

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?

 

[SteveM]

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?

You could add a third column with COUNTIF nested in an IF like this:

=IF(COUNTIF($L$4:$L$14,L4) > 1,L4+0.5,L4)

Here L4 is your second column RANK value, L4:L14 the COUNTIF column
reference. The IF adds the .5 value if the COUNTIF > 1. Copy the
formula down the third column and you're done.

SteveM

 

[kounoike]

Applying a formula written in Excel help to your case, it could be something
like this in B1 and copy it down to B12

=RANK(A12,A$1:A$12,1)+(COUNT(A$1:A$12)+1-RANK(A12,A$1:A$12,0)-RANK(A12,A$1:A$12,1))/2

also see
http://office.microsoft.com/en-us/excel/HP052092311033.aspx?pid=CH062528311033

keiji

 

[Bernd P]

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

Regards,
Bernd

 

[kounoike]

i was wrong in my formula. A12 in RANK should be A1. But your formula is
much more simple. i think it might be better to use COUNTIF(ref, number)/2
as Correction factor for tied ranks instead of [COUNT(ref) + 1 -
RANK(number, ref, 0) - RANK(number, ref, 1)]/2 in the help

Regards
keiji