• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Ranking Excel in Double value?

R

Rizky Tony

New Member
Hi guys,

I need help to rank double type data which have 2 decimal after .

Data:

Score Rank

61.50 1

31.33 2

31.33 4

31.00 4

I use this formula

=RANK.EQ(A2,$A$2:$A$5)+COUNTIF(A$2:A2,A2)-1


Best Regards,

RT
 
Hi Tony,
Firstly welcome to the forum.

Your formula is fine you just need to remove ".EQ"

something like:
=RANK(A2,$A$2:$A$6)+COUNTIF(A$2:A2,A2)-1

Put this in B2
and copy down

Regards,
 
Khalid NGO said:
Hi Tony,
Firstly welcome to the forum.

Your formula is fine you just need to remove ".EQ"

something like:
=RANK(A2,$A$2:$A$6)+COUNTIF(A$2:A2,A2)-1

Put this in B2
and copy down

Regards,
Click to expand...
Hi,
Thanks for your answer but it still can't for this case.. help huhu

61.50 1
31.33 22
30.67 27
50.33 4
31.33 23
36.00 18
31.00 25
34.00 21
36.50 17
35.83 20
40.00 16
48.00 8
48.67 6
42.00 15
31.33 26
59.83 2
46.00 11
36.00 19
21.00 29
42.83 13
25.00 28
46.67 10
48.33 7
43.67 12
50.00 5
42.17 14
48.00 9
58.00 3
31.00 26
 
Hi Tony ,

If you use the following formula :

=RANK.EQ(A2,$A$2:$A$5)

you will get the following ranks :

1 2 2 4

since the second and third values are identical.

If you now add the COUNTIF part to differentiate between duplicates , you will have the formula :

=RANK.EQ(A2,$A$2:$A$5)+COUNTIF(A$2:A2,A2)+1

you will get the ranks :

1 2 3 4

where the COUNTIF part differentiates between the duplicate values.

What kind of a ranking do you wish to get ?

You cannot get 1 2 4 4 , since that is illogical ; a value of 31.33 and another value of 31.00 cannot have the same rank , unless you eliminate decimal places , in which case the first 31.33 will also have the same rank.

In fact with the data in your latest post , the following formula :

=RANK.EQ(A2,$A$2:$A$30)+COUNTIF(A$2:A2,A2)-1

will work for all cases except one value of 31.33 , where you show it having a rank of 26 , whereas it will be 24.

Narayan
 
NARAYANK991 said:
Hi Tony ,

If you use the following formula :

=RANK.EQ(A2,$A$2:$A$5)

you will get the following ranks :

1 2 2 4

since the second and third values are identical.

If you now add the COUNTIF part to differentiate between duplicates , you will have the formula :

=RANK.EQ(A2,$A$2:$A$5)+COUNTIF(A$2:A2,A2)+1

you will get the ranks :

1 2 3 4

where the COUNTIF part differentiates between the duplicate values.

What kind of a ranking do you wish to get ?

You cannot get 1 2 4 4 , since that is illogical ; a value of 31.33 and another value of 31.00 cannot have the same rank , unless you eliminate decimal places , in which case the first 31.33 will also have the same rank.

In fact with the data in your latest post , the following formula :

=RANK.EQ(A2,$A$2:$A$30)+COUNTIF(A$2:A2,A2)-1

will work for all cases except one value of 31.33 , where you show it having a rank of 26 , whereas it will be 24.

Narayan
Click to expand...
Hi Naraya,

Thanks for your reply... yes that's right but i cannot use it while there is 3 identical score.. like my long post before

Regards,
 
Hi Tony ,

Unless you can explain the logic , I don't know how anyone can help ; can you take just 5 or 6 numbers , and explain what their ranking should be , and the logic behind the ranking ?

Narayan
 
NARAYANK991 said:
Hi Tony ,

Unless you can explain the logic , I don't know how anyone can help ; can you take just 5 or 6 numbers , and explain what their ranking should be , and the logic behind the ranking ?

Narayan
Click to expand...
Hi Narayan,

