As designed ☺ [Software]
❝ 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 :
a <- 5.05
b <- 5
c <- a - b
identical(c, 0.05)
[1] FALSE
sprintf("%.17f", c)
[1] "0.04999999999999982"
a <- 4
b <- 2
c <- a - b
identical(c, 2)
[1] TRUE
sprintf("%.17f", c)
"2.00000000000000000"
identical(c, 2L)
[1] FALSE
is.integer(c)
[1] FALSE
a <- 4L # or as.integer(4)
b <- 2L # or as.integer(2)
c <- a - b
identical(c, 2)
[1] FALSE
sprintf("%.17f", c)
"2.00000000000000000"
identical(c, 2L)
[1] 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 neds: 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
[1] 0.04999999999999982236431605997495353221893310546875
mpfr(a-b, 32)
1 'mpfr' number of precision 32 bits
[1] 0.05
a <- mpfr(4, 256)
b <- mpfr(2, 256)
diff(c(b, a))
1 'mpfr' number of precision 256 bits
[1] 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
Complete thread:
- Spreadsheet failures, any recent examples? ElMaestro 2019-07-18 13:43 [Software]
- Spreadsheet failures, any recent examples? Ohlbe 2019-07-18 14:32
- Spreadsheet addiction Helmut 2019-07-18 16:12
- Nasty beast Ohlbe 2019-07-18 17:30
- Nasty beast Helmut 2019-07-18 20:26
- Nasty beast Ohlbe 2019-07-19 11:32
- Nasty beast ElMaestro 2019-07-19 12:32
- Decidedly off topic Ohlbe 2019-07-19 13:38
- OT: R limbo 101 Helmut 2019-07-19 13:00
- Nasty beast ElMaestro 2019-07-19 12:32
- Nasty beast Ohlbe 2019-07-19 11:32
- Nasty beast Helmut 2019-07-18 20:26
- Spreadsheet addiction Shuanghe 2019-07-18 18:59
- Spreadsheet addiction Helmut 2019-07-18 20:04
- OT: Spreadsheet addiction Shuanghe 2019-07-19 12:41
- OT: Spreadsheet addiction Helmut 2019-07-19 14:29
- OT: Spreadsheet addiction nobody 2019-07-19 15:53
- OT: Spreadsheet addiction Helmut 2019-07-19 19:32
- OT: Spreadsheet addiction nobody 2019-07-19 15:53
- OT: Spreadsheet addiction Helmut 2019-07-19 14:29
- OT: Spreadsheet addiction Shuanghe 2019-07-19 12:41
- Spreadsheet addiction Helmut 2019-07-18 20:04
- Nasty beast Ohlbe 2019-07-18 17:30
- Spreadsheet etc. failures zizou 2019-07-20 00:18
- As designed ☺Helmut 2019-07-20 02:12
- As designed ☺ ElMaestro 2019-07-20 09:08
- To round or not to round… Helmut 2019-07-20 12:53
- To round or not to round… ElMaestro 2019-07-20 19:59
- floating-point math is always more complex than you think it is mittyri 2019-07-20 22:43
- To round or not to round… ElMaestro 2019-07-20 19:59
- To round or not to round… Helmut 2019-07-20 12:53
- As designed ☺ ElMaestro 2019-07-20 09:08
- As designed ☺Helmut 2019-07-20 02:12
- Spreadsheet addiction Helmut 2019-07-18 16:12
- Spreadsheet failures, any recent examples? Ohlbe 2020-12-10 18:43
- Floating point arithmetic, again Helmut 2020-12-10 19:12
- Floating point arithmetic, again ElMaestro 2020-12-10 19:40
- Floating point arithmetic, again Ohlbe 2020-12-10 20:18
- Floating point arithmetic, again ElMaestro 2020-12-10 21:38
- Floating point arithmetic, again Ohlbe 2020-12-10 21:46
- Floating point arithmetic, again ElMaestro 2020-12-10 22:05
- Floating point arithmetic, again Ohlbe 2020-12-10 21:46
- Floating point arithmetic, again ElMaestro 2020-12-10 21:38
- Floating point arithmetic, again Ohlbe 2020-12-10 20:18
- Floating point arithmetic, again Ohlbe 2020-12-10 20:13
- Floating point arithmetic, again ElMaestro 2020-12-10 19:40
- From bad to worse Ohlbe 2020-12-10 22:11
- From bad to worse mittyri 2020-12-11 00:22
- All is good Helmut 2020-12-11 00:36
- Float is float PharmCat 2020-12-18 20:53
- Float is float! Helmut 2020-12-20 23:27
- rational solution in R mittyri 2020-12-21 13:49
- related stuff Helmut 2021-01-14 12:53
- related stuff SDavis 2021-02-09 12:02
- related stuff ElMaestro 2021-02-09 19:55
- related stuff SDavis 2021-02-09 12:02
- related stuff Helmut 2021-01-14 12:53
- rational solution in R mittyri 2020-12-21 13:49
- Float is float! Helmut 2020-12-20 23:27
- Float is float PharmCat 2020-12-18 20:53
- Floating point arithmetic, again Helmut 2020-12-10 19:12
- Spreadsheet failures, any recent examples? Ohlbe 2019-07-18 14:32