RANK gives different rank for same value

[Weedlow]

Hi guys,

I'm getting totally crazy over the RANK-function.

In this sheet, B2 = RANK(A2,$A$2:$A$3) and B3 = RANK(A3,$A$2:$A$3),
and as you can see, it gives me two different ranks for the same value.

These are constants (not formulas) and prove that the value is the same, formula in C2 = A2=A3.

This does not happen if I physically type "5,3", but when I paste it "as values" from another sheet (where I discovered the error). However, the value is nothing more than 5,3 -no more decimals anywhere due to use of ROUND function to get the value 5,3 - so there should really be no difference. ​

[TABLE="width: 209"]
<tbody>[TR]
[TD]Constants[/TD]
[TD]RANK[/TD]
[TD]EQUAL?[/TD]
[/TR]
[TR]
[TD] 5,3[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD] 5,3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is this some Excel-bug? What else can I do? Are there any alternative ranking-formulas?

 

[RoryA]

If it's different when you paste it, then the stored value is not actually 5.3. There is almost certainly a very small difference in the stored values (Excel will actually store up to 17 significant digits not just the 15 it will display). This issue seems to be cropping up a bit recently (i've seen a couple of similar issues with COUNTIF formulas in different forums) so I do wonder if something has been changed recently. I'd suggest using a SUMPRODUCT formula instead, as so far it seems to be unaffected in my experience.

 

[Weedlow]

Thanks RoryA - This is so strange, cus in the formula bar the value is actually exactly 5,3 - and why does not the A2=A3 formula react the same way?

How can I use SUMPRODUCT as a tool of ranking the numbers? Also taking into account the that in my actual working sheet I need unique ranking?
(in my original problem I used RANK in combination with COUNTIF()-1 to in order to obtain unique values - but as RANK treated the numbers as not equal and COUNTIF did, the result would not be a unique number after all)

 

[Linford]

Thanks RoryA - This is so strange, cus in the formula bar the value is actually exactly 5,3 - and why does not the A2=A3 formula react the same way?

How can I use SUMPRODUCT as a tool of ranking the numbers? Also taking into account the that in my actual working sheet I need unique ranking?
(in my original problem I used RANK in combination with COUNTIF()-1 to in order to obtain unique values - but as RANK treated the numbers as not equal and COUNTIF did, the result would not be a unique number after all)

Hello Weedlow, have you discovered where is the catch? I have faced literally the same problem I cannot continue my work until I solve this... OK, if there is the other way I can get correct result I will use it... Still, I'd be wondering about this problem... I'm uploading a screenshot...

Thank you,

Linford

 

[glfiedler]

According to Microsoft support web site the Rank function is being replaced by Rank.avg and Rank.eq. Sorry I cannot paste the link since I am using my Kindle e-reader. They claim there is better accuracy with the new ones.

 

[joeu2004]

I have faced literally the same problem [....] if there is the other way I can get correct result I will use it... Still, I'd be wondering about this problem

It is a common issue with 64-bit binary floating-point, which is how Excel represents numbers in memory. In a nutshell, most decimal fractions cannot be represented exactly. And the approximation of a particular decimal fraction varies depending on the magnitude of the number.

IMHO, the best solution is: whenever you calculate a value that you expect to be accurate to some number of decimal places, explicitly round the calculation to that number of decimal places. (Not to an arbitrary number of decimal place like 10, as some people suggest.)

-----

I cannot read your screen image. And for this problem, a screen image is almost useless without the underlying formulas and other referenced values.

IMHO, it is better to upload an Excel file (redacted) that demonstrates the problem to a file-sharing website, and post the download URL in a response here. Some participants object, because they cannot or will not download files. But often, the devil is in details that we cannot see, regardless of the cell format. The problem in the original posting is a good example.

(Nevertheless, you should temporarily format all cells so that they display 15 significant digits. That might be sufficient for you to get some idea of the root cause of the problem.)

Suppose A1 is 2.2, A2 is 3.1, and A3 is =SUM(A1:A2).

A3=5.3 returns TRUE. And if we format A3 with 14 decimal places, it displays 5.30000000000000, which suggests that it is indeed exactly 5.3.

But =MATCH(5.3,A3,0) returns #N/A, which indicates they do not match.

If we change A3 to =ROUND(SUM(A2:A1),1), =MATCH(5.3,A3,0) returns 1, which indicates a match.

First, A3=5.3 returns TRUE because the "=" operator rounds the left and right expressions to 15 significant digits for the purpose of the comparison. And as the reformatting demonstrates, A3 does indeed rounds to 5.3.

But the MATCH function compares the exact binary value. Obviously, the binary representation of the value in A3 is not the same as binary representation of 5.3, despite appearances.

In fact, we can discover the difference with the formula =A3-TEXT(A3,"0.00000000000000E+0")-0. It returns about 8.88E-16.

