I have encountered rather a strange problem in Excel, proven in 2007, 2010 and 2013, on both Windows XP and 7. To reproduce - in Excel:
- Enter the precise value: 12586.4767
- Press Enter
- Go back to the cell you've just typed into, and look at the value in the formula bar along the top...
- Is it the 12586.4767 you just entered? Or is it now 12586.4766999999 ?
does anyone know how one might go about logging this problem with Microsoft?
Ostensibly, this is just a formatting defect, which I believe MS is already aware of and acknowledges in KB 161234 (
click here) [1].
That is, despite appearances, when we enter the constant 12586.4767, its internal representation is the same as VALUE("12586.4767"), which is different from the internal rerpresentation of 12586.4766999999.
So, if we just ignore the appearance, all is fine.
However, a real problem arises when we edit the cell later. At that point, the value does change to the internal representation of 12586.4766999999 because that is how it appears in the Formula Bar.
To embellish Steve's demonstration of the problem, try the following:
1. In A1 in a new worksheet, enter =(A2=A3).
2. In A2, enter 12586.4767.
3. In A3, enter =VALUE("12568.4767"). Note that A1 displays TRUE.
4. Select A2, press F2, then Enter. Note that A1 now displays FALSE.
-----
Explanation....
Generally, there is no "good" explanation for programming defects. The simplest explanation is: "Sh*t happens!".
It is true that 12586.4767, like most non-integers, cannot be represented exactly in 64-bit binary floating-point. In fact, that constant is represented internally exactly as 12586.4766999999,992549419403076171875.
But we would expect that to be displayed as 12586.4767 when rounded to 15 significant digits, denoted by the comma in my presentation of the exact representation above.
In contrast, the constant 12586.4766999999 is represented internally exactly as 12568.4766999998,99210524745285511016845703125.
Obviously, the formatting defect that Steve describes is not due to the limitations of 64-bit binary floating-point representation, since:
1. 12586.4767 is not more than 15 significant digits, the Excel formatting limitation [2]. Any number with 15 significant digits or less should be representable consistently (within the magnitude limitations).
2. VBA has no problem representing that number consistently.
3, The work-around, VALUE("12586.4767"), represents the number consistently and differently from 12586.4767999999.
But it should be noted that KB 161234 obviously over-simplifies the problem description. It probably just describes the example(s) that the user provided when the defect was first reported a very long time ago.
When I investigated this several years, I concluded that I believe the following conditions are necessary and sufficient to cause the formatting defect:
1. The integer part is less than 65536. That is, it fits in 16 bits or less. If the integer part is zero, the decimal fraction must exceed 0.5. And
2. The binary fractional part of the original value -- that is, MOD(...,1), not the decimal representation -- can be represented in 32 bits or less. That is, all bits to the right are zero. And
3. The 16th significant digit of the original value is 5 or more.
I doubt that Rule 3 is actually explicit in the defective Excel implementation that causes the problem. But this formatting defect is evident only when Rule 3 is true because that is the only time we would expect the 15th significant digit to be rounded up, which it is not due to the defect.
For the technically-savvy, here is the break-down of Steve's example.
1. 12586 is less than 65535.
2. The binary fractional part -- MOD(VALUE("12586.4767"),1) -- can be represented as &h3FDE8240,B7800000. That is, it is 1.E8240B78, which is 30 significant bits, less than 32 bits.
3. As noted above, the 16th significant digit of the original value is 9, which is 5 or more.
Hope this helps. Gotta run! I'll post this as-is, with only a little review. I hope I don't have any material typos or unintentional misstatements. I'll do a deeper review later.
-----
[1] http://support.microsoft.com/kb/161234
[2] As explained in the IEEE standard, any 64-bit binary floating-point representation can be reproduced consistently when formatted to 17 significant digits, not 15.
