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!!
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!!
You can do an "if" as follows, asuming the data is sorted, as you show.
(data range starts in cell A1; cell B1=1)Please Login or Register to view this content.
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.
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.
If then we assume unsorted range, eg A1:A8
You can get the ranking using:Please Login or Register to view this content.
B1: =SUMPRODUCT(($A$1:$A$8>$A1)/COUNTIF($A$1:$A$8,$A$1:$A$8&""))+1
copied to B8
Does that help ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
DonkeyOte,
That works great. Wow...thank you. I had found an array solution but I could not get it to work. That works perfectly.
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?
Welcome to the forum, awaring.
Please take a few minutes to read the forum rules, and then start your own thread.
Thanks.
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks