(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 correlation
and linear regression analysis.
I think that you will see that my model makes
the calculations pretty easy.
When the calculations are easy, we can relax and spend
our effort on interpreting the results.
Let's get started.
From my lecture about correlation and regression analysis
you might be wondering how is Dr. Jerz going to help me
solve these kinds of correlation and linear
regression problems that seem to have a complex set
of required equations?
Let me tell you that we are into some complexity
that has baffled some folks but there are still
basically only two approaches with Excel.
We can use an Excel add-in or we can use functions
to do it ourselves.
If you don't know Excel well or you don't want
to dabble with Excel functions, the add-in approach
will look appealing.
Here is what we need to calculate and produce.
We want to draw a scatter diagram.
We want to be able to calculate the coefficient
of correlation and the coefficient of determination.
We want to test the correlation coefficient to see
if the relationship might be from chance.
We want to produce a linear regressed equation
containing a slope and intercept.
We want to use this equation for prediction.
And we want to calculate both a confidence
and a prediction interval for our prediction.
Here's my Excel model for correlation
and regression analysis.
When you open it, it contains the data from our example
of copiers sold.
Notice in the green cells I have columns labeled
Predictors X and Predicted Y.
X is the independent variable and Y
is the dependent variable.
As in all my Excel models, you are expected to enter
data into the green cells and results are shown
in red cells.
To the right on this model, I give you eight common steps.
We will go through these steps.
The first step is to plot the data.
As always, we like to look at the data before trying
to analyze it.
I have provided you a plot for this data but let me show you
how I created this.
I swiped through the X and Y values.
Then I go to Insert and right here I want to insert
a scatter diagram.
I immediately notice that my X and Y data are reversed.
I can fix this by right-clicking on the graph
and choosing to select the data and then edit
the X and Y value references.
This looks correct.
I can also add a chart title and the X and Y axis labels.
For the X and Y axis titles, I want these
to be the column labels so I will reference these cells.
However, for the chart title, I wanted this
to be a combination of the two column headings
and the only way that I could figure out how to do this
was to create the combination in a scratch cell
and then reference it.
I can continue formatting this chart until it looks
just the way I want it to look.
My chart that I give you shows a straight line
through this data.
You can add the straight line by clicking once
on a single data point, right-click,
and choose Add Trendline.
In this dialogue box, the default is that you want
a linear line.
But you might notice that Excel does give us the choice
of adding different kinds of curved lines through our data.
So what we are seeing is that Excel provides
a scatter diagram feature to insert
the regressed straight line.
Another thing we can do with this dialogue box
is to calculate the regressed line equation and R
squared value, meaning the coefficient of determination.
Pretty cool.
Some people would call it quits right now.
But we can do more.
Let's go on and explore how we will have Excel calculate
our necessary statistics.
I will first show you Excel's add-in approach
and I will use Excel's Data Analysis ToolPak
which comes free from Microsoft with Excel.
Remember that you might need to turn this add-in on.
And when you do so, this Analysis ToolPak shows up
on the Data ribbon.
I pick the Analysis Toolpak and then scroll down
and pick Regression.
This brings up this dialogue box.
It doesn't look that difficult to use.
Click in the Y data text box and then swipe through
your Y data of interest.
Do the same thing for the X data.
There are a few options to consider.
Yes, I included headings when I swiped the data.
And sure, give me the 95% confidence interval.
Some options would make some sense in an advanced course
in regression analysis.
You have a choice of putting the results on this worksheet
or in a new worksheet.
I'd like to put the results on this same page.
So in this text box, I point to the location
of where I want the results.
When I click okay, the results appear and it looks like
I am done.
But are we?
This tool provides some results and Microsoft labels these
in their own way.
The way that one figures out some of this is to compare
the results of the Data Analysis Toolpak to the results
that the author gets.
Yes, as I probe what Microsoft calls the multiple R
is what we call the coefficient of correlation.
What Microsoft calls R square is what we call
the coefficient of determination.
I can see that what Microsoft calls the coefficients
are the slope and the Y intercept
of the linear regressed equation.
Okay, upon more probing, the sales call P value
matches our textbook's P value.
But what does this really mean?
But that's about it in terms of results.
This add-in doesn't tell us anything else.
And there is no way that we can use it to predict.
Only a few of the things that we want to know
seem to be answered.
The Analysis ToolPak does seem to leave us with a bunch
of possible intermediate statistics
that we would have to take back into equations
in order to draw conclusions from this data.
This doesn't seem like fun.
Why couldn't Microsoft do more?
Unfortunately, this utility was developed back
in around 1992 and has never been improved.
That's around 30 years ago.
Well, some instructors go gaga over this.
But it left me unsatisfied.
Other people in companies have seen the weaknesses
of Microsoft's Data Analysis ToolPak and have developed
their own add-ins.
For example, McGraw-Hill provides its own
add-in called MegaStat.
I show you the output of MegaStat below
the Data Analysis ToolPak.
MegaStat clearly shows the R and R squared values.
It too provides the slope and Y intercept values.
And it provides the sales calls P value.
MegaStat will also do some predicting.
Notice that if I make 96 sales calls, MegaStat predicts
that we will sell 45 copiers.
And MegaStat will create a scatter diagram too.
MegaStat's dialogue boxes provide more features
than Microsoft's Analysis ToolPak.
SO MegaStat is better than Microsoft's
Data Analysis ToolPak.
McGraw-Hill used to give MegaStat to students for free.
But now, guess what?
They charge students for the current version.
Yep, McGraw-Hill realized that they can make money
with a better utility.
There are other companies that also sell
statistical add-ins for Excel.
But none of this ever made sense to me.
Can't we do these calculators ourselves by simply
using Excel as it is supposed to be used,
by using functions, by creating our own models?
Furthermore, these utilities take snapshots of our data.
If our data changes or if you simply wanna explore
what happens when you change values, you need
to rerun these utilities.
This is pretty awkward and slow.
Another drawback is that these utilities are difficult
to use in your own bigger business models and they rely
on the user knowing how to use the utility.
So can't we do better?
Well, you know my answer.
Of course we can.
But to do better, you need to know how to use Excel.
And it also helps to be a little creative.
Welcome to my model for correlation and regression analysis.
My goal in creating this model was to develop a model
that would be simple to use, that would do all
necessary calculations, do predictions, and even help
interpret the results.
Okay, we took care of step one and used Excel
to create our scatter diagram.
The next step is to answer the question
are the data correlated?
Below the data entry area, I calculate the coefficient
of correlation and the coefficient of determination.
These are the exact results that we obtained
in my lecture session.
How did I do these calculations?
Well, Excel has a function called CORREL.
It's a very easy function to use.
For the coefficient of determination, I simply
squared the correlation coefficient.
These statistics are very easy to get to when you know
how to use Excel.
When an Excel function exists for a calculation, use it.
When a function does not exist, create an equation.
Now let's ask the question how confident are we
that this correlation coefficient for the sample
reflects the correlation in the population?
Might it be that the correlation and the population
is actually zero?
In my model, we enter a level of significance.
In this case, an alpha 5%.
In the orange cells, I use Excel to calculate
some intermediate values.
Below these, in red, I calculate the P value.
And finally, right below this, I compare
the calculated P value to our level of significance
and interpret the results.
Remember, if the P value is less than this level
of significance, we reject the null.
My model says reject null.
There is a relationship.
Why can't Microsoft's Data Analysis ToolPak or MegaStat
provide this final interpretation?
Another less frequently used test, which I call step four,
is whether or not the slope is equal to zero.
In a similar way that I used Excel to test the coefficient
of correlation, I test for the slope.
The final cell interprets the results for you.
In step five, we are to determine the linear relationship.
I begin by calculating the slope and Y intercept.
If you look at how I do this, you will see that Excel
has functions for the slope and the intercept
which are easy to use.
You will see that my slope and intercept results
exactly match the values in the scatter diagram.
But by calculating these values in cells, you can reference
these values elsewhere.
This is exactly what I do below.
I use these values to create a linear equation.
I do this using Excel's concatenation character,
the ampersand.
In steps seven and eight, I create an area
where you can use our linear equation for prediction.
First, I calculate Y hat.
And then I calculate the prediction
and confidence intervals.
It may take you a while to compare my results
to that of the Data Analysis Toolpak or MegaStat.
But if you do, you will see that my model provides
the same answers and it provides the results in a way
that is easier for us to understand.
Remember that these add-in utilities do not automatically
recalculate results when the data changes.
My model does.
Well, for us to do problems for correlation and simple
linear regression, all we have to do is identify
the X and Y data, put this into our model correctly
and interpret the results.
When you open my Excel model, the data and results
are for the current textbook's example.
Let's do a more difficult problem, one that involves
a data set.
This problem uses a baseball data file, which I have given
to you in the resources webpage.
Let the attendance be the dependent variable
and the total team salary in millions of dollars
be the independent variable.
Determine the regressed equation and answer
the following questions.
A, draw a scatter diagram.
From the diagram, does there seem to be a direct
relationship between the two variables?
One problem that we immediately have is that we have more
data than what our model can hold.
I'm going to insert some rows starting somewhere, oh,
maybe in the middle of my data range to allow myself
more space for data.
Now, let me copy the salary data and paste it into my model.
Remember that we are told that the salary
is the independent variable, meaning the X variable.
Then I copy and paste the attendance data into my model.
Well, we are all done with the calculations.
Now, we sit back and interpret the results.
And we can use my model for predictions.
The scatter diagram is immediately produced and there does
appear to be a relationship.
Remember that this is a visual relationship
that I am seeing.
In B, we are asked what is the expected attendance
for a team with a salary of $100,000,000?
To answer this, we will use our Y hat equation.
We put 100 million into this X value cell and our model
predicts approximately 2.22 million people.
The next question could be a little bit tricky.
If the owners pay an addition 30 million, how many more
people could they expect to attend?
You should recognize that the slope of this line
is what each additional one million in salary brings in.
So if I multiply this by 30, we can see
that the additional 30 million will bring in
approximately 304,000 people.
The next question asks at the 5% significance level
can we conclude that the slope of the regressed
line is positive?
Using my model we reject the null, meaning that there
is a relationship and it is positive.
Next we are asked what percentage of the variation
in attendance is accounted for by salary?
Well, this is what the coefficient of determination tells us
and it is about 42%.
The last question asks us to compare the correlation
between attendance and team batting average
and between attendance and team ERA.
Which is stronger?
This question will show you how fast and powerful
my model is.
This problem would take some time using the add-ins.
We already have attendance data in my model.
I will just copy and paste the team batting average data
into the X column.
I note the coefficient of correlation and will copy
and paste this in the next cell just as a reference.
I also note that at the 5% level of significance
we do not reject the null, meaning that there does not
seem to be a relationship.
The small R value also suggests this.
Then I copy and paste the team ERA data into the X column.
Which correlation coefficient is larger?
I see that the larger value is for ERA.
For the ERA data, we do not reject a null
and we conclude that the data is related.
The R value of 0.5 is not strong
but it does suggest a relationship.
An interesting note is that the author's P value
matches ours but the author did not interpret
this P value correctly.
This is why having an Excel model becomes so valuable.
One point that I do want to make about
this particular problem is that it is a very
high-numbered problem from our textbook, meaning one
of the hardest problems that can be given to us.
It probably takes the average student, one using
the author's approach, about one to two hours to complete.
We do this in less than 10 minutes.
My model for regression and correlation has made
the analysis quite simple.
I hope that you can appreciate the power behind using Excel
to do correlation and regression analysis.
Give it a try on your own.
Remember that you can explore my methods and equations.
My models are completely open and I'm not charging you
for my models either.
If you do happen to start selling my models
I do want some of the profits.