Spreadsheet addiction [Software]

posted by Helmut Homepage – Vienna, Austria, 2019-07-18 18:12 (1714 d 16:44 ago) – Posting: # 20390
Views: 14,036

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,957 posts in 4,819 threads, 1,640 registered users;
79 visitors (0 registered, 79 guests [including 6 identified bots]).
Forum time: 09:56 CET (Europe/Vienna)

Nothing shows a lack of mathematical education more
than an overly precise calculation.    Carl Friedrich Gauß

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