The redundant -0 is necessary sometimes to avoid an Excel trick to make us believe that the difference is exactly zero (0.00E+00).

The values on the right are the exact decimal representations of the binary values:

2.2        2.20000000000000,017763568394002504646778106689453125
3.1        3.10000000000000,0088817841970012523233890533447265625
5.3        5.29999999999999,982236431605997495353221893310546875
2.2+3.1    5.30000000000000,0710542735760100185871124267578125

I use period for the decimal place and comma to demarcate the first 15 significant digits, which is all that Excel will format (rounded).

As we see, the binary value is much more precise than even 17 significant digits. But the IEEE 754 standard does require up to 17, not 15, significant digits in order to covert between decimal and binary with no loss of precision. Excel does not store 17 significant digits into memory; the numeric values are represented in binary. But Excel does represent binary values with up to 17 significant decimal digits when we save to an "xlsx" or "xlsm" file, which is a compressed XML file.

But what is most important thing to note is: the difference between 5.3 and 2.2+3.1.

That is why RANK returns different values for A1 and A2: they are indeed different. Apparently, RANK also compares the exact binary value, not the decimal representation rounded to 15 significant digits.

 

[joeu2004]

if we format A3 with 14 decimal places, it displays 5.30000000000000, which suggests that it is indeed exactly 5.3.

I should have written: which mistakenly (misleadingly) suggests that it is exactly 5.3, when it is not.

The points that I was trying to make are:

1. Excel formats only up to 15 significant digits, but the actual value might have many more digits to the right.

2. Excel comparison operations are inconsistent, sometimes comparing values rounded to 15 significant digits and sometimes comparing the binary value, which might be more precise.

That leads to apparent inconsistencies like how RANK behaves versus our expectations based on the data appears.

 

[Linford]

Thank you all for support; here is the link with file that demonstrates problem I have: RANK issue.xlsx

Dear Joe, meanwhile I'll try your proposed idea with ROUND, MATCH, TEXT... tnx

All the best,

Linford

 

[joeu2004]

@Linford.... The value in G1 is not "literally" 1.4957264957265%. That is, you did not enter 1.4957264957265%.

Instead, presumably you did copy-and-paste-value from a cell where it was calculated.

That is self-evident because =G1-TEXT(G1,"0.00000000000000E+0")-0 displays -4.16E-17 when formatted as Scientific.

In contast, the value in D1 is exactly 1.4957264957265%; that is, the binary representation of that constant.

Either you did enter 1.4957264957265% literally, or you did copy-and-paste-value from a cell where the exact value was calculated by coincidence.

That is self-evident because =D1-TEXT(D1,"0.00000000000000E+0")-0 displays 0.00E+0 (exact zero) when formatted as Scientific.

(Note: It is important to use the Scientific format in order to recognize exact zero, as well as the infinitesimal differences. With some other formats, Excel might display zero because it rounds according to the format.)

That explains why =RANK(D1,$B$1:$J$1) and =COUNTIF($B1:D1,D1) return different values than =RANK(G1,$B$1:$J$1) and =COUNTIF($B1:G1,G1).

As I explained previously, =D1=G1 returns TRUE, a contradition, because Excel rounds D1 and G1 to 15 significant digits (i.e. 1.49572649572650%) for the purpose of the "=" comparison.

That is a quirk of Excel specifically. (Although OpenOffice Calc and Google Sheets might do the same thing. I don't know.)

My point is: the inconsistency is due to Excel's inconsistent methods of comparison.

-----
PS.... The exact values represented in decimal are:

D1: 0.0149572649572649,9953586863256305150571279227733612060546875
G1: 0.0149572649572649,5790250520911968123982660472393035888671875

I use period for the decimal place and comma to demarcate the first 15 significant digits, which is all that Excel will format (rounded).

 

[joeu2004]

=D1=G1 returns TRUE, a contradition, because Excel rounds D1 and G1 to 15 significant digits (i.e. 1.49572649572650%) for the purpose of the "=" comparison. That is a quirk of Excel specifically. (Although OpenOffice Calc and Google Sheets might do the same thing. I don't know.)

FYI, Google Sheets has its own share of inconsistencies. With =0.014957264957265 - "4.17E-17" in A1 (the value in your G1) and =0.014957264957265 in A2 (the value in your D1):

=A1=A2 returns TRUE, like Excel
=MATCH(A1,A2,0) returns 1, unlike Excel, but consistent with A1=A2
=A1-A2=0 returns FALSE, like Excel, but mathematically inconsistent with A1=A2
=A1-A2 returns -4.17E-17, the actual arithmetic difference, unlike Excel, but consistent with A1-A2=0
=RANK(A1,A1:A2) returns 2, but =RANK(A2,A1:A2) returns 1, like Excel and consistent with A1-A2, but inconsistent with A1=A2

Like Excel, it appears that Google Sheets formats only up to the first 15 significant digits (rounded), replacing any digits to the right with zero.