ranking an row of values

W

Wazooli

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?
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top