Forum Discussion

Thomas Bryant's avatar
Thomas Bryant
Copper Contributor
Aug 22, 2017

Ranking

I am trying to rank the following the following numbers. 40,39,41,38,39,37,23,35,25,35,30,30,33,31,35.5,29,30,32,33.5,40, and 37.5. When I attempt to rank 39 using the function =RANK(B3,B3:B26) I comes out as four. I want 41 as 1, both 40's as 2, and 39 as 3. How can I accomplish this?

Charting
excel

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 22, 2017

    Hi Thomas,

     

    I'd suggest

    =RANK(B3,$B$3:$B$23,0) -
   (
      COUNTIF($B$3:$B$23, ">"&B3)-
      SUMPRODUCT(($B$3:$B$23>B3)*1/COUNTIF($B$3:$B$23,$B$3:$B$23))
   )

    (range is from B3 to B23).

     

    COUNTIF returns number of values greater than current one. SUMPRODUCT - number of unique values in that range (i.e. for one which are greater than current value).

     

    In combination they give number of duplicates whic we deduct from rank. Result is

    and in attached file