# 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

WOW!
Genius and elegant.
Works perfectly.
I don't think I ever would have come up with that one because I discarded FREQUENCY as a possible part of the solution hours ago......

Thank you so much SHG!

• Re: Dense Rank

You're welcome, glad it worked for you.

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