Bioequivalence and Bioavailability Forum

❝ ❝ [...] which led regulators to conclude they were the only ones who could be trusted to make proper use of such pernicious creations.

❝

❝ Maybe their coding skills are not any better than mine*, and they consider that they will always trust whatever they do with a spreadsheet more than anything they might obtain with R ?

❝ * […] I spent a few more hours trying to have the colour of the plot change based on simple conditions (red if under a threshold value, green above it). I was even waking up at night with ideas on new ways to test. I never succeeded.

op  <- par(no.readonly = TRUE)    # Safe original graphics parameters
par(pty = "s")                    # I want a square plotting region
x   <- runif(n=50, min=1, max=20) # Sample from the uniform distribution
a   <- 0.5                        # Intercept
b   <- 2                          # Slope
y   <- a + b * x + rnorm(n=length(x), mean=0, sd=2) # Response + random error
th  <- 10                         # Threshold
plot(x, y, type = "n", las = 1)   # Important: type="n", will add points later
grid()                            # Nice to have one
abline(h = th, col = "red")       # Line for threshold
abline(lm(y ~ x), lwd = 2, col = "blue")    # Linear regression
points(x[y < th], y[y < th], pch = 21, cex = 1.5,
       col = "red", bg = "#FFD700AA")       # Below threshold
points(x[y >= th],  y[y >= th], pch = 21, cex = 1.5,
       col = "darkgreen", bg = "#32CD32AA") # At least threshold
par(op)                           # Restore original graphics parameters

library(shape)
op  <- par(no.readonly = TRUE)
par(pty = "s")
x   <- runif(n = 50, min = 1, max = 20)
a   <- 0.5
b   <- 2
y   <- a + b * x + rnorm(n=length(x), mean=0, sd=2)
th  <- 10
xlim <- c(0, 21)
ylim <- c(0, 42)
plot(x, y, type = "n", las = 1, xlim = xlim, ylim = ylim)
below <- y[y < th]
above <- y[y >= th]
clr.below <- colorRampPalette(c("#880000", "red"))(length(below))
clr.below <- paste0(clr.below, "80")
clr.above <- colorRampPalette(c("#004000", "#00BB00"))(length(above))
clr.above <- paste0(clr.above, "80")
df.below  <- data.frame(x=x[y < th], y = below, orig = 1:length(below))
df.below  <- df.below[order(df.below$y, decreasing = TRUE), ]
df.below$rank <- length(below):1
df.below$clr <- clr.below[df.below$rank]
df.above <- data.frame(x=x[y >= th], y=above, orig=1:length(above))
df.above <- df.above[order(df.above$y, decreasing = TRUE), ]
df.above$rank <- length(above):1
df.above$clr <- clr.above[df.above$rank]
filledrectangle(wx=30, wy = th, mid = c(10, th/2),
                col = colorRampPalette(c("#FFCCCC", "#FFEEEE"))(256))
grid()
abline(lm(y ~ x), lwd = 2, col = "blue")
abline(h = th, col = "red")
box()
for (j in 1:nrow(df.below)) {
  points(x = df.below[df.below$rank == j, ]$x,
         y = df.below[df.below$rank == j, ]$y,
         pch = 21, cex = 1.5,
         col = df.below[df.below$rank == j, ]$clr,
         bg = df.below[df.below$rank == j, ]$clr)
}
for (j in 1:nrow(df.above)) {
  points(x = df.above[df.above$rank == j, ]$x,
         y = df.above[df.above$rank == j, ]$y,
         pch = 21, cex = 1.5,
         col = df.above[df.above$rank == j, ]$clr,
         bg = df.above[df.above$rank == j, ]$clr)
}
par(op)

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

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

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

❝ A colleague recently pointed to a strange behaviour in Excel (I have 2013, he has 2016, both give the same result here.

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

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

1. What do you expect?
   
2. What do you get?

options("digits" = 16)
a <- 0.5-0.4-0.1
b <- (0.5-0.4-0.1)
a; b
[1] -2.775557561562891e-17
[1] -2.775557561562891e-17
identical(a, b)
[1] TRUE
c <- 16-8-4-2-2
d <- (16-8-4-2-2)
c; d
[1] 0
[1] 0
identical(c, d)
[1] TRUE

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