Bioequivalence and Bioavailability Forum

Floating point arithmetic, again [Software]

posted by ElMaestro  – Denmark, 2020-12-10 20:40 (1020 d 09:42 ago) – Posting: # 22127
Views: 7,521

Hi Ohlbe,

❝ Type =0.5-0.4-0.1: you'll get the expected result, 0.

❝ Now type =(0.5-0.4-0.1): you'll get -2,77556E-17. What ? Ain't that supposed to be the same calculation ?

Yes it is (or may be) to you and to me, but that isn't how an electronic brain works.

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

In the world of 1's and 0's you cannot represent all decimal numbers that are easily displayed in our common number format using digits of 0 to 9 ("decimal"). Your little equations exemplifies it wonderfully; it is not per se an Excel issue.

Here's R:

0==(0.5-0.4-0.1)
[1] FALSE

You can totally represent .5 (a half) in binary: It is actually .1 because it is 2 raised to the power of -1.
.11 in binary would be 2 raised to the power of -1 plus 2 raised to the power of -2. And so on.

But things are tricky for a figure like 0.1 (one tenth) - in binary you'd probably represent it as .00011001100110011001100110011001100110011001100110011... where the 0011 blocks repeat indefinitely.
In computers are data types have a maximum storage space, like 4 bytes or 8 bytes. So somewhere along the repeating 0011 blocks the computer will truncate the figure so it fits in the memory. And that is the source of your rounding issue.

Computer scientists know never to compare fractional numbers uncritically.
It stinks. The problem is not Excel, but that we have invested computers that use binary when at the same time we like to think of numbers represented with the usual 10 digits ("decimal").

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
      • 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
    • 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
  • Spreadsheet failures, any recent examples? Ohlbe 2020-12-10 18:43
    • Floating point arithmetic, again Helmut 2020-12-10 19:12
      • Floating point arithmetic, againElMaestro 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
                • Depressed Ohlbe 2020-12-10 23:35
                  • Depressed ElMaestro 2020-12-11 07:59
      • Floating point arithmetic, again Ohlbe 2020-12-10 20:13
    • 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