+ Reply to Thread
Results 1 to 5 of 5

Ranking up to 15 numbers in column D Ranking skips 7 with a tie at 6 and 2

  1. #1
    Registered User
    Join Date
    11-01-2013
    Location
    Olympia, Washington
    MS-Off Ver
    Excel 2007
    Posts
    37

    Ranking up to 15 numbers in column D Ranking skips 7 with a tie at 6 and 2

    Workbook is attached so that you can see the issue.

    Also in column E ...

    =IF(D1="","",MATCH(LARGE(D:D,ROW()),D:D,FALSE))

    is not producing the correct numbers for some reason.

    In cells 4 and 5 the value is listed as 2. (This issue's foundation comes from a
    two tie in column C cells 2 and 8. I would like to resolve this type of issue by
    first using the corresponding value in column B and if needed backing that up
    with the value in column A.

    Another tie occurs in Column E cells 9 and 10 with it's roots beginning in column
    C cells 4 and 5. This looks to be able to be taken care of as described above.

    Thank you for your help with this one ... way over my head here with this issue
    and am not finding a way to shore through the Excel help guide.

    Leonard

    The problem here is that there is also another tie which looks to really complicate
    things as a two way also tie exist in column E cells 9 and 10.
    Attached Files Attached Files
    Last edited by Securitysports; 11-02-2013 at 11:32 PM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Ranking up to 15 numbers in column D Ranking skips 7 with a tie at 6 and 2 with a tie

    make your rank formula this, =IF(C1="","",RANK(C1,$C$1:$C$10,0)+COUNTIF($C$1:C1,C1)-1)
    as for the col E issue, don't know about that one.

    EDIT: Not sure but when I changed that formula that may have also solved your issue with the match function?
    Last edited by Sam Capricci; 11-02-2013 at 10:04 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    11-01-2013
    Location
    Olympia, Washington
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Ranking up to 15 numbers in column D Ranking skips 7 with a tie at 6 and 2 with a tie

    That appears to have the same issues as it did before. attached it so that you could see what happened. ... Book 3 attached
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-01-2013
    Location
    Olympia, Washington
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Ranking up to 15 numbers in column D Ranking skips 7 with a tie at 6 and 2 with a tie

    Is I use the sum of columns A + B and use that to eliminate ties in column C the issue would disappear.

    Does anyone know a formula that would make that happen?

    Leonard

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: {RESOLVED} Ranking up to 15 numbers in column D Ranking skips 7 with a tie at 6 and 2

    Ok, your thread is marked as "resolved" - is it?
    If not I notice in your second attachment that the formula changes in cell D2, if you click on D1 and drag down it corrects the issue or ties.

+ 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. [SOLVED] Ranking numbers in a column from largest to smallest.
    By Securitysports in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-04-2013, 04:46 AM
  2. [SOLVED] Need a ranking value in a column to top out at 7. Even though there are 15 numbers in the
    By Securitysports in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-03-2013, 09:32 PM
  3. [SOLVED] Ranking numbers in a column from 1 to 15 then stating the rank in another column
    By Securitysports in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-02-2013, 02:09 AM
  4. Replies: 6
    Last Post: 06-01-2010, 06:19 PM
  5. ranking numbers in a column between 2 words
    By jamiepullen in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-02-2008, 11:04 AM

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