Spreadsheet addiction [Software]

posted by Helmut Homepage – Vienna, Austria, 2019-07-18 18:12 (1352 d 13:14 ago) – Posting: # 20390
Views: 12,393

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 🖖🏼 Довге життя Україна! [image]
Helmut Schütz
[image]

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

Complete thread:

UA Flag
Activity
 Admin contact
22,559 posts in 4,725 threads, 1,607 registered users;
20 visitors (0 registered, 20 guests [including 8 identified bots]).
Forum time: 07:27 CEST (Europe/Vienna)

The penalty for scientific irrelevance is, of course,
that the statistician’s work is ignored by the scientific community.    George E.P. Box

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