# How to get exponential regression equation after performing linear regression on the log transformed equation?

The equation of an exponential function is $y = ae^{bx}$

The data is plotted as shown below:

Transforming this for linear regression: $ln(y) = ln(a) + bx$

This transformation is shown in the plot below:

Then the linear regression equation is: $ln(y) = -369.9778+0.187693x$

How do I transform it back in the form of $y=ae^{bx}$??

My issue is in $ln(a) = -369.9778$. Of how to get the $a$ value.

Even Excel cannot get the equation correctly, but there is a trendline? I don't understand how it is derived. The trendline does not represent the actual scenario based on the data at all:

But it is somewhat accurate when I use the more recent data points:

The data are as below:

Year    Asymptomatic    AIDS    Total
1984    0   2   2
1985    6   4   10
1986    18  11  29
1987    25  13  38
1988    21  11  32
1989    29  10  39
1990    48  18  66
1991    68  17  85
1992    51  21  72
1993    64  38  102
1994    61  57  118
1995    65  51  116
1996    104 50  154
1997    94  23  117
1998    144 45  189
1999    80  78  158
2000    83  40  123
2001    117 57  174
2002    140 44  184
2003    139 54  193
2004    160 39  199
2005    171 39  210
2006    273 36  309
2007    311 31  342
2008    505 23  528
2009    804 31  835
2010    1562    29  1591
2011    2239    110 2349
2012    3151    187 3338
2013    4477    337 4814
2014    5468    543 6011
2015    7328    503 7831
2016    8151    1113    9264

• I don't use Excel routinely and don't know what the added line is in your first plot. It's certainly not an exponential as it is not monotonic. I advise students and colleagues never to give a curve if they can't explain how it was produced. It's probably a polynomial or a spline. – Nick Cox Jan 23 '18 at 9:44
• I just pressed exponential in excel. You're right I just randomly clicked what I felt it to be. I am trying to find out how to properly fit any kind of line I am only familiar with linear regression. – Pherdindy Jan 25 '18 at 9:33
• Thanks for providing an Excel file on another site. I've taken the data and listed them in your question. That's a better way to give examples, cutting out one or two other programs, not using Excel, which many people don't do or don't have, and just giving people something they can copy and paste into their favourite software. – Nick Cox Jan 26 '18 at 11:51

These two regressions will not give parameter values that can be transformed into one another exactly:

$ln(y) ~ vs. ~ A + B ~ x$

$y ~ vs. ~ a ~ exp(b ~ x)$

because they minimize different sums of squares, namely, the following respectively:

$\Sigma_i(ln(y_i) - (A + B ~ x_i))^2$

$\Sigma_i(y_i - a ~ exp(b ~ x_i))^2$

and those are not equivalent minimization problems.

The first regression can be solved for $A$ and $B$ using linear regression.

To solve the second regression, begin by solving the first. Then use $a = exp(A)$ and $b = B$ as starting values to solve the second regression problem using a non-linear regression solver (i.e. in Excel that would be Solver). Also, if the nonlinear regression model is sufficiently far from the linear regression model then it is possible that these starting values will not be adequate in which case you will need to try other starting values.

The data has been added to the question so we can now carry out the suggested action discussed in the paragraph above. Below we show the R code to do this. If you install R on your machine just copy and paste that code into the R console.

First we read the data into DF and then run a linear model, i.e. regression, of log(Total) vs. Year. Note that log in R is log base e. We see that the regression coefficients that are produced are A = -369.977814 and B = 0.187693 for the intercept and slope. Then we extract the slope out into variable b to use as a starting value in the nonlinear regression. We don't need the intercept as a starting value since the nonlinear regression algorithm, plinear, only requires starting values for non-linear parameters. Then we run the nonlinear regression of Total vs. a * exp(b * Year). The coefficients it produces are b = 2.838264e-01 and a = 3.117445e-245. We then plot the result and we see that it seems reasonably close to the data.

In general, when performing nonlinear optimization numerical considerations imply that we want the parameters to be roughly of the same magnitude which is not the case. This suggests re-parameterizing the model to be:

$y ~ vs. ~ exp(a ~ + ~ b ~ x_i)$ [re-parameterized nonlinear model]

and at the end of the code below we do that. We see that now the parameters are a = -562.9959733 and b = 0.2838263 where now a is as defined in the definition of the re-paramaterized nonlinear model. These parameters are much more comparable values so our re-parameterized nonlinear model seems preferable.

The graph would look similar to the one shown for the first nonlinear regression model.

Lines <- "Year    Asymptomatic    AIDS    Total
1984    0   2   2
1985    6   4   10
1986    18  11  29
1987    25  13  38
1988    21  11  32
1989    29  10  39
1990    48  18  66
1991    68  17  85
1992    51  21  72
1993    64  38  102
1994    61  57  118
1995    65  51  116
1996    104 50  154
1997    94  23  117
1998    144 45  189
1999    80  78  158
2000    83  40  123
2001    117 57  174
2002    140 44  184
2003    139 54  193
2004    160 39  199
2005    171 39  210
2006    273 36  309
2007    311 31  342
2008    505 23  528
2009    804 31  835
2010    1562    29  1591
2011    2239    110 2349
2012    3151    187 3338
2013    4477    337 4814
2014    5468    543 6011
2015    7328    503 7831
2016    8151    1113    9264"


Now run this:

# run linear regression model
fit.lm <- lm(log(Total) ~ Year, DF)
coef(fit.lm)
## (Intercept)        Year
## -369.977814    0.187693

