Spreadsheet etc. failures [Software]

posted by zizou – Plzeň, Czech Republic, 2019-07-19 22:18  – Posting: # 20408
Views: 1,825

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:

Activity
 Mix view
Bioequivalence and Bioavailability Forum |  Admin contact
20,002 posts in 4,231 threads, 1,376 registered users;
online 9 (0 registered, 9 guests [including 5 identified bots]).
Forum time (Europe/Vienna): 03:29 UTC

It is a well-known experience that the only truly enjoyable and
profitable way of studying mathematics is the method of
“filling in details” by one’s own efforts.    Cornelius Lanczos

The BIOEQUIVALENCE / BIOAVAILABILITY FORUM is hosted by
BEBAC Ing. Helmut Schütz
HTML5