Unique Rank Formula

Abstract

This article is about using the Microsoft Excel functions RANK, RANK.EQ, RANK.AVG. Somtimes it appears that two (or more) positions will result at the same rank. If a uniqu ranking is needed, the following solution can be used:

Starting Position

In the interactive spreadsheet below, the mentioned functions are applied in order to compute the ranking of the values given in range C4:C12. Please note that using the RANK.AVG will not solve the described problem. RANK.AVG will show values on average, e.g., if the 7th rank is available twice, the average will be 7.5 ((7+8)/2), if 3 items occur on the 4th rank, the average will be 5 - and again the result will include duplicates.

Solution

The solution is to count the number of existing ranks equal to the current rank in the range above the current cell and add the number of found items to the current rank representing the final result. It is important not to use the whole range of computed ranks, only the range above (and relative to) the current cell has to be referenced.

Formulas

E4: =RANG(C4;$C$4:$C$12)
F4: =RANG.EQV(C4;$C$4:$C$12)
  =RANG.GLEICH(C4;$C$4:$C$12)
G4: =RANG.AVG(C4;$C$4:$C$12)
=RANG.MITTELW(C4;$C$4:$C$12)
I4: =IF(COUNTIF($F$4:F4;F4)>1;F4+COUNTIF($F$4:F4;F4)-1;F4)
=WENN(ZÄHLENWENN($F$4:F4;F4)>1;F4+ZÄHLENWENN($F$4:F4;F4)-1;F4)
L4: =INDEX($B$4:$B$12;MATCH(K4;$I$4:$I$12;0))
=INDEX($B$4:$B$12;VERGLEICH(K4;$I$4:$I$12;0))

Interactive Table


Dieter Neumann