b <- coef(fm.lm)[[2]]
b
## [1] 0.187693

# run nonlinear regresion model
fit.nls <- nls(Total ~ exp(b * Year), DF, start = list(b = b), alg = "plinear")
coef(fit.nls)
##             b          .lin
##  2.838264e-01 3.117445e-245

plot(Total ~ Year, DF)
lines(fitted(fit.nls) ~ Year, DF, col = "red")

a <- coef(fit.lm)[[1]]
a
## [1] -369.9778

# run reparameterized nonlinear regression model
fit2.nls <- nls(Total ~ exp(a + b * Year), DF, start = list(a = a, b = b))
coef(fit2.nls)
##            a            b
## -562.9959733    0.2838263


• That's correct. In practice, linearising first is not just easier to implement because it's just a matter of regression thereafter; for data like these it seems reasonable in view of the error structure implied by the graph of log $y$ versus year, notably that scatter appears roughly even on logarithmic scale. We don't have the raw data to check, but in examples like this linearising first seems unlikely to be problematic or inferior. – Nick Cox Jan 24 '18 at 16:03
• Linear regression failed to give the desired answer. That is the main point of the question. – G. Grothendieck Jan 24 '18 at 16:13
• I don't read it the question that way at all. The OP didn't understand all that was being done (a) in general (b) by Excel. (It is disconcerting that the OP has revisited the thread but is not responding to either of the lengthier answers so far.) – Nick Cox Jan 24 '18 at 16:15
• The discussion in the question right at the end and the accompanying graphs point out that what was obtained from the linear regression was not what was wanted. – G. Grothendieck Jan 24 '18 at 16:20
• There's a lot that is confused and even contradictory in the question. If the data were exactly exponential it wouldn't matter how the model was fitted. It's possibly a choice between a middling fit that undershoots at high values; a middling fit that pays more attention to them; and thinking up quite a different model. The OP is the authority on what bothers them but (as said) hasn't yet clarified any important detail. Regardless of that, the answers raise various points that might be of use or interest to others in this territory. – Nick Cox Jan 24 '18 at 16:40

You are using calendar year as $x$, so the inevitable consequence is that $a$ in $y = a \exp(bx)$ is, or was, the value of $y$ in year $x = 0$. Setting aside the pedantic point that there was no year zero, that was the year before $1$ AD (CE), and mental projection of your curve backwards should underline that the fitted value will be (would have been!) very small indeed in year $0$ (but still positive, as the exponential function guarantees that).

You don't give the original data for us to check but I see no reason to doubt what you show. I get $\exp(-369.9778)$ to be $2.09 \times 10^{-161}$, very small indeed. So Excel is correct to the two decimal places it shows. Moreover, you will need to show your result in power notation.

If this were my problem I would fit in terms of say $a \exp[b(x - 2000)]$; then $a$ will have the easier interpretation of $y$ when $x = 2000$ and can be compared with data more easily. (Numerical precision isn't harmed either, and may be helped.)

J.W. Tukey argued that we should fit "centercepts", not intercepts, and this example does underline the point. Authority: Roger Koenker at this page of his.

Plotting on log scale suggests that the exponential is only a rough fit, but that isn't the question.

Related discussion about choice of origin at Does it make sense to use a date variable in a regression?

EDIT Given the data, I read them into Stata.

I fitted $\text{total} = a \exp[b(\text{year} - 2000)]$ by regressing $\ln(\text{total})$ on $\text{year} - 2000$.

That yields a linear equation of $5.40827 + 0.187693 (\text{year} - 2000)$.

The "centercept" for $2000$ thus transforms back to $223$ or so. The data value was $123$. An important detail here is that $0.187693$ matches your Excel result.

I then fitted the same equation directly using non-linear least squares and got centercept of $105.2718$ and coefficient of $0.2838264$. That is very different and not surprisingly so, as the nonlinear least squares doesn't discount the high values as linearising by logarithms does. Your own graph on log scale shows that the highest values in later years are under-predicted by fitting on logarithmic scale. Conversely, nonlinear least squares leans the other way.

Even if an exponential appeared to be a very good fit, I wouldn't try to extrapolate it very far into the future. With these data, where an exponential is a best a rough zeroth approximation, and with a more modest extrapolation than you asked for, the uncertainty is serious:

• Thank you for those references i'll read up on them. I am not so good with the fundamentals regarding the origins of the equations and how they work so I apply the tools incorrectly. Well I guess that's why most people find math hard – Pherdindy Jan 25 '18 at 9:28

To begin with, I would highly suggest you to look for Khan Academy videos about log and exponentials functions.

You should be ok by simply making a = e^(-369.9778).

• I don't quite understand how you got to that value. Isn't log(a) = -369.9778 same as 10^(-369.9778) = a? – Pherdindy Jan 23 '18 at 4:57
• Wait sorry you're right it's e^(-369.9778). Although it does not explain the behavior of the trendlines and the regression equation. Perhaps there's something i'm missing – Pherdindy Jan 23 '18 at 7:12
• When you first wrote the question I thought that was a simple math problem. Now I get your point. – Gilmar Neves Jan 24 '18 at 12:17
• Sorry for the misleading question. When I first made the question I also thought it was my flawed algebra that caused the issue. I'm just not so good with the fundamentals of mathematics I have a lot of holes to fill up. – Pherdindy Jan 25 '18 at 9:25