R equivalent of excel exponential trendline (including equation)

Question

The attached picture shows the exponential curve fitting and the exponential equation for this set of data I have. However, I have 219 samples just like the picture that I would like to code in r & get the value for the exponential parameters (the power and the constant). I have tried lm() and nls(). With lm(), I realized in doesn't give me the same exponential format as excel. By that I mean exponential function including e (refer to the equation in the attached image). With nls() the values seem so small to me & don't match what excel gives me.

Answer 1

Difficult to say without a reproducible example, but your code could be something like this:

library(ggplot2)

mod <- nls(y ~ a * exp(b * x), data = df, start = list(a = 8, b = 0.1))

a <- round(coef(mod)["a"], 2)
b <- round(coef(mod)["b"], 4)
Formula <- bquote(y == .(a) * e^{.(b)*x})

ggplot(df, aes(as.numeric(bins) * 10, y)) +
  geom_point(stat = "summary", fun = "mean", col = "orange", 
             shape = 15, size = 5) +
  theme_minimal(base_size = 20) +
  geom_smooth(aes(group = 1), method = nls,
              formula = y ~ a * exp(b * x), 
              method.args = list(start = list(a = 8, b = 0.1)),
              se = FALSE, color = "black", linetype = 2) +
  annotate(geom = "text", label = Formula, x = 20, y = 70, size = 6) +
  scale_x_continuous(breaks = 0:10 * 10, name = "x") +
  theme(panel.grid.major.x = element_blank(),
        panel.grid.minor.x = element_blank(),
        axis.ticks = element_line())

^{Created on 2022-09-28 with reprex v2.0.2}

---

Data used

We have no idea what your data frame or variables are called, but here is an example data frame with 219 values that approximately match your own data. This is the data used in the above example:

set.seed(1)

x <- sample(100, 219, TRUE)
y <- exp(rnorm(219, log(8.49) + 0.018 * x))
bins <- cut(x, breaks = 0:10 * 10)

df <- data.frame(x, y, bins)

Answer 2

Using the data in the picture, there is no discrepancy between Excel and R. First create the data and plot:

X <- 1:10
Y <- c(15, 10, 21, 28, 14, 12, 27, 12, 147, 83)
plot(X, Y, pch=16)

Now we can add a linear regression:

fit.linear <- lm(Y~X)
coef(fit.linear)
# (Intercept)           X 
# -13.800000    9.218182      
p.linear <- predict(fit.linear)
lines(X, p.linear)

Now the log transform equation:

fit.log <- lm(log(Y)~X)
coef(fit.log)
# (Intercept)           X 
#   2.1317772   0.1887922 
exp(coef(fit.log)[1])
    (Intercept) 
       8.429835 
p.log <- exp(predict(fit.log))
lines(X, p.log, col="blue")

Note that the slope value agrees with your Excel example. The intercept also agrees after we change the scale. Finally the nls() equation:

fit.nls <- nls(Y~a*exp(b*X), start=c(a=5, b=0.2))
coef(fit.nls)
#         a         b 
# 3.4643870 0.3430626 
p.nls <- predict(fit.nls)
lines(X, p.nls, col="red")

The nls regression does not match the log-transform regression because the two measure the residuals differently. The log-transform minimizes the log(Y) residuals whereas the nonlinear regression minimizes the Y residuals.