+ Reply to Thread
Results 1 to 10 of 10

How to break a tie in Rank

  1. #1
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    How to break a tie in Rank

    Hello,

    I have a general number that I am ranking, but when there's a tie, I want it to refer to a number in another column to break the tie. Is there a formula to make this happen? My data is below

    Col....A.. B... C
    Row.TOT. Rk.. Final
    1.....99... 1... 750
    2.....98... 2... 436
    3.....98... 2... 336
    4.....97... 4... 532

    I want the 98 TOT with the higher "Final" score to be ranked second and the 98 with the lower "Final" score ranked third. Is there a way to do this so it's already built into the original rank formula and only applies it to the scores in column A that are tied?

    Thanks!
    Last edited by mlbdc2012; 11-23-2013 at 04:07 AM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: How to break a tie in Rank

    welcome to the forum. something like:
    =RANK(A1,$A$1:$A$4)+COUNTIFS($A$1:$A$4,A1,$C$1:$C$4,">"&C1)

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: How to break a tie in Rank

    Quote Originally Posted by benishiryo View Post
    welcome to the forum. something like:
    =RANK(A1,$A$1:$A$4)+COUNTIFS($A$1:$A$4,A1,$C$1:$C$4,">"&C1)
    That worked great thanks a bunch!

  4. #4
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: How to break a tie in Rank

    If I wanted to rank the criteria based on a third column how would I incorporate that into the formula you just provided?

    If I only wanted to Rank people that contain 'AL' instead of 'NL' how would I do that still incorporating the tiebreak feature you just assisted me with?

    EXAMPLE:

    Col....A.. B... C........D
    Row.TOT. Rk...Final...League
    1.....99... 1... 750.....AL
    2.....98... 2... 436.....NL
    3.....98... 2... 336.....AL
    4.....97... 4... 532.....NL

  5. #5
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: How to break a tie in Rank

    Can anyone help me with the second part of my question? Thanks

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2212
    Posts
    37,021

    Re: How to break a tie in Rank

    Like this??? I'm sure there's a more elegant way of doing this (i.e. without the helper row). Others?
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: How to break a tie in Rank

    Quote Originally Posted by Glenn Kennedy View Post
    Like this??? I'm sure there's a more elegant way of doing this (i.e. without the helper row). Others?
    Ya that's it, but like you said, is there a way to do it without the use of the helper row? Thanks

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2212
    Posts
    37,021

    Re: How to break a tie in Rank

    You can always hide the helper row...

  9. #9
    Forum Contributor
    Join Date
    11-23-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2013
    Posts
    457

    Re: How to break a tie in Rank

    Quote Originally Posted by Glenn Kennedy View Post
    You can always hide the helper row...
    That's true and what I'll end up doing if no one has a better solution. Thanks for your help!

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to break a tie in Rank

    maybe
    =IF(F2="al",SUMPRODUCT(($F$2:$F$7=F2)*($B$2:$B$7+$D$2:$D$7/10000>B2+D2/10000))+1,"")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. RANK - Non unique values, contiguous rank required
    By PaulBo in forum Excel General
    Replies: 9
    Last Post: 02-20-2014, 10:34 AM
  2. How to break ties in the RANK function
    By ducecoop in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-20-2013, 12:38 AM
  3. Rank function - Tie break
    By Dtheater in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-25-2013, 10:06 AM
  4. Replies: 1
    Last Post: 08-15-2005, 05:05 PM
  5. How to use RANK to break multiple ties.
    By Brian in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2005, 02:06 PM

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