Dense Rank With Ties

  • I am trying to figure out how to create a dense rank in excel.
    I have a list of numbers
    5 6 6 7 8
    Rank will rank them
    1 2 2 4 5
    I would like to have them ranked
    1 2 2 3 4


    I'm trying to create a 1st-2nd-3rd scoring worksheet and ties for a place are okay and the 4th person should get 3rd place.


    Thanks for any help, I'm baffled...

  • Re: Dense Rank


    I don't immediately see a closed-form solution for this that is independent of sorting, though I'm sure there is one.


    If the values are sorted ascending in column A, then:


    B1 [COLOR="blue"]=1[/COLOR]
    B2 and copy down: [COLOR="Blue"]=IF(A2=A1, B1, B1+1)[/COLOR]

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Dense Rank


    The list is not sorted. It is random scores entered in random order.
    What I am trying to achieve is a gymnastics score tracker. A list of gymnansts on the left. You enter their score in the next column. And I wanted the next column to list out the 1st, 2nd, 3rd, etc place winners; and, ties are okay. Meaning you can have 2 people tie for second and then have a 3rd place winner. Continuing, there are 4 events and then an all around score and ranking. I just can't figure out the ranking part to account for ties without gaps in the list.


    Thanks!

  • Re: Dense Rank


    [COLOR="Blue"]=SUMPRODUCT( (FREQUENCY($A$1:$A$10, $A$1:$A$10) > 0) * (A1 >= $A$1:$A$11) )[/COLOR]


    Note the extra row required in the second expression.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Dense Rank


    I pulled this from an Older post... it will rank from 1 to whatever... and account for duplicate scores but assigns them a different increasing number... not the "same number" as you requested...


    Assume your numbers are A1 to A5...


    =RANK(A1,$A$1:$A$5,1)+COUNTIF($A$1:A1,A1)-1


    HTH
    Ger[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Nice job SHG....

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

  • Re: Dense Rank


    You're welcome, glad it worked for you.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]