(upbeat music)
- Hello, I'm Professor Rick Jerz,
and I welcome you back to this session,
where we are going to learn how to use Excel
to do multiple regression analysis.
These calculations can be very difficult to do by hand.
Most instructors will tell you that you must use
statistical software for multiple linear regression.
Other instructors who do use Excel,
resort to using Excel's Data Analysis ToolPak,
which I have continued to mention I don't like,
because it only provides a snapshot of the results
and it cannot be easily integrated
into bigger business models.
I have invested a lot of time into developing my own
multiple regression model
and I think that you are going to enjoy using it.
I should mention that this model uses some Excel techniques
that many students have never seen.
So be patient as you learn how to use this model.
Take your time.
You will see that my Excel model does all the calculations
and then allows you to spend time
on interpreting the results.
Let's get started.
Multiple regression analysis is one of the most
mathematically challenging statistical techniques.
Consider all of these items that we want to address.
We want to produce a scatter diagram or some graphic.
We want to calculate the correlation coefficient,
coefficient of determination,
and the adjusted coefficient of determination.
We want to perform a global test
of all independent variables.
We want to determine the regressed equation,
that includes the intercept and coefficients
of the regressed line.
We want to determine if any of the predictors
should be eliminated.
We want to determine if multicollinearity exists
and if so, which predictor should be eliminated?
And what is most important
is that we want to use the regressed line to predict values.
When we make these predictions,
we want to calculate the confidence interval
and prediction intervals for the predicted value.
We'd like to see if we could do the test for interaction,
look at stepwise regression and perform some tests
to see if our assumptions for multiple regression
might be violated.
Wow, now you can appreciate why multiple regression analysis
is so complex.
Many instructors teaching multiple regression analysis
will say that you must use statistical software,
such as Minitab, SAS, or SPSS for this analysis.
Those instructors who do use Excel,
almost always use Excel's Data Analysis ToolPak.
This is because Excel's simple regression functions,
such as those for correlation, slope, and intercept
do not work with multiple independent variables
and there seems to be no easy way around this problem,
or any Excel technique that can be applied.
For many years I used the Data Analysis ToolPak.
However, I finally figured out how to use Excel functions
and techniques and came up with my own model,
a much better method for doing multiple regression analysis.
What I will do in this session
is to give you a broad overview of what I'm giving you.
Second, I will show you the Data Analysis ToolPak technique
that many instructors use.
I will also briefly show you the MegaStat add-in.
And finally, I will show you how to use my model
to solve business problems.
Let's use this example
that we looked at in my previous session.
Salsberry Realty sells homes along the East Coast
of the United States.
One of the questions most frequently asked
by prospective buyers is,
if we purchase this home, how much can we expect to pay
to heat it during the winter?
The research department at Salsberry has been asked
to develop some guidelines regarding heating costs
for single-family homes.
Three variables are thought to relate to the heating costs.
The mean daily outside temperature,
the number of inches of insulation in the attic,
and the age of the furnace.
To investigate, Salsberry's research department
selected a random sample of 20 recently sold homes.
It determined the cost
to heat each home last January as well.
The data for this problem is what you see in my model
when you open it.
This model is more complex,
but follows my model in conventions.
On the top left is the data entry area
and below this is my model.
To the right, in this area,
are some intermediate calculations
with orange cells reflecting some interesting
intermediate results.
To the right of these I show you the results
of Excel's Data Analysis ToolPak.
Below this I show results using MegaStat.
Okay, first the Data Analysis ToolPak approach.
I showed you this approach
in my simple regression analysis session using Excel
and the technique here is essentially the same
for multiple regression with a minor adjustment.
I encourage you to follow along with me
and try this technique on your own.
First, it is best to have your data organized
and especially to have your
independent variables grouped together,
as I do in my model.
Open the Data Analysis ToolPak and pick regression,
which brings up this dialog box.
Click in the Y data text box
and then swipe through your Y data of interest,
including the heading.
Now for the X data, this is where our technique
is slightly different, which may not be obvious.
You need to swipe through
all your independent variables data,
meaning multiple columns, include the headings.
Yep, there are a few options to consider.
Yes, I included headings when I swiped the data,
so click on labels.
And sure, give me the 95% confidence intervals.
Some of these options would make some sense
in an advanced course in regression analysis.
You have a choice of putting the results in a new worksheet
or on this worksheet.
I am going to put the output on this worksheet,
right on top of where my model shows it.
So in this text box I click on the cell
that currently contains summary output.
When I click OK, the results appear
and it looks like I am done.
The challenge for most students is reading
and interpreting this output.
The Multiple R is our correlation coefficient.
R Square is our coefficient of determination.
And Excel provides the adjusted coefficient of determination.
These statistics seem to imply a relationship.
The regression significance F
is the P-value for the global test
that all coefficients are significant as a group.
This happens to be a very small P-value,
much smaller than a 1% level of significance,
but we need to interpret this ourselves as
reject a null, there is a relationship.
Excel doesn't help at all with this interpretation.
The coefficients are the intercept and slope
of each variable and these are used to create
our regressed equation, as shown here.
These P-values are the P-values
for each individual coefficient.
If any of these P-values are greater
than our level of significance,
we conclude that the coefficient is not significant
and it can be removed from our regressed equation.
However, it is still up to us
to pick a level of significance,
compare it to these P-values
and formulate our own conclusion.
Excel doesn't help with a conclusion.
Well, this is the Data Analysis ToolPak.
Of the 13 items that we wanted to explore,
we have addressed these six.
Why doesn't the Data Analysis ToolPak do more?
Well, when this tool was provided by Excel
in the early 1990s, this was considered to be quite good.
Microsoft has not done anything to improve this tool
and just as some instructors continue to use tables
in the back of the book
that were created in the early 1900s,
many instructors still go gaga
over this Data Analysis ToolPak.
The MegaStat tool represents a much newer approach,
but this is now a paid for add-in offered by McGraw-Hill.
I show its output as an example
of what a more modern add-in can do.
Its output is very similar,
but you should notice that MegaStat does provide prediction,
including the confidence and prediction intervals.
Another things that MegaStat calculates
is a VIF statistic, which can be used to determine
if we have multicollinearity.
So MegaStat does seem better,
but a lot is still up to us to determine.
The good news is that these add-ins do work.
You could compare the output of these add-ins
with what our author's answers are
and you will see that they match.
These add-ins do have some serious drawbacks.
When you change any data values like this,
the results do not update.
You must rerun the add-in to update the results.
Spreadsheets were originally developed to help us
do quick what if analysis.
And having to rerun a utility is a very serious drawback.
Perhaps buried somewhere in the add-in results
are statistics that we can use
to address our remaining items.
It just seems like a lot of work
to have to manipulate values
to get what you want to determine.
This is like having to calculate Z values
to read a table of probabilities
instead of simply directly calculating the probability.
The more that we have to manipulate values,
the better it looks to try to build our own model.
And if we try to start building our own labels
or models with the results of these add-ins,
we have to be careful to always put the add-in output
in the exact same cells,
so that our references to output cells remains correct.
Let me show you my model,
beginning with the same data that we have been using.
You begin by entering your data
into this upper left side area in the green cells.
I will say a few more things about this data entry area
a little bit later.
Below the raw data,
my model calculates the coefficient of correlation,
the coefficient of determination,
and the adjusted coefficient of determination.
Below this is the global test.
You enter your desired level of significance
and my model calculates the P-value,
compares it to the level of significance
and interprets the results.
For this problem the model shows that we reject a null.
There is a relationship.
To the right I provide a graph of the F statistic
that is used for this test.
When the vertical black line is outside the red area,
we reject a null.
In step three my model calculates
the intercepting coefficients
for all the predictor variables.
We must test each independent variable
to see if they should be kept in our model.
In this area my model calculates P-values for each variable,
compares it to our level of significance
and tells you with a yes or no
if we should keep this predictor.
My model also tells you if multicollinearity exists
by calculating VIF factors.
Recall that multicollinearity means
that two of the predictor variables
are related to each other.
This is bad.
When you see a yes for multicollinearity,
you should consider removing one of the variables.
In step four I show you the regressed equation
in regular equation form.
In step five we can use our model for prediction.
We enter some values of interest
and the model calculates Y-hat,
which is our predicted value.
And my model also calculates the prediction
and confidence intervals.
For this problem the heating cost is $276 and 57 cents.
matching what our author got.
Well, this is pretty nice.
Just enter your data and you are done.
This is a live model that does not need to be rerun
and rerun and rerun.
Let's learn how to use this model
to do some really cool things.
When we look at the test for individual variables,
we don't have multicollinearity,
but our model says not to keep the age variable,
since its P-value is higher than our level of significance.
If we were using the Data Analysis ToolPak approach,
we would have to manually remove this variable,
perhaps rearrange our remaining variables
so that they can be swiped through
and rerun the Data Analysis Tool.
I hope that you are sitting down,
as I am going to show you what to do in my model.
To get rid of a variable,
all you have to do in my model
is to go to the top of your data
and flip this YN switch to N.
That's it, the variable has been removed
and you are done with all the recalculations.
Whoa, that was easy.
This ability to easily switch independent variables
on and off, you will see is an extremely powerful feature
that I have built into my model.
Our new prediction becomes $262.
I designed my model to handle
up to six independent variables.
I stopped at six, because I haven't seen any textbooks
use more that six independent variables in a question.
Six is a lot.
However, if you find yourself
needing more than six variables,
you would need to extend my model.
Remember that my Excel models are completely open
and I am not doing any VBA or programming,
just using Excel functions.
Let's go on.
Let's extend our analysis by considering
how me might add a qualitative independent variable,
such as whether or not the home has a garage.
Here is the data from our author.
If the home does have a garage,
we'll enter a one, and if it does not,
we will enter a zero.
Recall that this type of variable
is called a Dummy variable.
I entered this data into this column
and turned this column on, or green,
by changing the no to a yes.
Once we do this, we are done.
Remember that Dummy variables are entered as zero or one.
If I happened to enter the word, yes,
my model reports a bunch of errors.
Whenever you see these errors occur,
something about your data entry is not correct.
What happened?
You should note that all we had to do is enter data
for another independent variable.
My model recalculates everything.
No need to remember to rerun an add-in.
Our global test shows that we should still reject a null,
meaning that there is a relationship.
Right below this we can also see
whether or not the garage variable should
or should not be kept as a predictor.
In this case it should be kept.
We are ready to use our model for prediction.
What would we predict as heating cost
if we have a temperature of 20 degrees,
three inches of insulation, and the house has a garage?
My model shows $357, which is exactly what our author gets.
If we want, we could easily turn age back on
to see if the addition of a garage variable
has helped the age variable.
When I do this, my model still suggests
that age should be removed.
You can't do this kind of quick analysis
with the Data Analysis ToolPak.
There is a suggested check, called interaction,
that should be done.
What you do is to multiply
two independent variables together
and then look at the P-value
for this new interaction variable.
If the P-value is greater than our level of significance,
there is not interaction
and our independent variables are fine in our model.
If the P-value is smaller than our level of significance,
this suggests that we do have interaction
and one of the two variables should be removed.
We can easily do this test using my model.
To follow along with our textbook example,
first I will turn off the garage variable,
then I add a new column, called T times I.
I enter the equation in this first cell.
My Excel modeling technique automatically duplicates
this down and we are ready to interpret the results.
When we look at this interaction variable's P-value,
it is greater than our 5% level of significance,
so we do not reject the null,
meaning we do not have interaction.
Wow, that was easy.
And for us it would be equally easy
to test this up to five independent variables,
reserving our sixth column for this interaction test.
We don't need to delete this temporary interaction data,
we can simply turn this interaction variable off
in our model.
Stepwise regression is a step-by-step method
to determine a regression equation
that begins with a single independent variable
and adds or deletes independent variables one by one.
Only independent variables with significant
coefficients of determinations are included
in the regression equation.
We can easily do this stepwise regression
with my model.
I turn off all variables, except temperature.
I note that its coefficient of determination
is point six six,
then I turn off temperature and turn on insolation.
Its coefficient of determination is point zero seven.
Right now temperature is better,
because its coefficient of determination is better.
I then test just the age variable.
Its coefficient of determination is point two nine.
Since temperature is best, I turn this on and keep it on,
then I turn on insolation and note
the coefficient of determination as point seven eight,
then I turn off insolation and turn on age.
The coefficient of determination is point six nine,
which is smaller than the point seven eight,
so insolation is better and it should stay on.
Now I turn on age, but age shows a high P-value,
so it should be removed.
I am done.
I think that you get the idea
and I think that you see how useful my model becomes
for this stepwise regression.
Some statisticians will debate the method
of how to add and remove predictor variables
to your regression analysis.
With my model it doesn't matter.
Even if we included the garage variable,
my model makes this easy.
It would be much more difficult
to do this stepwise regression
using the Data Analysis ToolPak add-in, or other add-ins.
Let's do another problem.
Problem nine from our textbook.
This problem will allow me to describe
a special data entry requirement of my model.
This special requirement is that you must have
exactly the same number of green data entry rows
to match your dataset.
If you have 52 sets of data,
you must have exactly 52 rows in my model.
I will illustrate this requirement with our problem.
The manager of High Point Sofa and Chair,
a large furniture manufacturer located in North Carolina,
is studying the job performance ratings
of a sample of 15 electrical repairmen
employed by the company.
An aptitude test is required
by the human resources department
to become an electrical repairman.
The manager was able to get the score for each repairman
in the sample.
In addition, he determined which of the repairmen
were union members, code equals one,
and which were not, code equals zero.
The sample information is reported below.
Use a statistical software package to develop
a multiple regression equation
using the job performance scores,
the dependent variable, and the aptitude test score,
and union membership as independent variables.
I have provided this Excel data file,
which makes it easy to work with the data.
Before you do anything, you have to prepare my model
by making sure that you have the correct number of rows.
So I first see how many datasets I have for this problem,
which we are told is 15.
I can also quickly verify this number
by swiping through the data and noting the count
in Excel's lower status bar.
In this case we need to remove five rows.
I swipe any five rows in my model,
making sure to do this on the side letters
so that I get the complete rows selected,
then right-click and pick delete.
Of course if we needed to add rows,
I would pick insert instead of delete.
Now that's not all that hard, is it?
In this model I use an Excel technique
that automatically numbers the rows.
So I see 15, which matches my datasets.
If you find yourself with too many or too few rows,
just repeat this step.
Now I swipe through my data file, including the headings
and pick copy, then I go to my model
and in the correct cell, do a paste special, values.
Make sure to turn on or off any columns as required
so that your pasted data become green cells.
If you get error messages showing in the residual area,
something is wrong.
For example, if your data is text and not values,
you might need to convert your data to numeric values.
Now that we have our data into my model,
we are done with all the calculations.
To answer question A, our regressed equation is
performance equals 29.276
plus 5.222 times the aptitude score plus 22.135
times the value representing whether or not
they are a union member.
Question B is a bit open-ended.
The global test says that there is a relationship
at the 5% level of significance.
Note that if we were using a 1% level of significance,
we would not reject the null.
Looking at the adjusted coefficient of correlation,
about 0,45, 46% of the variation in performance
is explained by the independent variables.
We do not have multicollinearity
and both independent variables should be kept in this model.
If we want to, we can quickly check for interaction.
This big P-value suggests no interaction.
Looking at question C, we have already answered this.
Yes, keep union membership at the 5% level of significance.
Our P-value is 2.8%.
Again, if we were using a 1% level of significance,
we would not include union membership.
And for question D, we have already addressed this
in our reply to question B.
Why does the author say to use
a statistical software package?
And had we only been using Excel's Data Analysis ToolPak,
this problem would have taken us longer.
My model makes solving these problems quite easy.
Why didn't the author ask us to use a regressed equation
to predict and include confidence and prediction intervals?
Does the author think these calculations
are too hard for us to do?
Well, did you ever believe that you would want to do more
multiple regression problems?
Let me summarize my modeling technique
by going back to the 13 items that we wanted to address.
Of these 13 items, we have not created a scatter diagram.
Creating a scatter diagram
when you have more than three
independent variables is difficult.
One could always use my simple regression model
to create a scatter diagram for each independent variable
one by one.
I have also not addressed the assumptions,
but I will do this in my reinforcement session.
Well, that's about it.
My model can be easily used
for all these other desired items.
So I believe that my modeling technique
is rather comprehensive.
Most statisticians who might want more,
relative to this multiple regression analysis,
could extend my model by either using my model results,
or my model's intermediate calculated values,
meaning those values in orange cells.
I hope you can appreciate how much work I put
into developing this multiple regression model of mine.
My goal was to make multiple regression analysis
easy for students to do.
In my reinforcement video
I'm going to share with you how I built this model
and I will address these multiple regression assumptions.
I do suggest that you practice using this model
with these examples that I have provided
so that you can repeat what I have done.
I also suggest that you do some more problems on your own
and maybe try Excel's Data Analysis ToolPak
side by side with my model.
(upbeat music)
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions or need my help,
please let me know.