Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Rank values and apply random rank to duplicates

  1. #1
    New Member
    Join Date
    May 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Rank values and apply random rank to duplicates

    In the cells F4:K4 I have 6 values that are formulated from elsewhere in my workbook. I want to rank these values from 1-6, which is simple using an =RANK. My issue is that sometimes two of these values could be the same. Is there a way to rank all values, but if there are duplicates assign them a random rank that isn't used by another number in the range?

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    12,692
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Rank values and apply random rank to duplicates

    Maybe...


    F
    G
    H
    I
    J
    K
    4
    45
    40
    42
    45
    42
    41
    5
    1
    6
    3
    2
    4
    5


    Formula in F5 copied across
    =COUNTIF($F4:$K4,">"&F4)+COUNTIF($F4:F4,F4)

    M.

  3. #3
    New Member
    Join Date
    Dec 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank values and apply random rank to duplicates

    Hello, avg1995!

    Try this and copy across:

    =IF(COUNTIF($E4:E4,F4)>0,RANK(F4,$F$4:$K$4)+1,RANK(F4,$F$4:$K$4))

    Godspeed!

  4. #4
    New Member
    Join Date
    May 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank values and apply random rank to duplicates

    Quote Originally Posted by estevaoba View Post
    Hello, avg1995!

    Try this and copy across:

    =IF(COUNTIF($E4:E4,F4)>0,RANK(F4,$F$4:$K$4)+1,RANK(F4,$F$4:$K$4))

    Godspeed!
    Thanks for the help!
    This returns 0 for every item when I copy it accross. Any suggestions?

  5. #5
    Board Regular
    Join Date
    Aug 2015
    Posts
    532
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank values and apply random rank to duplicates

    =
    f
    g
    h
    i
    j
    k
    4
    45
    40
    42
    45
    42
    41
    5
    1
    6
    3
    2
    4
    5

    f5=RANK(F4,$F$4:$K$4)+COUNTIF($F$4:F4,F4)-1 copy across

  6. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    12,692
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Rank values and apply random rank to duplicates

    @avg1995

    Have you seen/tried the formula in post #2 ?

    M.
    Last edited by Marcelo Branco; Aug 13th, 2017 at 09:20 AM.

  7. #7
    New Member
    Join Date
    May 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank values and apply random rank to duplicates

    Yes, this one determines which items are duplicates, but ranks each set of duplicates the same.

  8. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    12,692
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Rank values and apply random rank to duplicates

    The outcome i showed in post #2 (F5:K5) is not what you want? If not, could you, please, provide a data sample along with desired results?

    M.
    Last edited by Marcelo Branco; Aug 13th, 2017 at 10:02 AM.

  9. #9
    Board Regular
    Join Date
    Aug 2015
    Posts
    532
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank values and apply random rank to duplicates

    @avg1995

    Post#5 ?

  10. #10
    New Member
    Join Date
    Dec 2016
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Rank values and apply random rank to duplicates

    Quote Originally Posted by avg1995 View Post
    Thanks for the help!
    This returns 0 for every item when I copy it accross. Any suggestions?
    I don't get it! I tested and retested and that formula worked just fine here.
    The figures below, for example, were ranked as desired, despite the repetition.

    3 4 1 5 2 6
    432 349 753 349 652 178

    The formula is built so that, when COUNTIF is greater than 0, i.e, when a value repetition occurs, 1 is added to RANK function result.
    Please make sure the cells with data are addressed correctly in your sheet.

    Godspeed!

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com