ranking an row of values

[Guest]

I would like to modify the way Excel ranks a range of values. An example
might be helpful:

value rank
4.6 1
4.7 2
4.9 3
5.1 4
5.2 5.5
5.2 5.5
5.3 7
5.4 8
5.5 9
5.6 10
5.8 11
6.1 12
6.2 13
6.3 14
6.5 15.5
6.5 15.5
6.8 17
7.2 18
7.7 19
8 20
8.1 21

As you can see, I would like the ranking to deal with identical values by
assigning them both the average rank of the values immediately adjacent to
them (see 5.2 as an example.). Is there a convenient way to do this?

 

[Tim C]

One way:
=RANK(A1,$A$1:$A$10,1)+(COUNTIF($A$1:$A$10,A1)-1)/2

Tim C

 

[Bernd Plumhoff]

If your values are in A1:A14, then enter in cell B1:

=SUM(ROW(INDIRECT(RANK($A1,$A$1:$A$14,TRUE)&":"&RANK
($A1,$A$1:$A$14,TRUE)+COUNTIF($A$1:$A$14,$A1)-1))/COUNTIF
($A$1:$A$14,$A1))

Enter this formula with CTRL+SHIFT+ENTER (as array
formula). Finally copy it down to B14.

HTH,
Bernd

 

[Guest]

Thanks. Works like a charm. Now I need to go figure out why.

 

[Tim C]

It counts the number of duplicates, and adds .5 for each one (minus itself).

Tim C