GROWTH is the exponential counterpart to the linear regression function TREND described in Method of Least Squares. For Example 1 the output for LOGEST(B6:B16, A6:A16, TRUE, TRUE) is as in Figure 4.įigure 4 – LOGEST output for data in Example 1 LOGEST doesn’t supply any labels and so you will need to enter these manually.Įssentially LOGEST is simply LINEST using the mapping described above for transforming an exponent model into a linear model. 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 is the exponential counterpart to the linear regression function LINEST described in Testing the Slope of the Regression Line. We can get the same result using Excel’s GROWTH function, as described below.Įxcel Functions: Excel supplies two functions for exponential regression, namely GROWTH and LOGEST. Thus if we want the y value corresponding to x = 26, using the above model we get ŷ =14.05∙(1.016) 26 = 21.35. 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).Īs usual we can use the formula y = 14.05∙(1.016) x described above for prediction. 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. The table in Figure 2 shows that the model is a good fit and the relationship between ln y and x is given byĪpplying e to both sides of the equation yields We now use the Regression data analysis tool to model the relationship between ln y and x.įigure 2 – Regression data analysis for x vs.
The table on the right side of Figure 1 shows ln y (the natural log of y) instead of y. Clearly, any such model can be expressed as an exponential regression model of form y = αe βxby setting α = e δ.Įxample 1: Determine whether the data on the left side of Figure 1 fits with an exponential model.įigure 1 – Data for Example 1 and log transform Observation: A model of the form ln y = βx + δ is referred to as a log-level regression model. e β, we note that an increase in x of 1 unit results in y being multiplied by e β.This equation has the form of a linear regression model (where I have added an error term ε): Taking the natural log (see Exponentials and Logs) of both sides of the equation, we have the following equivalent equation: In particular, we consider the following exponential model: Sometimes linear regression can be used with relationships that are not inherently linear, but can be made to be linear after a transformation.