## Floating point arithmetic, again [Software]

Dear Helmut,

I expect to read 0. Regarding : I would naturally expect it to return TRUE. But on the other hand, typing =0=0.5-0.4-0.1 returns FALSE, and I would not necessarily expect a two-step calculation to yield a result that differs from a one-step calculation. By the way I checked: =0=5-4-1 returns TRUE, so it is not that Excel just compares 0 to 0.5 and ignores the subsequent signs and figures.

returns 0, as expected. Switching to scientific notation gives 0,00E+00. So it doesn't look like we have some hidden decimals there that don't show because of the default display.

returns TRUE. Indeed the result I would normally expect. But this means that typing the comparison and the calculation in one cell, or the calculation in one cell and the comparison in another, gives different results.

Yes. But it works also with

❝ OK, write in `=0.5-0.4-0.1`

and in `=A1=0`

❝ 1. What do you expect?

I expect to read 0. Regarding : I would naturally expect it to return TRUE. But on the other hand, typing =0=0.5-0.4-0.1 returns FALSE, and I would not necessarily expect a two-step calculation to yield a result that differs from a one-step calculation. By the way I checked: =0=5-4-1 returns TRUE, so it is not that Excel just compares 0 to 0.5 and ignores the subsequent signs and figures.

❝ 2. What do you get?

returns 0, as expected. Switching to scientific notation gives 0,00E+00. So it doesn't look like we have some hidden decimals there that don't show because of the default display.

returns TRUE. Indeed the result I would normally expect. But this means that typing the comparison and the calculation in one cell, or the calculation in one cell and the comparison in another, gives different results.

❝ 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.

Yes. But it works also with

`=5-4-1`

, in `=(5-4-1)`

, and in `=A1=B1`

. So it works with 5, which is not a power of 2 ?—

Regards

Ohlbe

Regards

Ohlbe

### 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, againOhlbe 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