first sorry for the wrong spelling before :p

actually based on my long data before the 24th rank is not there

61.50 1
31.33 22
30.67 27
50.33 4
31.33 23
36.00 18
31.00 25
34.00 21
36.50 17
35.83 20
40.00 16
48.00 8
48.67 6
42.00 15
31.33 26
59.83 2
46.00 11
36.00 19
21.00 29
42.83 13
25.00 28
46.67 10
48.33 7
43.67 12
50.00 5
42.17 14
48.00 9
58.00 3
31.00 26

There is something wrong there on the formula, but i cannot find out what is that...

Regards,
 
Hi Tony ,

With the data you have posted , I get the following ranks :

1
22
27
4
23
18
25
21
17
20
16
8
6
15
24
2
11
19
29
13
28
10
7
12
5
14
9
3
26

Can you upload your file ?

Narayan
 
NARAYANK991 said:
Hi Tony ,

With the data you have posted , I get the following ranks :

1
22
27
4
23
18
25
21
17
20
16
8
6
15
24
2
11
19
29
13
28
10
7
12
5
14
9
3
26

Can you upload your file ?

Narayan
Click to expand...
Yes,

but there is error.. nothing hapened when I click "upload a file" button bellow, any suggestion?

Regards,
 
Hey it is here, I can do it when I change my browser..

Plz check AI38:AI66 the score is there and the rank is AH38:AH66
 

Attachments

  • Ranking Shorting.xlsx
    53.1 KB · Views: 6
Hi Tony,
I think you should just add *0.00001
in countif to get more uniques:

=RANK(AI38,$AI$38:$AI$66)+(COUNTIF($AI$38:$AI$38,AI38)-1)*0.00001
 
NARAYANK991 said:
Hi Tony ,

I think the problem is because we are dealing with an unsorted list ; I sorted the data , and everything seemed to be OK.

Anyway , I have put in a different formula in column AF ; see if this works correctly on the unsorted data.

This formula has been copied from here :

http://www.mrexcel.com/forum/excel-questions/336371-rank-duplicate-values.html

Narayan
Click to expand...
Hi Narayan,

rank number 6 is double there and rank number 29 is not there it is suppose to be rank number 1-29

Regards,
 
Khalid NGO said:
Hi Tony,
I think you should just add *0.00001
in countif to get more uniques:

=RANK(AI38,$AI$38:$AI$66)+(COUNTIF($AI$38:$AI$38,AI38)-1)*0.00001
Click to expand...
Hi Khalid,

If I use your formula ranking number 2 is not showing

=FLOOR.MATH(RANK(AI38,$AI$38:$AI$66)+(COUNTIF($AI$38:$AI$38,AI38)-1)*0.00001)

If use this formula the ranking number 1 is double... I use this formula to get floor from the calculation
 
Maybe

Put in AH38 and copied down and cross:
=COUNTIF(AI$38:AI$66, ">"&AI38)+SUM(IF(AI38=AI$38:AI38, 1, 0))+COUNTIF(AI$37:AI37,AI38)

Hope it works
 

Attachments

  • Ranking Shorting.xlsx
    52.2 KB · Views: 20
Hi guys,

couple years in the future huh? :)

I ran into the same issue and I think I understood what was wrong in your conversation - and what I think Rizky Tony failed to explain.

He meant that with Rank included in any of the functions, there were two identical values, but rank counted them as different. That's why Azumi's solution worked, as its based on COUNTIF and RANK is not used what so ever.

I actually have similar problem in one of my data - I have two exact same values, but RANK gives them 7 and 8. In reality it can be solved by using Azumi's function or by rounding the original number - but I don't understand how is it possible that the rank doesn't work.

Does anybody know?!!!

I include the file and I am very much interested in your thoughts.

71851
 

Attachments

  • Ranking Error.xlsx
    11.3 KB · Views: 5
pavel.skuhrovec
As a new member, please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Start a new post every time you ask a question, even if the theme is similar.

And about Your challenge ...
... take the last number (six) away from both of those values ... then You'll get Your 'expected' result.
... that means - for Excel
- - there is difference between those values
- - it also has limits, how many numbers (15) it can use
 
