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:
Code:
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.
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...
