Floating point arithmetic, again [Software]
Dear Ohlbe,
» A colleague recently pointed to a strange behaviour in Excel (I have 2013, he has 2016, both give the same result here.
And I have 2000 (don’t laugh).
» Type =0.5-0.4-0.1: you'll get the expected result, 0.
Yep.
» Now type =(0.5-0.4-0.1): you'll get -2,77556E-17. What ? Ain't that supposed to be the same calculation ?
In principle, yes. Unless we could inspect the source code by M$, difficult to tell what happens with the brackets.
» Trying =0=0.5-0.4-0.1: the answer I got was FALSE. WTF, if the answer you get is 0 ? It's not a matter of number of decimals displayed: I tried to add more to the first result, or to switch to scientific notation, but still got 0.
OK, write in
Floating point arithmetic, again (see also this post above).
Try in
What about
?
» A colleague recently pointed to a strange behaviour in Excel (I have 2013, he has 2016, both give the same result here.
And I have 2000 (don’t laugh).
» Type =0.5-0.4-0.1: you'll get the expected result, 0.
Yep.
» Now type =(0.5-0.4-0.1): you'll get -2,77556E-17. What ? Ain't that supposed to be the same calculation ?
In principle, yes. Unless we could inspect the source code by M$, difficult to tell what happens with the brackets.
» Trying =0=0.5-0.4-0.1: the answer I got was FALSE. WTF, if the answer you get is 0 ? It's not a matter of number of decimals displayed: I tried to add more to the first result, or to switch to scientific notation, but still got 0.
OK, write in
=0.5-0.4-0.1
and in =A1=0
- What do you expect?
- What do you get?
![[image]](https://static.bebac.at/img/Favicon_wikipedia_en.gif)
Try in
=16-8-4-2-2
, in =(16-8-4-2-2)
, and in =A1=B1
. I bet it “works” as expected, since these numbers can be converted to binary digits without error.What about
options("digits" = 16)
a <- 0.5-0.4-0.1
b <- (0.5-0.4-0.1)
a; b
[1] -2.775557561562891e-17
[1] -2.775557561562891e-17
identical(a, b)
[1] TRUE
c <- 16-8-4-2-2
d <- (16-8-4-2-2)
c; d
[1] 0
[1] 0
identical(c, d)
[1] TRUE
—
Dif-tor heh smusma 🖖
Helmut Schütz
![[image]](https://static.bebac.at/img/CC by.png)
The quality of responses received is directly proportional to the quality of the question asked. 🚮
Science Quotes
Dif-tor heh smusma 🖖
Helmut Schütz
![[image]](https://static.bebac.at/img/CC by.png)
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, againHelmut 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
- 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, againHelmut 2020-12-10 19:12
- Spreadsheet failures, any recent examples? Ohlbe 2019-07-18 14:32