Bioequivalence and Bioavailability Forum • Spreadsheet etc. failures

Spreadsheet etc. failures [Software]

posted by zizou – Plzeň, Czech Republic, 2019-07-20 02:18 (1882 d 19:17 ago) – Posting: # 20408
Views: 15,715

Hello everybody and nobody.

❝ Nope. I have examples of users using the wrong functions, or unlocked spreadsheets getting modified at some point, but that's about all. The problem is most often somewhere between the chair and the keyboard: wetware rather than software.

❝

❝ Well, there are some "features" of Excel you need to be aware of when using it for stats, of course. Already mentioned on the forum.

Right.

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

See following examples:

| | Default View | Number Format with Cell | Formula | in General Format | Additional Decimal Places ──────┼──────────┼───────────────────┼─────────────────────────── A1 | 25.05 | | A2 | 25 | | A3 | =A1-A2 | 0.05 | 0.0500000000000007 A4 | =A3=0.05 | FALSE | A5 | =A3>0.05 | TRUE | Difference is higher than expected. | | Default View | Number Format with Cell | Formula | in General Format | Additional Decimal Places ──────┼──────────┼───────────────────┼─────────────────────────── A1 | 5.05 | | A2 | 5 | | A3 | =A1-A2 | 0.05 | 0.0499999999999998 A4 | =A3=0.05 | FALSE | A5 | =A3<0.05 | TRUE | Difference is lower than expected.

Nevertheless it is not problem of spreadsheet itself, but only precision issue. In R:

a=5.05 b=5 a-b #[1] 0.05 a-b==0.05 #[1] FALSE a-b<0.05 #[1] TRUE

Everyone knows, only smart machines don't.

Best regards,
zizou

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. failureszizou 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, 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
        
        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