Another non-linear regression model is the power regression model, which is based on the following equation:
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 ε):
Observation: A model of the form ln y = β ln x + δ is referred to as a log-log regression model. Since if this equation holds, we have
it follows that any such model can be expressed as a power regression model of form y = αxβ by setting α = eδ.
Example 1: Determine whether the data on the left side of Figure 1 is a good fit for a power model.
Figure 1 – Data for Example 1 and log-log transformation
The table on the right side of Figure 1 shows y transformed into ln y and x transformed into ln x. We now use the Regression data analysis tool to model the relationship between ln y and ln x.
Figure 2 – Log-log regression model for Example 1
Figure 2 shows that the model is a good fit and the relationship between ln x and ln y 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 Power Trendline option (after choosing More Trendline Options). We can also create a chart showing the relationship between ln 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 described above for prediction. For example, if we want the y value corresponding to x = 26, using the above model we get
Excel doesn’t provide functions like TREND/GROWTH (nor LINEST/LOGEST) for power/log-log regression, but we can use the TREND formula as follows:
to get the same result.
Observation: Thus the equivalent of the array formula GROWTH(R1, R2, R3) for log-log regression is =EXP(TREND(LN(R1), LN(R2), LN(R3))).
Observation: In the case where there is one independent variable x, there are four ways of making log transformations, namely
level-level regression: y = βx + α
log-level regression: ln y = βx + α
level-log regression: y = β ln x + α
log-log regression: ln y = β ln x + α
We dealt with the first of these in ordinary linear regression (no log transformation). The second is described in Exponential Regression and the fourth is power regression as described on this webpage. We haven’t studied the level-log regression, but it too can be analyzed using techniques similar to those described here.