Multiple Linear Regression Using Excel

Calculate the coefficients involved in the mutliple linear regression model using Excel.
Note that you need to first load the analysis toolPack in Excel if it is not loaded yet.


Step 1 - Organize the data sets in columns and use labels in the first row such as \( X_1 \), \( X_2 \), \( X_3\) and \( Y \), as shown below, to make it easier to interpret the results of the computations.
Note that in this example, \( X_1 \), \( X_2 \), \( X_3\) are the independent variables and \( Y \) is the dependent variable.

Organize Data Into Columns
Step 2 - Press on the "Data" tab and click on "Data Analysis".

Press Data Tab and Click on Data Analysis
Step 3 - Select "regression" and press "OK".

Select Regression and Press OK
Step 4 - Clear "Input Y Range" , "Input X Range" and "Output Range".

Clear Input and Output Ranges
Step 5 - Click inside "Input Y Range" and use the mousse to select the column containing the Y values including the label \( Y \).

Select Input Y Range Data
Step 6 - Click inside "Input X Range" and use mousse to select all three columns containing the X values including the labels \( X_1 \), \( X_2 \), \( X_3\).

Select Input X Range Data
Step 7 - Check "Labels".

Check Labels
Step 8 - (1) Check "Output Range" , (2) click inside the small window, (3) click a cell, and (4) click "OK".

Output Range
Step 9 - Read and interpret results from table.
Several different quantities are displayed on a table. At this point we are interetsed on the coefficients \( \hat \beta_0 \) denoted as the intercept , \( \hat \beta_1 \) the coefficient of \( X_1 \), \( \hat \beta_2 \) the coefficient of \( X_2 \) and \( \hat \beta_3\) the coefficient of \( X_3 \).
The multiple linear regression model is given by: \[ \hat y = \hat \beta_1 X_1 + \hat \beta_2 X_2 + \hat \beta_3 X_3 + \hat \beta_0 \] where
\( \hat \beta_0 = 112.6721 \)
\( \hat \beta_1 = -5.55002 \)
\( \hat \beta_2 = -0.98833 \)
\( \hat \beta_3 = 11.97741 \)
Interpret Results


More References and links

  1. Data Analysis in Excel
  2. Load The Analysis ToolPack in Excel
  3. Correlation Coefficient Examples with Solutions
  4. Correlation Problems with Real Life Data