The second is described in Exponential Regression and the fourth is power regression as described on this webpage. We dealt with the first of these in ordinary linear regression (no log transformation). In the case where there is one independent variable x, there are four ways of making a log transformation, namely Thus the equivalent of the array formula GROWTH(R1, R2, R3) for log-log regression is =EXP(TREND(LN(R1), LN(R2), LN(R3))). For example, if we want the y value corresponding to x = 26, using the above model we getĮxcel doesn’t provide functions like TREND/GROWTH (nor LINEST/LOGEST) for power/log-log regression, but we can use the TREND formula as follows:

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).Īs usual, we can use the formula 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 Power Trendline option (after choosing More Trendline Options). We now use the Regression data analysis tool to model the relationship between ln y and ln x.įigure 2 – Log-log regression model for Example 1įigure 2 shows that the model is a good fit and the relationship between ln x and ln y is given byĪpplying e to both sides of the equation yields

The table on the right side of Figure 1 shows y transformed into ln y and x transformed into ln x. It follows that any such model can be expressed as a power regression model of form y = αx βby setting α = e δ.Įxample 1: Determine whether the data on the left side of Figure 1 is a good fit for a power model.įigure 1 – Data for Example 1 and log-log transformation This equation has the form of a linear regression model (where I have added an error term ε):Ī model of the form ln y = β ln x + δ is referred to as a log-log regression model. Taking the natural log (see Exponentials and Logs) of both sides of the equation, we have the following equivalent equation: Another non-linear regression model is the power regression model, which is based on the following equation:

Trendline equation excel