+ Reply to Thread
Results 1 to 11 of 11

How to break ties in the RANK function

  1. #1
    Forum Contributor
    Join Date
    10-16-2008
    Location
    San Diego
    Posts
    109

    How to break ties in the RANK function

    Excel 2003
    I am using the RANK function to rank the data in several rows. There are some times ties in the result.
    I want to break that tie by taking the greatest value in a different cell in that row
    Greatest value wins the tie.
    same situation if there are multiple ties (4 "RANK"'s of position 2)

    Any help here will be appreciated.

    Thanks in advance
    dave
    Last edited by ducecoop; 12-08-2008 at 12:57 PM.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284
    Here is an example

    =RANK(B1,$B$1:$B$10,1)+COUNTIF($B$1:$B1,B1)-1

  3. #3
    Forum Contributor
    Join Date
    10-16-2008
    Location
    San Diego
    Posts
    109
    Quote Originally Posted by Bob Phillips View Post
    Here is an example

    =RANK(B1,$B$1:$B$10,1)+COUNTIF($B$1:$B1,B1)-1
    Thanks for the reply but I do not understand how to use it.

    my Rank function in from column A in cell A5
    Please Login or Register  to view this content.
    Tie breaker is the greatest value in column C

    In the attached example

    there is a tie between row 5 and row 8
    row 5 should win

    also there is a tie between row 7 and row 9

    row 9 should win

    hope that helps
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-21-2006
    Location
    Ossett, West Yorkshire
    MS-Off Ver
    2003
    Posts
    150

    Smile

    Hi, I have put 3 helper columns in at the right of you data to combine the rankings befor inserting the finalranking in ColA.
    have alook at the attachment
    Regards Howard
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    Does this give you the order you want (eg in an adjacent row 2 cell copied down):

    =RANK(B3,$B$3:$B$9,1)+SUMPRODUCT(--($B$3:$B$9=B3),--($C$3:$C$9>C3))

    Richard
    Richard Schollar
    Microsoft MVP - Excel

  6. #6
    Forum Contributor
    Join Date
    10-16-2008
    Location
    San Diego
    Posts
    109
    Quote Originally Posted by RichardSchollar View Post
    Does this give you the order you want (eg in an adjacent row 2 cell copied down):

    =RANK(B3,$B$3:$B$9,1)+SUMPRODUCT(--($B$3:$B$9=B3),--($C$3:$C$9>C3))

    Richard
    Seems to work.

    Can you explain what it is doing so I can duplicate it please

    Thanks much

  7. #7
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    Sure - it uses the rank function to determine an initial rank and then the Sumproduct part of the formula separates out any ranking ties by adding on the number of values in the column where the score is tied (ie ($B$3:$B$9=B3)) and the value in the C column is greater than the current C column value ($C$3:$C$9>C3) - so this will give a higher rank to tied values where the C column has the highest value.

  8. #8
    Forum Contributor
    Join Date
    10-16-2008
    Location
    San Diego
    Posts
    109
    Quote Originally Posted by RichardSchollar View Post
    Sure - it uses the rank function to determine an initial rank and then the Sumproduct part of the formula separates out any ranking ties by adding on the number of values in the column where the score is tied (ie ($B$3:$B$9=B3)) and the value in the C column is greater than the current C column value ($C$3:$C$9>C3) - so this will give a higher rank to tied values where the C column has the highest value.
    Got it
    Thanks much

  9. #9
    Registered User
    Join Date
    06-21-2011
    Location
    Corning, NY
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: How to break ties in the RANK function

    I have a column of unordered ranks (including ties) in 1st column and want to create 2nd column that is an ordered version of the 1st column. I want to do this with Excel functions (I have Excel 2010, but a function that works for Excel 2007 would be OK too), not the Data Sort through the ribbon.

    Col 1
    11
    10
    2
    7
    3
    5.5
    13
    12
    8
    15
    4
    9
    5.5
    16
    14
    1

    Can someone help?

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to break ties in the RANK function

    @profdean
    @nmginn

    please start your on threat.

    it's not allowed to ask an own question in anotherone's treat.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  11. #11
    Registered User
    Join Date
    08-20-2013
    Location
    malaysia
    MS-Off Ver
    Office 365
    Posts
    18

    Re: How to break ties in the RANK function

    hi richard
    chanced upon your post while looking for a solution to my ranking with multiple criteria in excel 2003 and your formula does work for my problem, thanks a million where ever and who ever you are, cheers.....

+ Reply to 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