Closed Thread
Results 1 to 7 of 7

Excel 2007 : Ranking values and ranking duplicates the same rank

  1. #1
    Registered User
    Join Date
    03-25-2009
    Location
    Erie, PA
    MS-Off Ver
    Excel 2007
    Posts
    3

    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. #2
    Registered User
    Join Date
    03-25-2009
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    25

    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. #3
    Registered User
    Join Date
    03-25-2009
    Location
    Erie, PA
    MS-Off Ver
    Excel 2007
    Posts
    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. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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. #5
    Registered User
    Join Date
    03-25-2009
    Location
    Erie, PA
    MS-Off Ver
    Excel 2007
    Posts
    3

    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. #6
    Registered User
    Join Date
    05-21-2010
    Location
    vancouver, washington
    MS-Off Ver
    Microsoft Office Excel 2007
    Posts
    7

    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. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    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.
    Entia non sunt multiplicanda sine necessitate

Closed Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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