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 and 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:

=EXP(TREND(LN(B6:B16),LN(A6:A16),LN(26)))

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.

Hello Charles,

Thank you for your insights here.I happen to have a question on the power law; however, it seems to combine a number of statistical aspects.

I am looking to fit a line on the linear part of a log-log plot of a power law. Unfortunately with excel, the power trendline fitted automatically takes into account the entire data set. I need to ignore the outlying first part. I have tried to look for methods to solve this and somewhere I found a suggestion that to bin my data. Other suggestions were to use maximum likelihood estimation or weighted least squares.

I did try to use Linear regression but it did not help. The biggest problem is where to choose to begin the regression from; what point in the data set?

Do you have any tricks up your sleeve as regards this?

Musa,

Can’t you just restrict your analysis to those points that are on the subset of the curve that you are interested in?

Charles

the power of developed equation is attained when the predicted value are within the range of input data

hi Charles,

Firstly, sorry if my question is not related here. I know one of my IV have no relationship with the DV(corr= 0.07). But I still wanted to put in the equations even though the result of the parameter variable is not significant after regression. The adjusted R square is 0.76 and the whole equation can be trusted. (<0.05). What can I do with the no correlation variables that I want it? Can I transform the particular data? Thank you in advance.

Yuna,

If you want to retain some independent variable in the model for theoretical reasons (based on your domain knowledge), then just keep it in the model and don-t worry about the fact that it is not significant. If you instead want to use some transformation that yields a significant regression coefficient, then make that transformation (I would do this based on some theoretical, not statistical, basis).

Charles

Pheww thank you Charles. However, can we make transformation to the variables if its already no relationship with the DV? Ive tried some method on transformation but only slight changes. Still far from significant. Thank you again Charles.

Yuna,

Here is a an example where a transformation can make a big difference

x y

1 -0.002004008

2 0.001908397

3 1.70797E-05

4 9.54129E-07

5 1.02405E-07

6 1.65383E-08

7 3.54014E-09

The correlation coefficient is .14876. If you use the transformation y –> (1/y + 500)^.1 then the correlation coefficient will be 1.

I don’t know how useful this is, but at least it shows that a transformation can make a difference in the correlation coefficient.

Charles

thank you so much Charles. Wish you are given longevity of health so you can always be here helping us.

In model: ln y = β ln x + α

β is short term elasticity.

How to calculate long term elasticity? I think it is connected with:

ln y = β ln x + β1 ln yt-1 + α

Matija,

I think you are asking me a question about economics, not statistics. It looks like you are looking for a time series model of long term elasticity. The website explains how to model time series and create forecasts based on the resulting model. This part of the website is under construction, but there is already a lot of useful information in the site about this topic.

Charles

Pingback: How many tickets will be sold before Wednesday? …and other burning Powerball questions | The Final Wager

Hi,

Near the end of the page, you explained how to get an X, if you know the Y. You did it like this: =EXP(TREND(LN(B6:B16),LN(A6:A16),LN(26))).

Is there any way to find Y, when you know the X?

Thanks in advance,

Kevin

Kevin,

It depends on which power model you are referring to. For the log-log model, you simply perform regression of log x on log y, and so can you the same Excel formula, exchanging the roles of x and y.

Charles

Are you talking about this?

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

Power Trendline

Equation: y=c*x^b

c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))

b: =INDEX(LINEST(LN(y),LN(x),,),1)

x and y are the data set that you have to generate this formula.

Is it possible to transform a model that has both a power and a linear variable?

My formula is y=a*x^b+z*d, where a*x^b covers what can be considered fixed tasks with improvement over months of time (x) and z*d covers variable support tasks that will scale with the effort z in hours of the people being supported.

I’ve currently set it up using an addition column for y-hat and used solver to estimate a, b, and d by maximizing the r2. I’m rather pleased with the result, however I’m wondering if there’s a way to transform this for use with linest. Also, being that I’m not nor should I ever be considered a mathematician I wonder if there’s anything I’m missing that would cause my results to be in error.

Please note that I also performed multivariable linear and transformed power regressions using linest. The results between my model and the two variable linear model are somewhat close, I just have a conceptual issue with the linear model since it estimates the fixed tasks as being negative if you go far enough in the future. I appreciate any help you can provide.

Thanks,

Jason,

Sorry, but I don’t know any way to use a transformation so that linest can be used.

Charles

Jason,

I may have that same question too, i.e. one predictor variable (x) that has a power relationship with response y, and another predictor (d) that has a linear relationship with y, which I want both together run in same (linear) model.

Probably you can simply run such (linear) model by linearizing (log-transform) all but the d predictor variable:

ln y = ln a + b * ln x + z*d

But, please, anybody confirm that, or correct me if I am wrong.

Jason,

This model looks correct to me. You can address it as a linear model or a non-linear model (e.g. using Solver).

Charles

Hi Charles,

I just wanted some clarification on why do we use a linear trend-line for the log-log transformed data? If we used a power trend-line, would it be less accurate?

Thanks for your help,

Anna

Anna,

The idea of the log-log transformation is to get a linear relationship. For this reason after the transformation you check for a linear trend. For the data before making the transformation, you won’t see a linear relationship and so your would not use a linear trendline.

Charles

Hi,

Thanks for your answer. But I think the same error has also been done in the following page. I’m referring to figure 2.

http://www.real-statistics.com/regression/exponential-regression/

Thanks again Hamed,

I have corrected the error that you detected. Thanks for catching these errors.

Charles

Hi,

In figure 2 the coefficient for Ln x is .23 and the coefficient for intercept is 2.81 but in your equation it has been shown otherwise (Ln y = .23+2.81 Ln x).

What is going on?

Hi Hamed,

Thanks for finding the error. My dyslexia has caught up with me again. I inadvertently exchanged the two parameters. I have now corrected the webpage. Thanks again for catching the error.

Charles