Search code examples
rlmexponentialnls

R equivalent of excel exponential trendline (including equation)

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.

enter image description here

Solution

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