A formula for Microsoft 365 Excel users.
Whilst RANK does not discriminate between instances of the same value, the modern dynamic array SORT functions do not suffer the same limitation. Thus
Code: 
= LET(
  n, ROWS(value),
  k, SEQUENCE(n),
  p, SORTBY(k, value, -1),
  rank, XMATCH(k, p),
  rank )
reorders the record numbers k by the value field. The XMATCH function searches the reordered list to determine its sorted location.
 
vletm said:
pavel.skuhrovec
As a new member, please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Start a new post every time you ask a question, even if the theme is similar.

And about Your challenge ...
... take the last number (six) away from both of those values ... then You'll get Your 'expected' result.
... that means - for Excel
- - there is difference between those values
- - it also has limits, how many numbers (15) it can use
Click to expand...
Hi,

Thanks for the quick response and advice. :)

About the rules - I know about this rule, in this case I was not sure whether to use new post or not, as I had comment to the old topic as well. At the end I felt that it's better not to create new post.


Anyway - I understand that RANK Function has limit of 15 numbers and it can be dealt with by shortening it or by rounding it. But I don't quite agree that there is a difference between those values - they are exactly the same and even excel says it if you use EXACT or just =.

Would you be so kind and elaborated more on this topic?

Thanks,
P.
 
pavel.skuhrovec
I understand Your 'pain' - here some of my opinions:
You have noticed that 1 is same as 1 as well as 1 could be different than 1 ( those 1 are numbers ... not only which value is one ).
Excel has own limits ... own ways to solve. There are combinations of values, which could give 'not-so-good-results'.
I've notice many times while calculating times. There are combination which work 'not-the-best-way'.
... but ... most of times those work as those should work - okay?
That's why, it would be wise to verify results ... as You have done.
If really need to use those values then it would always solve eg rank manually.

My opinion about Your case ...
of course, if values are same then those should act like same.
BUT
if You have so many decimals .. are those really necessary?
... of course, if have to rank something then ... it should matter, but .. if difference of values is very small then
... would it be more 'correct' to use less decimals?
Same challenge would happen with very huge values.
 
vletm said:
pavel.skuhrovec

if You have so many decimals .. are those really necessary?
... of course, if have to rank something then ... it should matter, but .. if difference of values is very small then
... would it be more 'correct' to use less decimals?
Same challenge would happen with very huge values.
Click to expand...
All I can think of is that more than one binary representation may exist for a given 15-digit decimal number. If that is the case, it is still odd that RANK recognises the differences whereas other functions do not. I rounded the values to a requested 20 sig. fig. and that made the 'dead-heats' show as such.
 
@pavel.skuhrovec

Please have a look of this link regarding RANK function error similar to your case :

sfmagazine.com

Excel: RANK, COUNTIF, and Floating Point Errors - Strategic Finance

Excel normally uses 15 digits of precision, but some features now appear to be using 17, which can cause unexpected problems.
sfmagazine.com

Here are some points to note :

1] Excel auto round down all excess 15 digit limitation number to 15 digits number

2] This round down 15 digits number appear in cell formatting only

3] Excel RANK function can handle 17 digits number calculation

Then,

This can explain cell A8 and A9 look like 2 exact same value, because Excel auto round down to 15 digits number.

However,

A8 and A9 maybe actually in 16 or 17 digits number and with difference making in 16 or 17 digits position

RANK function (can handle 17 digits number calculation) consider they are different number and return result value in 7 & 8 to cell B8 & B9

Thus,

Excel RANK function carry with some design safety factor over the limitation. So that it run more than its specified limitation figure.

Regards
 
Last edited:
@bosco_yip Thank you for the reference to Bill Jelen's article. It seems I was on the right lines and it is more an issue of inconsistency within Excel rather than an actual error. I found it interesting that ROUND created a conformant dataset even though rounding to 20 digits should be a null operation. Maybe we have gone a little off-topic but interesting nevertheless.
 
You must log in or register to reply here.
Back
Top