Ranking Issue - Page 3

Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 27 of 27

Thread: Ranking Issue

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

    Default Re: Ranking Issue

     
    Quote Originally Posted by kelly mort View Post
    Post a small sample of how you want your output look like.
    Ok
    If i use rank only this will be result , as you see below rank 17 , 10 , 2, 15 and 4 all of this ranks duplicate
    % Rank
    Sales 24 100% 26
    Sales 25 84% 46
    Sales 26 105% 17
    Sales 27 115% 10
    Sales 28 130% 2
    Sales 29 97% 31
    Sales 30 105% 17
    Sales 90 90% 43
    Sales 91 93% 37
    Sales 92 96% 32
    Sales 93 101% 24
    Sales 94 110% 14
    Sales 95 59% 49
    Sales 96 105% 17
    Sales 118 115% 10
    Sales 119 90% 42
    Sales 120 96% 34
    Sales 121 93% 39
    Sales 122 93% 38
    Sales 123 139% 1
    Sales 184 100% 28
    Sales 185 97% 30
    Sales 186 89% 44
    Sales 187 40% 50
    Sales 188 105% 17
    Sales 242 119% 8
    Sales 243 79% 48
    Sales 244 101% 25
    Sales 245 98% 29
    Sales 246 109% 15
    Sales 247 91% 41
    Sales 248 96% 35
    Sales 249 81% 47
    Sales 250 96% 33
    Sales 251 114% 13
    Sales 320 104% 23
    Sales 321 105% 17
    Sales 322 120% 7
    Sales 323 125% 4
    Sales 324 105% 17
    Sales 325 96% 36
    Sales 326 124% 6
    Sales 327 85% 45
    Sales 328 115% 10
    Sales 329 109% 15
    Sales 330 125% 4
    Sales 331 91% 40
    Sales 338 100% 27
    Sales 340 115% 9
    Sales 341 130% 2


    if use rank + count if
    it gives me unique rank as you see below
    But there's some numbers missing like #9

    % Rank
    Sales 24 100% 26
    Sales 25 84% 46
    Sales 26 105% 17
    Sales 27 115% 10
    Sales 28 130% 2
    Sales 29 97% 31
    Sales 30 105% 18
    Sales 90 90% 43
    Sales 91 93% 37
    Sales 92 96% 32
    Sales 93 101% 24
    Sales 94 110% 14
    Sales 95 59% 49
    Sales 96 105% 19
    Sales 118 115% 11
    Sales 119 90% 42
    Sales 120 96% 34
    Sales 121 93% 39
    Sales 122 93% 38
    Sales 123 139% 1
    Sales 184 100% 28
    Sales 185 97% 30
    Sales 186 89% 44
    Sales 187 40% 50
    Sales 188 105% 20
    Sales 242 119% 8
    Sales 243 79% 48
    Sales 244 101% 25
    Sales 245 98% 29
    Sales 246 109% 15
    Sales 247 91% 41
    Sales 248 96% 35
    Sales 249 81% 47
    Sales 250 96% 33
    Sales 251 114% 13
    Sales 320 104% 23
    Sales 321 105% 21
    Sales 322 120% 7
    Sales 323 125% 4
    Sales 324 105% 22
    Sales 325 96% 36
    Sales 326 124% 6
    Sales 327 85% 45
    Sales 328 115% 12
    Sales 329 109% 16
    Sales 330 125% 5
    Sales 331 91% 40
    Sales 338 100% 27
    Sales 340 115% 12
    Sales 341 130% 3



    you can download example sheet ant try it

  2. #22
    Board Regular
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    703
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    So let us say you are ranking col. B then use
    =iferror(rank(b1,$b$1:$b$10000)+countifs(b$2:b2,b2)-1,"")

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

    Default Re: Ranking Issue

    Quote Originally Posted by kelly mort View Post
    So let us say you are ranking col. B then use
    =iferror(rank(b1,$b$1:$b$10000)+countifs(b$2:b2,b2)-1,"")
    that's exactly what i do but i have missing numbers 9 or 10 or any other number
    how to avoid that

  4. #24
    Board Regular
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    703
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    I have your sheet here and i cant really get your intentions properly.

  5. #25
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    856
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

    I ran Mohamed's sheet past the Excel MVP group and it is a very intriguing situation.

    It is generally well known that Excel only had 15 digits of precision. That is why you find so many people complaining that they can not store their 16-digit credit card numbers in Excel without losing the last digit.

    Some of my friends cracked open the XML of Mohamed's worksheet and discovered that Excel is actually storing seventeen digits . It turns out that Mohamed has four cells that appear in Excel as 115%.
    But in the XML, those four cells are actually:
    0.11499999999999999
    0.11499999999999999
    0.11499999999999999
    0.11500000000000000

    The maddening thing here is that Excel won't show more than 15 digits, so we can never see in the Excel user interface nor in VBA that the cell contains anything other than 115%.

    COUNTIFS and =A2=A3 all treat all four of those cells as if they contain 115%.

    But we can tell from how the RANK function and the Sort feature work that Excel is using the 16th and 17th digits. This causes problems.

    Mohamed - your workaround is that when you calculate the percentage, you need to wrap that calculation in a rounding function to keep only 14 or 15 digits. =ROUND(A2/B2,15) will prevent this from happening again.

    I am guessing this won't make you happy. But the fact is that it will solve your problem. The other solution would be to push Microsoft to make RANK and sorting less accurate, but that will slow Excel calculation down for 749,999,999 other people, and I am not going to advocate for less accuracy and slower Excel.
    View a collection of recent Excel articles in the Excel Daily News

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

    Default Re: Ranking Issue

    Quote Originally Posted by MrExcel View Post
    I ran Mohamed's sheet past the Excel MVP group and it is a very intriguing situation.

    It is generally well known that Excel only had 15 digits of precision. That is why you find so many people complaining that they can not store their 16-digit credit card numbers in Excel without losing the last digit.

    Some of my friends cracked open the XML of Mohamed's worksheet and discovered that Excel is actually storing seventeen digits . It turns out that Mohamed has four cells that appear in Excel as 115%.
    But in the XML, those four cells are actually:
    0.11499999999999999
    0.11499999999999999
    0.11499999999999999
    0.11500000000000000

    The maddening thing here is that Excel won't show more than 15 digits, so we can never see in the Excel user interface nor in VBA that the cell contains anything other than 115%.

    COUNTIFS and =A2=A3 all treat all four of those cells as if they contain 115%.

    But we can tell from how the RANK function and the Sort feature work that Excel is using the 16th and 17th digits. This causes problems.

    Mohamed - your workaround is that when you calculate the percentage, you need to wrap that calculation in a rounding function to keep only 14 or 15 digits. =ROUND(A2/B2,15) will prevent this from happening again.

    I am guessing this won't make you happy. But the fact is that it will solve your problem. The other solution would be to push Microsoft to make RANK and sorting less accurate, but that will slow Excel calculation down for 749,999,999 other people, and I am not going to advocate for less accuracy and slower Excel.
    Thanks MR Excel For your caring on my case
    I'll use round to get rank as a facade

    thanks for everyone reply to me

  7. #27
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    856
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Ranking Issue

      
    The 15/17 digit issue became the subject of my video # 2110 today:
    https://youtu.be/WZfjmbEDbfI
    View a collection of recent Excel articles in the Excel Daily News

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