Table of Contents
Exponential regression is a statistical method used to model data that follows an exponential trend. This method can be easily performed in Excel using a step-by-step approach. First, organize the data into two columns, with the independent variable (x) in one column and the dependent variable (y) in the other. Next, select the data and go to the “Insert” tab, click on “Recommended Charts” and choose “Scatter with Smooth Lines and Markers.” Then, right-click on one of the data points and select “Add Trendline.” In the “Format Trendline” menu, choose “Exponential” as the type of trendline. Finally, check the boxes for “Display Equation on chart” and “Display R-squared value on chart” to see the equation and the goodness of fit of the model. This step-by-step method allows for easy and accurate performance of exponential regression in Excel.
Exponential Regression in Excel (Step-by-Step)
Exponential regression is a type of regression model that can be used to model the following situations:
1. Exponential growth: Growth begins slowly and then accelerates rapidly without bound.
2. Exponential decay: Decay begins rapidly and then slows down to get closer and closer to zero.
The equation of an exponential regression model takes the following form:
y = abx
where:
- y: The response variable
- x: The predictor variable
- a, b: The regression coefficients that describe the relationship between x and y
The following step-by-step example shows how to perform exponential regression in Excel.
Step 1: Create the Data
First, let’s create a fake dataset that contains 20 :
Step 2: Take the Natural Log of the Response Variable
Next, we need to create a new column that represents the natural log of the response variable y:
Step 3: Fit the Exponential Regression Model
If you don’t see Data Analysis as an option, you need to first .
In the window that pops up, click Regression. In the new window that pops up, fill in the following information:
Once you click OK, the output of the exponential regression model will be shown:
The of the model is 204.006 and the corresponding p-value is extremely small, which indicates that the model as a whole is useful.
Using the coefficients from the output table, we can see that the fitted exponential regression equation is:
ln(y) = 0.9817 + 0.2041(x)
Applying e to both sides, we can rewrite the equation as:
y = 2.6689 * 1.2264x
We can use this equation to predict the response variable, y, based on the value of the predictor variable, x. For example, if x = 14, then we would predict that y would be 46.47:
y = 2.6689 * 1.226414 = 46.47
Bonus: Feel free to use this online to automatically compute the exponential regression equation for a given predictor and response variable.