• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

10.21-10=0.1999999999999999999

R

Raj Hooda

New Member
Hi Chandoo,

Thank you for your blog and I am regular visiting to learn cool stuff. Today we came across a fun calculation or may look like funny to us. We were taking len(10.2-10) and it is showing 17, after checking the formula excel's answer is 10.20-10=0.199999999999999

Could you or anybody can help us to understand this?

Regards,
Raj Hooda
 
Raj Hooda said:
Hi Chandoo,

Thank you for your blog and I am regular visiting to learn cool stuff. Today we came across a fun calculation or may look like funny to us. We were taking len(10.2-10) and it is showing 17, after checking the formula excel's answer is 10.20-10=0.199999999999999

Could you or anybody can help us to understand this?

Regards,
Raj Hooda
Click to expand...
Hi,

Excel works in binary and often there is no precise binary representation of a decimal number so you can get these vary small floating point errors. Have a look at the link which explains where Excel does and doesn't adhere IEEE754 which is the standard for computers storing floating point numbers and in particular the section on rounding.

https://support2.microsoft.com/kb/78113?wa=wsignin1.0
 
Hi Raj ,

The problem is a known one ; when ever you subtract two numbers which are not different from each other by at least one order of magnitude , then you will face this problem.

Suppose in A1 you put the value 10.2 ; in A2 put in the value 10.

In A3 , put in the formula =A1 - A2 , and in A4 put in the formula =A3=0.2 ; you will get the result as FALSE.

Now , if you change the order as follows :

In A1 you put the value 10.2 ; in A2 put in the value 0.2

In A3 , put in the formula =A1 - A2 , and in A4 put in the formula =A3=10 ; you will get the result as TRUE.

This will happen what ever be the value of A1 and A2 ; the issue is that you should never subtract two decimal values which are close to each other , whether they happen to be 0.15 and 0.14 , or they happen to be 1,234,567 and 1,234,566.1

Narayan
 
NARAYANK991 said:
Hi Raj ,

The problem is a known one ; when ever you subtract two numbers which are not different from each other by at least one order of magnitude , then you will face this problem.

Suppose in A1 you put the value 10.2 ; in A2 put in the value 10.

In A3 , put in the formula =A1 - A2 , and in A4 put in the formula =A3=0.2 ; you will get the result as FALSE.

Now , if you change the order as follows :

In A1 you put the value 10.2 ; in A2 put in the value 0.2

In A3 , put in the formula =A1 - A2 , and in A4 put in the formula =A3=10 ; you will get the result as TRUE.

This will happen what ever be the value of A1 and A2 ; the issue is that you should never subtract two decimal values which are close to each other , whether they happen to be 0.15 and 0.14 , or they happen to be 1,234,567 and 1,234,566.1

Narayan
Click to expand...
@NARAYANK991

I think you'll find it's not limited to the numbers close together or far apart, it's all to do with the way floating point numbers are stored. Try this:-

A1 1.2E+200
B1 1E+100
C1 +A1+B1
D1 =C1=A1

As you will see despite adding the numbers together A1 is the same as C1.
 
Thanks you very much Mike, It is very helpful.

I am working on Excel very intensively since last 12 years and never face this before..... Excel is ocean :)
 
Hi Mike ,

You may be right ; can you explain why changing the order of operations gives different results , one of them correct , and the other wrong ?

I did not say my explanation was the only one ; I gave a reason for why some operations may behave the way they do.

Narayan
 
NARAYANK991 said:
Hi Mike ,

You may be right ; can you explain why changing the order of operations gives different results , one of them correct , and the other wrong ?

I did not say my explanation was the only one ; I gave a reason for why some operations may behave the way they do.

Narayan
Click to expand...
Hi,

Yes,

I think the example you gave was this 10.2-10 =.2 and the .2 returned by the formula returns FALSE =a3=.2

If you reverse this to 10.2-0.2 you get 10 so there is no precision error because 10 can be represented exactly in binary.
 
Last edited:
Hi Mike ,

I think I did not make myself clear ; I am uploading a file which has 3 numbers in two columns ; the order of operations in the 2 columns is different. The results are different ; can you explain this ?

Narayan
 

Attachments

  • Mike Example.xlsx
    7.9 KB · Views: 2
NARAYANK991 said:
Hi Mike ,

I think I did not make myself clear ; I am uploading a file which has 3 numbers in two columns ; the order of operations in the 2 columns is different. The results are different ; can you explain this ?

