+ Reply to Thread
Results 1 to 5 of 5

Rank function - Tie break

  1. #1
    Registered User
    Join Date
    09-25-2013
    Location
    Geneva, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Rank function - Tie break

    Dear co-forumers,
    Congrats on the forum I have gone through many times!
    Now it is my turn to post a question (I would say more of a verification needed).

    So, I have to rank based on a value e.g. costs.
    I have one column (Names) and one column (Costs).
    Not all names have associated costs. Some are 0 (zero) so these will not be ranked.
    From other threads I have found a couple of solutions for the rank to work. But I also needed another parameter: In case of a tie in costs I would like to have the ranks based on the alphabetical order of the names. I have used the below two solutions which seem to work fine I guess. I just wanted to confirm because I don't know if I am missing sth when it comes to alphabetical order vs. value order.

    So my columns would look like this:
    Column A: Ranks result
    Column B: John, Chris, George, Peter, Mike etc...
    Column C: 100, Blank, Blank, 200, 200 etc...

    I have used this that seems to work: =RANK(C2,$C$2:$C$11,0)+SUMPRODUCT(--($C$2:$C$11=C2),--($B$2:$B$11<B2))
    And I have also used this that seem to work: =RANK(C2,C$2:C$11)+SUMPRODUCT((C$2:C$11=C2)*(B$2:B$11<B2))
    There is also this but it doesn't sort alphabetically the "tie" ones (which is ok I guess for those who don't care for the order): =RANK(C4,$C$2:$C$11,0)+COUNTIF($C$2:C4,C4)-1
    I am not familiar with complex formula architecture, maybe the first two are the same thing. BTW what do the two lines (--) mean???

    So my question is: Is it ok to use the first two in order to sort alphabetically in case of a tie in costs? Is there a vital difference between the two of them?

    Thanks for the help and keep up the good work!
    Chris

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

    Re: Rank function - Tie break

    here is a workbook showing ranking alphabetically on ties
    Attached Files Attached Files
    "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

  3. #3
    Registered User
    Join Date
    09-25-2013
    Location
    Geneva, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Rank function - Tie break

    Rank Tie Break Alphabetical.xlsx

    Hi Martin thanks for the file.
    I guess you wanted me to look at Sheet 3.
    Basically I want also to keep the number sequence 1,2,3,4, etc... and also want to avoid adding new column for the decimals.

    I attach the file with the example I mention and the versions of the formula I show above.
    They seem to work but I wanted to double check in case I am missing sth.

    Also for my knowledgd where the two lines (--) help in a formula?

    Thanks,
    Chris

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

    Re: Rank function - Tie break

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html re --
    you can often use * but it depends on what purpose the sumproduct is being used for

  5. #5
    Registered User
    Join Date
    09-25-2013
    Location
    Geneva, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Rank function - Tie break

    Thanks, I should have looked better when I searched for the double unary operator.
    I guess in my case whichever I use is the same -- or *, since it is pretty simple with no big lists and long data.

    So I guess the conditions in the formulas I have placed in the attachment, either -- or *, accomodate the alphabetical order...

    Much appreciated if sone can confirm this.

    Sorry but I am not such a big expert on this thing.

    Thanks,
    Chris

+ 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. 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
  2. [SOLVED] rank function
    By rhmjsh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-22-2013, 10:34 AM
  3. Rank Function
    By Jeana in forum Excel General
    Replies: 4
    Last Post: 06-27-2006, 06:10 PM
  4. rank function
    By donl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-21-2005, 05:59 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1