+ Reply to Thread
Results 1 to 4 of 4

Rank Formula skipping a number

  1. #1
    Registered User
    Join Date
    03-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    40

    Rank Formula skipping a number

    I have this formula -

    =RANK(S53,$S$53:$S$152,0)+COUNTIF(S$53:$S53,S53)-1

    It is used for numbers in cells s53-s88. It works pretty much correct, but for some reason it will skip one number. Last week it skipped Rank 20, this week it is skipping rank 15.

    Any ideas why it is doing this?

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

    Re: Rank Formula skipping a number

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "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
    03-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Rank Formula skipping a number

    Sample sheet.xlsx

    If you scroll down to the table that says "Summary Alphabetical"

    I am ranking the Auto% in column B. The Auto% can have duplicate %'s.

    If you scroll down to the next table, you can see that there is nothing with rank of 18, last week it did the same thing with rank 20. I would like to get rid of it doing this.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,317

    Re: Rank Formula skipping a number

    From http://office.microsoft.com/en-us/ma...978.aspx?CTT=1


    Quote Originally Posted by MS Excel help files
    RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
    For some purposes one might want to use a definition of rank that takes ties into account. In the previous example, one would want a revised rank of 5.5 for the number 10. This can be done by adding the following correction factor to the value returned by RANK. This correction factor is appropriate both for the case where rank is computed in descending order ( order = 0 or omitted) or ascending order ( order = nonzero value).

    Correction factor for tied ranks=[COUNT(ref) + 1 RANK(number,ref, 0 ) RANK(number,ref, 1 )]/2.
    See if that is the problem and a possible solution.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    03-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Rank Formula skipping a number

    Not exactly, I think I already accounted for the duplicate ranking issue. If this were the problem, I should have gotten like six values ranked 1.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Rank Formula skipping a number

    The problem is the value in red

    =RANK(S53,$S$53:$S$152,0)+COUNTIF(S$53:$S53,S53)-1

    By ranking down to row 152 you are including the totals row (row 89) in the ranks thereby misranking some entries - restrict the ranking to the end of the table, i.e.

    =RANK(S53,$S$53:$S$88,0)+COUNTIF(S$53:$S53,S53)-1
    Audere est facere

  7. #7
    Registered User
    Join Date
    03-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Rank Formula skipping a number

    Nevermind I figured it out myself. Thanks!

  8. #8
    Registered User
    Join Date
    03-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Rank Formula skipping a number

    Yeah i just figured that out, I hadn't refreshed since you posted that. Thank you for the help.

  9. #9
    Registered User
    Join Date
    12-27-2016
    Location
    California
    MS-Off Ver
    Office Pro 2013
    Posts
    4

    Re: Rank Formula skipping a number

    This formula will allow ties and not skip in ranks when scoring , I can't share my google doc because I can't post links yet sorry

    =SUMPRODUCT((DD34<$F34:$DT34)/COUNTIF($F34:$DT34,$F34:$DT34))+1

+ 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