Narayan
Click to expand...
Hi,

In the Col D calculation we have 10.2-10 which equals .2 and we now have a precision error because that .2 is actually .199999999999 This calculated .2 is now compared to a manually entered .2 and they are different by the very small amount.

In the Col F calculation this never occurs. We have 10.2-.2 which returns 10 and there's no problem with that 10, it's an integer and can be represented exactly and we can't have a precision error because there's no decimal to display. That remains true for 10-10 so it returns exactly zero so when the comparison is done calculated 10- input 10 the result is exactly zero.

Hope I explained my self clearly. In the forum where I mainly post; Microsoft Answer, there's a user called joeu2004 who has posted many times on the subject of precision. If you search his posts you'll find he explains it a lot better then me.

http://answers.microsoft.com/en-us/profile/4a1c07ba-3a87-4b0c-98b0-f4608def4860
 
Hi !​
​
Run this VBA demonstration :​
Code: 
Sub DemoCalculationPrecision()
    Const F$ = "20.4 - 19.6", L$ = vbLf & vbLf
    C@ = Evaluate(F)
    D# = Evaluate(F)
    S! = Evaluate(F)
    MsgBox Space(27) & "Calcul  :  " & F & L & "Type :    (@)  Currency =  " & C & L & _
          Space(15) & "(#)    Double   =  " & D & L & _
          Space(15) & "(!)      Single     =  " & S, vbExclamation
End Sub
 
NARAYANK991 said:
Hi Mike ,

I am sorry to be a nuisance , but try this example !

Narayan
Click to expand...
Hi,

In order to be certain it would I think be necessary t get inside Excel but Excel isn't open source so we cant.

In D8 we have a calculated .1 which has a precision error and is .099999999996 This is then compared to a manually entered .1 which doesn't have a precision error and we get a slight difference when comparing but see below in bold

In F8 we have a calculated 10.1 which may; or may not, have a precision error, it doesn't matter because what we do next is F6-F8 or 10.1-10.1. That returns zero and this straight from Excel help explains.

Should an addition or subtraction operation result in a value at or very close to zero, Excel 97 and later will compensate for any error introduced as a result of converting an operand to and from binary. The example above when performed in Excel 97 and later correctly displays 0 or 0.000000000000000E+00 in scientific notation.

Excel treats two values as equal if they are "sufficiently close". MS does not document what "sufficiently close" is so it may be that the second calculation is sufficiently close enough to zero to be treated as such and the first not.

The example referred to can be see at the link given to the OP.

All of this is easily eliminated by using ROUND eg =ROUND(D5-D6,2)
 
Hi @NARAYANK991 / @Mike H.. ,

I might be making my comment in between your discussion, but doesn't this strange thing happening only with subtraction operation and not on others.

Say if I am doing =1/5 than I am getting 0.2 (exactly) or say =1/3 than also the expected result are coming.

Regards,
 
Hi Misra ,

I am not sure but my impression is that it is closeness to 0 which aggravates the problem of storing real numbers which can have an infinite number of decimal places in a format which can have only a finite number ; secondly , it is the operations which result in such small values , which will be a problem.

Addition is less likely to result in such problems than subtraction , especially as I mentioned earlier , where two very large decimal numbers are subtracted and the result is many orders of magnitude smaller than the two numbers themselves.

This is true of almost all computers and all software , unless the software has specific instructions to deal with such values ; you might find the following interesting :

http://en.wikipedia.org/wiki/Arbitrary-precision_arithmetic

Narayan
 
So I did test this "10.2-10" on Excel 2011 for Mac & Numbers on MAC, and the results are same. So I think this has to do something with microprocessor arithmetic unit.

Regards,
 
Somendra Misra said:
Hi @NARAYANK991 / @Mike H.. ,

I might be making my comment in between your discussion, but doesn't this strange thing happening only with subtraction operation and not on others.

Say if I am doing =1/5 than I am getting 0.2 (exactly) or say =1/3 than also the expected result are coming.

Regards,
Click to expand...
Hi,

Thanks for your observations. IMHO there is no precise answer here due to imprecise MS documentation of the 2 issues here that give rise to these small errors.

1. The dubious heuristic where numbers are very close to zero. MS say they round these to zero but 'close to zero' isn't documented.

2. The application of IEEE754 and the known limitation of the ability to represent floating point numbers in binary.
 
You must log in or register to reply here.
Back
Top