Table of Contents
Multiple linear regression is a statistical technique that can be used in Excel for predictive analysis. It involves analyzing the relationship between multiple independent variables and a single dependent variable. By using this method, one can identify the most significant variables that influence the outcome and make predictions based on their values. The process involves creating a regression model, testing its accuracy, and using it to forecast future outcomes. With the help of Excel’s built-in tools, users can easily perform multiple linear regression and use the results to make informed decisions and predictions. This approach is commonly used in various fields, such as finance, marketing, and economics, to forecast trends and patterns. It is an efficient and reliable method for predictive analysis in Excel, providing valuable insights and aiding in decision-making processes.
Excel: Use Multiple Linear Regression for Predictive Analysis
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:
