Ranking list with ties?

**unipsychologist** · 07-15-2012, 01:03 PM

Hi, I'm trying to come up with a formula that assigns ranks to an ascending list of integer values where ties are resolved by assigning tied values the mean of the ranks they would have received if there were no ties, e.g., 117, 119, 119, 125, 128 becomes 1, 2.5, 2.5, 4, 5. (If the two 119s were not tied, they would have been assigned the ranks 2 and 3, yielding a mean of 2.5.) My list looks like this:

Column_A Column_B
1
1
1
2
3
4
4
5
...

Help appreciated, thank you!

**shg** · 07-15-2012, 01:36 PM

      --A-- -B--
  1   Value Rank
  2       1    2
  3       1    2
  4       1    2
  5       2    4
  6       3    5
  7       4  6.5
  8       4  6.5
  9       5    8

B2 and copied down:

=COUNTIF($A$2:$A$9, "<" & A2) + (COUNTIF($A$2:$A$9, A2) + 1) / 2