+ Reply to Thread
Results 1 to 5 of 5

Un-Skipping the Rank after a duplicate value to get a regular Series!

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Un-Skipping the Rank after a duplicate value to get a regular Series!

    Un-Skipping the Rank after a duplicate value to get a regular Series!

    Col A ------ Col B ------ Col C ------ Col D
    Players ------ Scores ------ Rank ------ Answr
    SACHIN ------ 186 ------ 1 ------ 1
    DHONI ------ 183 ------ 2 ------ 2
    KAPIL ------ 175 ------ 3 ------ 3
    HAYDEN ------ 175 ------ 3 ------ 3
    SEHWAG ------ 167 ------ 5 ------ 4
    BORDER ------ 158 ------ 6 ------ 5
    RAHUL ------ 152 ------ 7 ------ 6
    LARA ------ 148 ------ 8 ------ 7
    RICKY ------ 148 ------ 8 ------ 7
    KEVIN ------ 142 ------ 10 ------ 8
    BOON ------ 133 ------ 11 ------ 9
    FLINTFF ------ 119 ------ 12 ------ 10
    BOTHAM ------ 112 ------ 13 ------ 11

    I want to prevent the skipping of numbers while using the RANK function which skips the next number when it hits a duplicate value..

    I want to get the rank as well as not have the next number skipped..


    Warm Regards
    e4excel
    Last edited by e4excel; 02-14-2009 at 08:04 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Un-Skipping the Rank after a duplicate value to get a regular Series!

    Put this formula in C2 and then copy down:

    =SUMPRODUCT((B$2:B$14>B2)/COUNTIF(B$2:B$14,B$2:B$14&""))+1

    BTW, with this formula, the table doesn't need to be sorted. It will work with the scores randomized.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Un-Skipping the Rank after a duplicate value to get a regular Series!

    Thanks a lot JBeaucaire..!

    It worked well and infact while researching on similar queries on RANKING found similar codes from Daddylonglegs and NBVC..

    Can u please explain the code ? especially the coloured portions...?

    [ =SUMPRODUCT((B$2:B$14>B2)/COUNTIF(B$2:B$14,B$2:B$14&""))+1 ]

    Warm Regards

    e4excel

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Un-Skipping the Rank after a duplicate value to get a regular Series!

    When you rank that way you are effectively counting the number of distinct values that are greater than the value to rank (plus 1 so that the highest rank is 1 not zero).

    A well known formula for counting distinct values in a range is

    =SUMPRODUCT(1/COUNTIF(range,range))

    but that formula will give you an error if there are any blank cells in the range so the solution to that is to change to this

    =SUMPRODUCT((range<>"")/COUNTIF(range,range&""))

    concatenating a "" to the last range in COUNTIF ensures that the COUNTIF part doesn't generate any zeroes, which would result in #DIV/0! error

    To update the above to cope with the ranking issue, then, clearly you only want the distinct values that are greater than the value to rank so it becomes:

    =SUMPRODUCT((range>value)/COUNTIF(range,range&""))+1

    see Aladin Akyurek's explanation here for a better understanding of the distinct count

  5. #5
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Thanks a lot for the explanation DaddyLL

    PHP Code: 
    When you rank that way you are effectively counting the number of distinct values that are greater than the value to rank (plus 1 so that the highest rank is 1 not zero).

    A well known formula for counting distinct values in a range is

    =SUMPRODUCT(1/COUNTIF(range,range))

    but that formula will give you an error if there are any blank cells in the range so the solution to that is to change to this

    =SUMPRODUCT((range<>"")/COUNTIF(range,range&"")) 
    Thanks a lot for the explanation , this is really useful as the code may be only for a particular situation but the knowledge is for life...

    God Bless..

    Warm Regards
    4excel
    Last edited by e4excel; 02-14-2009 at 08:17 AM.

+ 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