Home Â» How to Perform Power Regression in Excel (Step-by-Step)

# How to Perform Power Regression in Excel (Step-by-Step)

Power regression is a type of non-linear regression that takes on the following form:

y = axb

where:

• y: The response variable
• x: The predictor variable
• a, b: The regression coefficients that describe the relationship betweenÂ x and y

This type of regression is used to model situations where the response variable is equal to the predictor variable raised to a power.

The following step-by-step example shows how to perform power regression for a given dataset in Excel.

### Step 1: Create the Data

First, letâ€™s create some fake data for two variables: x and y.

### Step 2: Transform the Data

Next, letâ€™s take the natural log of both x and y by using the =LN(number) formula:

### Step 3: Fit the Power Regression Model

Next, weâ€™ll fit a regression model to the transformed data.

To do so, click the Data tab along the top ribbon. Then click the Data Analysis option within the Analyze section.

If you donâ€™t see this option available, you need to first load the Analysis ToolPak.

In the dropdown window that appears, click Regression and then clickÂ OK. Then fill in the following information:

Once you clickÂ OK, the regression output will automatically appear:

The overall F-value of the model is 254.2367 and the corresponding p-value is extremely small (4.61887e-12), which indicates that the model as a whole is useful.

Using the coefficients from the output table, we can see that the fitted power regression equation is:

ln(y) = 0.15333 + 1.43439ln(x)

ApplyingÂ e to both sides, we can rewrite the equation as:

• y = e 0.15333 + 1.43439ln(x)
• y = 1.1657x1.43439

We can use this equation to predict the response variable,Â y, based on the value of the predictor variable,Â x.

For example, if x = 12, then we would predict that y would beÂ 41.167:

y = 1.1657(12)1.43439 = 41.167

Bonus: Feel free to use this online Power Regression Calculator to automatically compute the power regression equation for a given predictor and response variable.