# Excel 2007 : Ranking values and ranking duplicates the same rank

1. ## Ranking values and ranking duplicates the same rank

I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.

Example:
20
18
18
15
12
11
11
9

The ranking I am looking for is:
1
2
2
3
4
5
5
6

Thanks!!

2. ## Re: Ranking values and ranking duplicates the same rank

You can do an "if" as follows, asuming the data is sorted, as you show.

``Please Login or Register  to view this content.``
(data range starts in cell A1; cell B1=1)

Copy this formula downwards, and your ranking should work. If the data is not sorted, it may be slightly more complicated, but it would most likely require a sorting anyway.

3. ## Re: Ranking values and ranking duplicates the same rank

Thanks Jorozco but yes you are correct that the data stills needs to be sorted. My exmaple was the end result I was looking for.

4. ## Re: Ranking values and ranking duplicates the same rank

If then we assume unsorted range, eg A1:A8

``Please Login or Register  to view this content.``
You can get the ranking using:

B1: =SUMPRODUCT((\$A\$1:\$A\$8>\$A1)/COUNTIF(\$A\$1:\$A\$8,\$A\$1:\$A\$8&""))+1
copied to B8

Does that help ?

5. ## Re: Ranking values and ranking duplicates the same rank

DonkeyOte,

That works great. Wow...thank you. I had found an array solution but I could not get it to work. That works perfectly.

6. ## Re: Ranking values and ranking duplicates the same rank

Is it possible with the ranking values with duplicates to utilize a secondary value?

I'm using this formula :
=SUMPRODUCT((\$F\$10:\$F\$55<\$F\$10)/COUNTIF(\$F\$10:\$F\$55,\$F\$10:\$F\$55&""))+1

I have list of individuals with varying performance scores; at times some will come back with the same percentage and if there is an option of a secondary value to look for to always only rank 1 through an infinate number not skipping any or duplicating a ranking number.

Does this make sense?

7. ## Re: Ranking values and ranking duplicates the same rank

Welcome to the forum, awaring.

Please take a few minutes to read the forum rules, and then start your own thread.

Thanks.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1