## As designed ☺ [Software]

Hi zizou,

» Recently, because of "floating point error" I failed to use spreadsheet for comparisons of differences of two similar values against certain value (my source data were almost all with two decimal places, but some of them were calculated with "full precision" and I wanted to easily check how many of differences are lower than 0.05, unfortunaly there was a case with the error).

Haha, I know this game and therefore, always round before the comparison. » Everyone knows, only smart machines don't.

Can’t be blamed. That’s a property of floating point arithmetic (see also IEEE 754). Unless you have powers of 2 (dec→bin: 1→1, 2→10, 4→100, 8→1000, …) you will always have precision issues in the last significant digits. Everything beyond the 15th (dec-) significant figure is just noise – even with 64bit software on a 64bit OS.
Try this in Excel:
Cells A1–A9: 0, 1, … , 8
Cells B1–B9: =2^A1–A9
Cells C2–B9: =B2-B1, B3-B1, …
Format to 16 decimal places. What do you get?

Little bit deeper into R:

 a <- 5.05 b <- 5 c <- a - b identical(c, 0.05)  FALSE sprintf("%.17f", c)  "0.04999999999999982" a <- 4 b <- 2 c <- a - b identical(c, 2)  TRUE sprintf("%.17f", c) "2.00000000000000000" identical(c, 2L)  FALSE is.integer(c)  FALSE a <- 4L # or as.integer(4) b <- 2L # or as.integer(2) c <- a - b identical(c, 2)  FALSE sprintf("%.17f", c) "2.00000000000000000" identical(c, 2L)  TRUE

GNU Octave 5.1.1.0 (all calculations internally in 64bit double):

 >> format long >> a = 5.05 a = 5.050000000000000 >> b = 5 b = 5 >> b - a ans = -4.999999999999982e-02 >> a = 4 a = 4 >> b = 2 b = 2 >> a - b ans = 2

Not even software for symbolic mathematics does better. Maxima 5.35.12.1 / wxMaxima 14.12.1:

 (%i1) [a:5.05, b:0.5, a-b]; (%o1) [5.05,5,0.04999999999999982] (%i2) [a:4, b:2, a-b]; (%o2) [4,2,2]

Only for R-nerds: Rmpfr. Doesn’t help. library(Rmpfr) a <- mpfr(5.05, 256) b <- mpfr(5, 256) diff(c(b, a)) 1 'mpfr' number of precision  256   bits  0.04999999999999982236431605997495353221893310546875 mpfr(a-b, 32) 1 'mpfr' number of precision  32   bits  0.05 a <- mpfr(4, 256) b <- mpfr(2, 256) diff(c(b, a)) 1 'mpfr' number of precision  256   bits  2

Dif-tor heh smusma 🖖
Helmut Schütz The quality of responses received is directly proportional to the quality of the question asked. 🚮
Science Quotes Ing. Helmut Schütz 