Often you may want to use a multiple linear regression model you’ve built in Excel to predict the response value of a new observation or data point.
Fortunately this is fairly easy to do and the following step-by-step example shows how to do so.
Step 1: Create the Data
First, let’s create a fake dataset to work with in Excel:
Step 2: Fit a Multiple Linear Regression Model
Next, let’s fit a multiple linear regression model using x1 and x2 as predictor variables and y as the response variable.
To do so, we can use the LINEST(y_values, x_values) function as follows:
Once we click enter, the regression coefficients appear:
The fitted multiple linear regression model is:
y = 17.1159 + 1.0183(x1) + 0.3963(x2)
Step 3: Use the Model to Predict a New Value
Now suppose that we’d like to use this regression model to predict the value of a new observation that has the following values for the predictor variables:
- x1: 8
- x2: 10
To do so, we can use the following formula in Excel:
Using these values for the predictor variables, the multiple linear regression model predicts that the value for y will be 29.22561.
Step 4: Use the Model to Predict Several New Values
If we’d like to use the multiple linear regression model to predict the response value for several new observations, we can simply make absolute cell references to the regression coefficients:
Additional Resources
How to Perform Simple Linear Regression in Excel
How to Perform Multiple Linear Regression in Excel
How to Perform Polynomial Regression in Excel
How to Create a Residual Plot in Excel