## Floating point arithmetic, again [Software]

Dear Helmut,

» OK, write in

» 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

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