Table of Contents
Excel can be used for predictive analysis with multiple linear regression by entering data into the worksheet, creating formulas to calculate the linear regression, and viewing the resulting data to make predictions. This process involves entering input variables, computing correlations, creating a linear regression equation, and using the equation to calculate the output variable. Excel can also be used to create graphs to visualize the data and analyze trends.
Often you may want to use a 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:
