SOLVED

Numbers endings in .598 becomes .59799999999

Copper Contributor

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?When 32768.598 is not 32768.598When 32768.598 is not 32768.598

 

 

7 Replies

hi @DCBoyce 

you need to change the cell format and decrease the number of decimals1.png after point to 3 numbers.

best response confirmed by DCBoyce (Copper Contributor)
Solution

@DCBoyce 

 

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.

@DCBoyce 

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

Hi Jihad Al-Jarady

I extended the dps to demonstrate the issue.
I ask you to type in a number ending in .598 above 32768. For example 41254.598 then go back to the cell and see what is in the text entry box.

@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.

@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.

@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. 

1 best response

Accepted Solutions
best response confirmed by DCBoyce (Copper Contributor)
Solution

@DCBoyce 

 

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.

View solution in original post