Spreadsheet addiction [Software]

posted by Helmut Homepage – Vienna, Austria, 2019-07-18 16:12 (794 d 11:11 ago) – Posting: # 20390
Views: 9,333

Dear Ohlbe

» » [...] 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 ? :-D

There is a series of papers about indirect adjusted comparisons in BE published by regulators. All done in Excel.

» * […] 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.

I believe it. [image] is a nasty beast like SAS (© Detlew). Waking up in the middle of the night or – worse – not being able to fall asleep at all is a common side-effect. Since you will never use [image] again, skip this code:

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


More fancy stuff wanted?

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)


[image]

Semitransparent colors in Excel? I don’t think that’s possible. ;-)

Dif-tor heh smusma 🖖
Helmut Schütz
[image]

The quality of responses received is directly proportional to the quality of the question asked. 🚮
Science Quotes

Complete thread:

Activity
 Admin contact
21,691 posts in 4,534 threads, 1,541 registered users;
online 4 (0 registered, 4 guests [including 4 identified bots]).
Forum time: Monday 03:23 CEST (Europe/Vienna)

They were “so intent of making everything numerical”
that they frequently missed seeing
what was there to be seen.    Barbara McClintock

The Bioequivalence and Bioavailability Forum is hosted by
BEBAC Ing. Helmut Schütz
HTML5