Apr 14 2022 12:35 PM
I was working with numbers to 3dp and came across an issues where a number that ended in .598 become .5979999999. It only stayed as 598 if I set the cell to "Text".
At first I thought it was a only happening to a specific number that I was using which was 56553.598 that then automatically became 56553.5979999999.
Further testing found that upto 32767.598 stays as is, but 32768.598 and all .598 after become .5979999999.
I have tried it on Excel 2016 and Microsoft 365 with the same result.
Would this be considered a bug or a very ineffective feature?
Apr 14 2022 01:23 PM
hi @DCBoyce
you need to change the cell format and decrease the number of decimals after point to 3 numbers.
Apr 14 2022 01:27 PM - edited Apr 14 2022 01:57 PM
Solution
Yes, this is a formatting defect. MSFT used to document it in KB 161234. The document is no longer available from MSFT. But I did find it in a third-party archive (click here).
Although it is related to the internal binary representation, this has nothing to do with the usual "floating-point error" (I prefer "anomaly"). Note that VBA has no problem with the same numbers.
Yes, one work-around is to enter the number as text. For example, --"56553.598" or VALUE("56553.598") or simply "56553.598" in any arithmetic expression.
We can also write ROUND(56553.598, 3). We will still see 56553.5979999999. But the explicit ROUND will protect the internal binary value from changing later.
-----
KB 161234 documents only one example that demonstrates the problem. There are many more examples. And it does indeed depend on the magnitude of the number.
For example, for constants with a 3-digit decimal fraction, the formatting defect is evident only for values with the "base fraction" 0.098 plus multiples of 0.125 and an integer part between 32768 and 65535.
For constants with a 4-digit decimal fraction, there are 256 instances of the formatting defect with an integer part between 4096 and 65535. We can find many of them using the "base fraction" 0.0196 plus multiples of 0.0625. However, that is incomplete.
For constants with a 5-digit decimal fraction, there are 3008 instances of the formatting defect with an integer part between 1024 and 65535. We can find many of them using the "base fractions" 0.01642 and 0.04767 plus multiples of 0.0625. And again, that is incomplete.
Possible TMI.... More generally, the formatting defect arises under the following conditions:
1. The integer part of the constant is less than 2^16 (i.e. fits within 16 bits). If the integer part is zero, the fraction must exceed 2^-1 (0.5). And
2. The fraction part of the constant 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 exact decimal representation the binary value is 5 or more.
I doubt that Rule 3 is actually explicit in the Excel implementation. But the formatting defect will be evident only when Rule 3 is true, because that is the only time we would expect the 15th significant digit to be rounded up.
Apr 14 2022 01:29 PM
This is due to unavoidable rounding errors. See Floating-point arithmetic may give inaccurate results in Excel
You might use the ROUND function: in F2, use =ROUND(F1+1,3) and fill down
Apr 14 2022 01:31 PM
Apr 14 2022 01:40 PM
@Jihad Al-Jarady wrote: ``you need to change the cell format and decrease the number of decimals``
That only changes the appearance. It does not change the actual cell value.
To demonstrate the insidious nature of the formatting defect, enter =VALUE("56553.598") into A1, and enter =MATCH(56553.598,A2,0) into A2.
Initially, that returns 1, indicating an exact binary match, even though the formula will appear to be =MATCH(56553.5979999999,A2,0) in the Formula Bar.
Now, change that to =MATCH(56553.5979999999,A2,1). That returns #N/A -- not a binary match. Change it back to =MATCH(56553.5979999999,A2,0). That now returns #N/A, as well.
The point is: despite appearances, the internal value correponds to the binary approximation of 56553.598. But it changes to the binary approximation of 56553.5979999999 when the formula is edited, because that is how the constant now appears in the Formula Bar.
Apr 14 2022 01:42 PM - edited Apr 14 2022 02:00 PM
@Hans Vogelaar wrote: ``This is due to unavoidable rounding errors. See Floating-point arithmetic may give inaccurate results in Excel ``
No, it is not. As I explain (but our postings crossed on the ether), VBA has no such formatting problem with the same constants.
PS.... I don't believe that any Excel work-alikes (e.g. Google Sheets) have the same formatting defect, either. It has been a very long time since I tested.
Apr 14 2022 02:01 PM
@Joe User that describes it perfectly. Thanks for digging that out from the archines.
Tried the =ROUND(xxxxx.598,3) and although displays okay; however when used in calculations it does reappear again.
Seeing that it has been there since 2005 at least, unlikely to get resolved anytime soon. I think I will need source the numbers from a database and calculate based on that, or set it as a text format.
Apr 14 2022 01:27 PM - edited Apr 14 2022 01:57 PM
Solution
Yes, this is a formatting defect. MSFT used to document it in KB 161234. The document is no longer available from MSFT. But I did find it in a third-party archive (click here).
Although it is related to the internal binary representation, this has nothing to do with the usual "floating-point error" (I prefer "anomaly"). Note that VBA has no problem with the same numbers.
Yes, one work-around is to enter the number as text. For example, --"56553.598" or VALUE("56553.598") or simply "56553.598" in any arithmetic expression.
We can also write ROUND(56553.598, 3). We will still see 56553.5979999999. But the explicit ROUND will protect the internal binary value from changing later.
-----
KB 161234 documents only one example that demonstrates the problem. There are many more examples. And it does indeed depend on the magnitude of the number.
For example, for constants with a 3-digit decimal fraction, the formatting defect is evident only for values with the "base fraction" 0.098 plus multiples of 0.125 and an integer part between 32768 and 65535.
For constants with a 4-digit decimal fraction, there are 256 instances of the formatting defect with an integer part between 4096 and 65535. We can find many of them using the "base fraction" 0.0196 plus multiples of 0.0625. However, that is incomplete.
For constants with a 5-digit decimal fraction, there are 3008 instances of the formatting defect with an integer part between 1024 and 65535. We can find many of them using the "base fractions" 0.01642 and 0.04767 plus multiples of 0.0625. And again, that is incomplete.
Possible TMI.... More generally, the formatting defect arises under the following conditions:
1. The integer part of the constant is less than 2^16 (i.e. fits within 16 bits). If the integer part is zero, the fraction must exceed 2^-1 (0.5). And
2. The fraction part of the constant 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 exact decimal representation the binary value is 5 or more.
I doubt that Rule 3 is actually explicit in the Excel implementation. But the formatting defect will be evident only when Rule 3 is true, because that is the only time we would expect the 15th significant digit to be rounded up.