4
x = 4.2 - 0.1

vb.net gives 4.1000000000000005
python gives 4.1000000000000005

Excel gives 4.1
Google calc gives 4.1

What is the reason this happens?

Honest Abe
  • 8,430
  • 4
  • 49
  • 64
bugtussle
  • 1,416
  • 11
  • 15
  • 2
    Google calc explicity casts the input to a float and then truncates the acuracy problems. See http://en.wikipedia.org/wiki/Floating_point#Minimizing_the_effect_of_accuracy_problems. – Nathan Kleyn Oct 20 '09 at 14:30
  • 7
    read this three or four times: http://docs.sun.com/source/806-3568/ncg_goldberg.html – nlucaroni Oct 20 '09 at 14:31
  • 3
    Duplicate. http://stackoverflow.com/questions/1089018/why-cant-decimal-numbers-be-represented-exactly-in-binary, http://stackoverflow.com/questions/177506/why-do-i-see-a-double-variable-initialized-to-some-value-like-21-4-as-21-39999961, http://stackoverflow.com/questions/963873/1-265-10000-126499-99999999999, etc. – S.Lott Oct 20 '09 at 14:54
  • Not really. The math and results are different. – Stefan Kendall Oct 20 '09 at 15:28
  • @Stefan Kendall: In what way is the math different? It's the standard issue with floating-point numbers. Interestingly Excel rounds before displaying. Google Calc rounds before displaying. Real program (vb and Python) don't round unless you add that code. This is the standard question asked over and over again. – S.Lott Oct 20 '09 at 16:01
  • @ S.Lott: My google fu failed. I searched before posting, but did not find anything. I can imagine that this question comes up all the time in a number of different un-googleable variations – bugtussle Oct 20 '09 at 16:30
  • Excel may not be using binary floating-point numbers at all; there are plenty of other representations (eg. decimal fractions), they're just slower, since they're not implemented in hardware. Also, Python does round formatted numbers as of 3.1; see http://docs.python.org/3.1/whatsnew/3.1.html. That's just for display, of course; the math is unchanged. – Glenn Maynard Oct 20 '09 at 20:35
  • @Glenn Maynard: Since XLRD and POI show that simple, dumb floating point values are what are used for persistence in .XLS files, then I conclude that it uses simple floating-point values and lots of formatting tricks and rules. – S.Lott Oct 21 '09 at 02:10
  • @S.Lott: Excel most deffinetely uses floating point for decimal number representation. http://support.microsoft.com/kb/78113 There is actually a lot of Excel/Float related bugs that make Excel unsuitable for serious number crunching. The "most famous" can be seen here http://www.lomont.org/Math/Papers/2007/Excel2007/Excel2007Bug.pdf – Esteban Küber Oct 21 '09 at 16:55
  • @S.Lott: Each equation presents unique floating point issues that are handled with different general methods. Certainly, this is a "float issue," but I wouldn't be so quick to judge all "float issues" the same. If I had tan(x)/cos(x) * (1 - cos(x)2) - sin(x), when would I have issues while performing float math? The root issues are the same, but the presentation affects how you can deal with it. – Stefan Kendall Oct 21 '09 at 18:55

4 Answers4

15

Float/double precision.

You must remember that in binary, 4.1 = 4 + 1/10. 1/10 is an infinitely repeating sum in binary, much like 1/9 is an infinite sum in decimal.

Stefan Kendall
  • 66,414
  • 68
  • 253
  • 406
10
>>> x = 4.2 - 0.1 
>>> x
4.1000000000000005

>>>>print(x)
4.1

This happens because of how numbers are stored internally.

Computers represent numbers in binary, instead of decimal, as us humans are used to. With floating point numbers, computers have to make an approximation to the closest binary floating point value.

Almost all machines today (November 2000) use IEEE-754 floating point arithmetic, and almost all platforms map Python floats to IEEE-754 “double precision”. 754 doubles contain 53 bits of precision, so on input the computer strives to convert 0.1 to the closest fraction it can of the form J/2***N* where J is an integer containing exactly 53 bits.

If you print the number, it will show the approximation, truncated to a normal value. For example, the real value of 0.1 is 0.1000000000000000055511151231257827021181583404541015625.

If you really need a base 10 based number (if you don't know the answer to this question, you don't), you could use (in Python) decimal.Decimal:

>>> from decimal import Decimal
>>> Decimal("4.2") - Decimal("0.1")
Decimal("4.1")

Binary floating-point arithmetic holds many surprises like this. The problem with “0.1” is explained in precise detail below, in the “Representation Error” section. See The Perils of Floating Point for a more complete account of other common surprises.

As that says near the end, “there are no easy answers.” Still, don’t be unduly wary of floating-point! The errors in Python float operations are inherited from the floating-point hardware, and on most machines are on the order of no more than 1 part in 2**53 per operation. That’s more than adequate for most tasks, but you do need to keep in mind that it’s not decimal arithmetic, and that every float operation can suffer a new rounding error.

While pathological cases do exist, for most casual use of floating-point arithmetic you’ll see the result you expect in the end if you simply round the display of your final results to the number of decimal digits you expect. str() usually suffices, and for finer control see the str.format() method’s format specifiers in Format String Syntax.

Honest Abe
  • 8,430
  • 4
  • 49
  • 64
Esteban Küber
  • 36,388
  • 15
  • 79
  • 97
  • 1
    the real value of 1.1 is 0.1000000000000000055511151231257827021181583404541015625. => off by 1.0 – foosion Oct 20 '09 at 15:22
4

There is no problem, really. It is just the way floats work (their internal binary representation). Anyway:

>>> from decimal import Decimal
>>> Decimal('4.2')-Decimal('0.1')
Decimal('4.1')
shylent
  • 10,076
  • 6
  • 38
  • 55
2

In vb.net, you can avoid this problem by using Decimal type instead:

Dim x As Decimal = 4.2D - 0.1D

The result is 4.1 .

Meta-Knight
  • 17,626
  • 1
  • 48
  • 58