+ Reply to Thread
Results 1 to 10 of 10

How to break a tie in Rank

  1. 11-23-2013, 04:04 AM #1
    mlbdc2012
    mlbdc2012 is offline
    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.
    Reply With QuoteRegister To Reply
  2. 11-23-2013, 04:42 AM #2
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    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
    Reply With QuoteRegister To Reply
  3. 11-23-2013, 04:52 AM #3
    mlbdc2012
    mlbdc2012 is offline
    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!
    Reply With QuoteRegister To Reply
  4. 11-23-2013, 04:55 AM #4
    mlbdc2012
    mlbdc2012 is offline
    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
    Reply With QuoteRegister To Reply
  5. 11-23-2013, 08:19 AM #5
    mlbdc2012
    mlbdc2012 is offline
    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
    Reply With QuoteRegister To Reply
  6. 11-23-2013, 09:10 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,996

    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
    Reply With QuoteRegister To Reply
  7. 11-23-2013, 09:39 AM #7
    mlbdc2012
    mlbdc2012 is offline
    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
    Reply With QuoteRegister To Reply
  8. 11-23-2013, 09:42 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,996

    Re: How to break a tie in Rank

    You can always hide the helper row...
    Reply With QuoteRegister To Reply
  9. 11-23-2013, 09:49 AM #9
    mlbdc2012
    mlbdc2012 is offline
    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!
    Reply With QuoteRegister To Reply
  10. 11-23-2013, 10:34 AM #10
    martindwilson
    martindwilson is offline
    Forum Expert 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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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. Rank items, select one start date, have remaining dates follow based on rank
    By rob normerica in forum Excel General
    Replies: 1
    Last Post: 08-15-2005, 05:05 PM
  5. [SOLVED] 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1