Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Ranking Issue

  1. #1
    New Member
    Join Date
    Nov 2016
    Location
    egypt
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Ranking Issue

    Hi Dears

    i use this formula to get unique rank

    Code:
    =IFERROR((COUNTIF(F5:F$426,F5)-1)+RANK(F5,F$6:F$426),"")


    But some times i didn't get some number
    like you see here . no 10 rank




    so ho to avoid this issue
    example sheet

    Thank

  2. #2
    Board Regular
    Join Date
    Jul 2017
    Location
    Sherbrooke, Canada
    Posts
    289
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    Hello,

    I see in your formula that you have 2 part in a sum. It's possible that this summation never equal some number.

    I suggest you to use 2 formula separately (like in column H and I) with a sum of those two (like in J) to observe if this is your problem.

    If this wasn't the origin of your problematic, can you explain why you use COUNTIF?

  3. #3
    New Member
    Join Date
    Nov 2016
    Location
    egypt
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    Quote Originally Posted by Roxxien View Post
    Hello,

    I see in your formula that you have 2 part in a sum. It's possible that this summation never equal some number.

    I suggest you to use 2 formula separately (like in column H and I) with a sum of those two (like in J) to observe if this is your problem.

    If this wasn't the origin of your problematic, can you explain why you use COUNTIF?
    Thanks for your reply
    I tried what you suggested but same result
    and about why i use count if
    i get this formula from this page which get ranks without duplicates

  4. #4
    Board Regular
    Join Date
    Jul 2017
    Location
    Sherbrooke, Canada
    Posts
    289
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    Hum ok,

    From what I'm seeing, to didn't use the same syntax for your COUNTIF. In your reference, they are locking the first cells of the range and you locked the last one.

    Replace your formula by the following one. I don't know if it will solve your issue but it won't hurt to try.

    Code:
    =IFERROR((COUNTIF(F$5:F5,F5)-1)+RANK(F5,F$6:F$426),"")
    Last edited by Roxxien; Jul 10th, 2017 at 02:43 PM.

  5. #5
    New Member
    Join Date
    Nov 2016
    Location
    egypt
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    Quote Originally Posted by Roxxien View Post
    Hum ok,

    From what I'm seeing, to didn't use the same syntax for your COUNTIF. In your reference, they are locking the first cells of the range and you locked the last one.

    Replace your formula by the following one. I don't know if it will solve your issue but it won't hurt to try.

    Code:
    =IFERROR((COUNTIF(F$5:F5,F5)-1)+RANK(F5,F$6:F$426),"")
    I tried It But now rank 9 is missing !!!

  6. #6
    Board Regular
    Join Date
    Jul 2017
    Location
    Sherbrooke, Canada
    Posts
    289
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    Ok this already help to have the right information. However, now that I'm reading character by character your formula, I see something that is strange. Why are you searching the rank of the F5 cell in a range from F6 to F426? Is it possible that your F5 value is your missing rank?

  7. #7
    New Member
    Join Date
    Nov 2016
    Location
    egypt
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    Quote Originally Posted by Roxxien View Post
    Ok this already help to have the right information. However, now that I'm reading character by character your formula, I see something that is strange. Why are you searching the rank of the F5 cell in a range from F6 to F426? Is it possible that your F5 value is your missing rank?
    Ok , now i corrected it to be the below but also Still missing number
    you can try it yourself
    Code:
    =IFERROR(RANK(F5,$F$5:$F$425)+COUNTIF($F$5:F5,F5)-1,"")
    Last edited by mohamed ali abdelgawad; Jul 10th, 2017 at 03:04 PM.

  8. #8
    Board Regular
    Join Date
    Jul 2017
    Location
    Sherbrooke, Canada
    Posts
    289
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    Why did you changed your F426 to F425?

  9. #9
    New Member
    Join Date
    Nov 2016
    Location
    egypt
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    Quote Originally Posted by Roxxien View Post
    Why did you changed your F426 to F425?
    because this is the range of data

  10. #10
    New Member
    Join Date
    Nov 2016
    Location
    egypt
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    Quote Originally Posted by Roxxien View Post
    Why did you changed your F426 to F425?
    Please open example sheet and check it

User Tag List

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
  •  


DMCA.com