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 = ax^b

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.1657x^1.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.

Additional Resources

How to Perform Multiple Linear Regression in Excel
How to Perform Exponential Regression in Excel
How to Perform Logarithmic Regression in Excel