Exponential Regression using a Linear Model

Basic Concepts

Sometimes linear regression can be used with relationships that are not inherently linear but can be made to be linear after a transformation. In particular, we consider the following exponential model:

Taking the natural log (see Exponentials and Logs) of both sides of the equation, we have the following equivalent equation:

This equation has the form of a linear regression model (where I have added an error term ε):

Observations

Since αe^β(x+1) = αe^βx · e^β, we note that an increase in x of 1 unit results in y being multiplied by e^β.

A model of the form ln y = βx + δ is referred to as a log-level regression model. Clearly, any such model can be expressed as an exponential regression model of form y = αe^βx by setting α = e^δ.

Example

Example 1: Determine whether the data on the left side of Figure 1 fits with an exponential model.

Figure 1 – Data for Example 1 and log transform

The table on the right side of Figure 1 shows ln y (the natural log of y) instead of y. We now use the Regression data analysis tool to model the relationship between ln y and x.

Figure 2 – Regression data analysis for x vs. ln y from Example 1

The table in Figure 2 shows that the model is a good fit and the relationship between ln y and x is given by

Applying e to both sides of the equation yields

We can also see the relationship between x and y by creating a scatter chart for the original data and choosing Layout > Analysis|Trendline in Excel and then selecting the Exponential Trendline option. We can also create a chart showing the relationship between x and ln y and use Linear Trendline to show the linear regression line (see Figure 3).

Figure 3 – Trend lines for Example 1

As usual, we can use the formula y = 14.05∙(1.016)^x described above for prediction. Thus if we want the y value corresponding to x = 26, using the above model we get ŷ  =14.05∙(1.016)²⁶ = 21.35.

We can get the same result using Excel’s GROWTH function, as described below.

Worksheet Functions

Excel Functions: Excel supplies two functions for exponential regression, namely GROWTH and LOGEST.

LOGEST Function

This worksheet function is the exponential counterpart to the linear regression function LINEST described in Testing the Slope of the Regression Line. Once again you need to highlight a 5 × 2 area and enter the array function =LOGEST(R1, R2, TRUE, TRUE), where R1 = the array of observed values for y (not ln y) and R2 is the array of observed values for x, and then press Ctrl-Shft-Enter. LOGEST doesn’t supply any labels, and so you will need to enter these manually.

Essentially LOGEST is simply LINEST using the mapping described above for transforming an exponent model into a linear model. For Example 1 the output for LOGEST(B6:B16, A6:A16, TRUE, TRUE) is as in Figure 4.

Figure 4 – LOGEST output for data in Example 1

GROWTH Function

This worksheet function is the exponential counterpart to the linear regression function TREND described in Method of Least Squares. For R1 = the array containing the y values of the observed data and R2 = the array containing the x values of the observed data, GROWTH(R1, R2, x) = EXP(a) * EXP(b)^x where EXP(a) and EXP(b) are as defined from the LOGEST output described above (or alternatively from the Regression data analysis). E.g., based on the data from Example 1, we have:

GROWTH(B6:B16, A6:A16, 26) = 21.35

which is the same result we obtained earlier using the Regression data analysis tool.

GROWTH can also be used to predict more than one value. In this case, GROWTH(R1, R2, R3) is an array function where R1 and R2 are as described above and R3 is an array of x values. The function returns an array of predicted y values for the x values in R3 based on the model determined by the values in R1 and R2.

Observation: Note that GROWTH(R1, R2, R3) = EXP(TREND(R1, R2, LN(R3)))

References

Microsoft Support (2012) LOGEST function
https://support.microsoft.com/en-au/office/logest-function-f27462d8-3657-4030-866b-a272c1d18b4b#:~:text=In%20regression%20analysis%2C%20the%20LOGEST,entered%20as%20an%20array%20formula.

Microsoft Support (2012) GROWTH function
https://support.microsoft.com/en-gb/office/growth-function-541a91dc-3d5e-437d-b156-21324e68b80d