Hello, this is Dr.. Grande Welcome to my video on conducting a multiple regression
using Microsoft excel
I have fictitious data loaded into an excel sheet, and I have two predictor variables
gPa and practice Exam and one
Outcome variable
comprehensive exam
so if we wanted to try to predict the
comprehensive Exam score
using gPA and practice exam we'd want to perform a multiple regression a
Multiple regression is used when you have two or more
predictor variables and one outcome variable if we only had one predictor variable
For example just the practice exam and we wanted to use the practice exam score to predict the comprehensive exam score
That would be a simple linear regression
however in this case
This fits a multiple regression because we have new predictors that we want to use so let's get started calculating the multiple regression
I'm going to go to the data Ribbon up top here and all the way to the right there is a menu item data
analysis tools
Now if you don't have this menu item available in the Data Ribbon
Just go to file and then options and this excel options dialog will appear
and you want to move down to add-ins and
You can see by default it's set to excel add-ins. Just click go here and
You want to make sure analysis?
Toolpak is checked off
And as you can see in the case of this instance of excel
I do have it checked off, so I'm just going to click cancel
Then I'm going to move up to the data analysis
Menu item and click that and you can see there are several analysis tools available in the data analysis Dialog
regression is already selected here, and I'm going to stay with that and click ok and
Then we have the regression dialog
The first item would be the input y range and that's going to be the dependent variable or the outcome variable
So I'm going to select that and a sexy one all the way through
C-51 so I have 50 values and I have the label
comprehensive Exam all selected in that range
then the input x range
this represents the
Predictor variables and
All the predictor variables are selected for this range. I'm going to start with a 1 and move down
through B 51
so both the gPA and
practice Exam scores all contained within this range
Again, I've included the first row
For input x range and for input y range, so I want to make sure I check labels here
I'm also going to add a confidence level and leave it set the default
95%
For output options. I'm going to select output range and
then
select cell
E4 this will represent the top-left corner of the output that's displayed and
then under residuals, I'm going to select just
The residuals option. There's also standardized residuals available and
Residual plots and line fit plots available under normal probability. I'm not going to check the normal probability plots
At this point. I'm ready to conduct the multiple regression, so I'm going to click ok
And you can see it starts with summary output, and you have regression statistics in this first table
the multiple R is
0.7 R. Square is 0.5
so this tells us that 50% of the variance in the
Outcome variable
Can be attributed to the predictor variables?
so 50% of the variance in the comprehensive exam scores can be attributed to the
grade point average and practice exam scores, then we have adjusted R-Square the standard error and
the number of observations in this Case 50
Looking at the output for the Anova table of the most interest would be the probability value in
This case it is less than point zero five so it's statistically
Significant and then we have down here in the bottom left
the intercept is the y-intercept and the
coefficient here for that is 11, and
then the slope for the gPA and the slope for the practice exam so for the gPa the slope is nine point six and
The practice Exam the slope is 0.3
where the y-intercept the gPA and the practice exam we also have the standard error the t statistic the P-value and
the lower and upper
95% confidence interval
moving down the output
we have the
observation number and the associated
Predicted comprehensive Exam score as well as the residuals
So if we look at observation one for example we can see the predicted comprehensive exam score is 38
0.8 and the residual is negative 8.8
the way this is calculated is we take the
comprehensive exam
score the actual score and
We subtract
the score
predicted using the estimated regression equation in this case thirty eight point eight and that gives us the residual of
Negative eight point eight and it's the same way for all the observations
so if I select all the predicted comprehensive exam scores and
I copy them let me control C and
Move up here next to the comprehensive exam scores. I'm just going to hit control V and paste
the predicted Values and
I'll add an extra Column to make this easier to see moving over here to the right of the multiple R value
Put in equal sign and then co Arielle correlation
You can see the two arguments are array one or a two for array one. I'm going to select the actual scores
then a comma and then
for Ray to
the predicted scores and click enter
And you can see it's the same value as the multiple are here returned in regression statistics
this value is the R value the correlation between the actual comprehensive exam scores and
the
predicted
Comprehensive exam scores if we square this value which would be equal sign
and I'll select this value and the shift 6 which is the carrot and
then 2 so point seven
squared
We have the R square value
So what if we want to use the estimated regression equation to predict?
outcomes on the comprehensive exam
That aren't listed here are combinations that aren't here for example
We have three point three and a gPa three point three and a practice exam of 41
the actual comprehensive Exam score of 62 the predicted fifty five point five
what if we had a gPA of
Three point two and a practice exam of forty two, what would be the predicted score in that instance
well to calculate that we use the
estimated regression equation
Which I'll build over here in
Cell H2 but first up with the values that we want to
predict or three point two for the gPA and
Forty two for the practice exam, so if we had a grade point average
observed of three point two and an observed practice exam of 42
Now we want the predicted value using the estimated regression equation
So this will follow the format y equals Mx plus b
Except we'll have two
predictor variables in this
So it'd be equal sign and then the slope for gPA which is down here under coefficients. It's nine point six
Shift eight for asterisk so the slope for gPA times the observation for gPA
plus the slope for the practice exam
times the practice Exam score 42 and
Then it will add
the Y-intercept so
we have a predicted score of
54 Point nine
So we can use this estimated regression equation for any combination of gPA and practice exam?
So just to show you here if we go back and use the values we had we had three point three and forty one
So these are actual values we have in the data set
and we have the predicted value for that if I plug these in three point three for gPA and
41 for practice exam it gives me the same value here fifty five point five eight and that matches the value
that was returned by Excel I
Hope you found this video on conducting a multiple regression in excel to be useful as always if you have any questions
Or concerns feel free to contact me, and I'll be happy to assist you