(funky 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.
Our goal for this session
is to understand correlation and regression analysis.
In previous sessions we learned how to compare the means
and variances between two populations.
In this session, we take a little bit different approach,
we want to investigate whether or not
one variable is dependent upon another variable,
and not that there means or variances are the same.
Correlation analysis is the study
of the relationship between variables.
It is also defined as a group of techniques
to measure the association between two variables.
Regression analysis is a technique used
to map the relationship into an equation,
most commonly a linear equation,
which expresses the relationship between two variables.
If the relationship is assumed to be a straight line,
this is called linear regression.
It is linear regression that is studied most frequently
in basic business statistics courses,
but there can be situations
where the variables are related in a non-linear way.
If so, you would have to use a nonlinear approach
to analyzing the data.
This is referred to as curvilinear regression analysis
and is beyond the scope of this course.
For correlation and regression analysis,
the variables must be either interval or ratio scales.
There are three questions that we want to address.
The first is, are the two variables related?
This is where correlation analysis is used.
The second question is,
is there a linear relationship between the two variables?
We address this question with linear regression analysis.
The third question involves
knowing how strong these relationships are.
We address this question with a variety of statistics.
This graphic depicts some of the possible scenarios
that you might encounter relative to correlation
and linear regression analysis.
The upper chart shows
a perfect negative linear relationship,
every data point is exactly on a linear line,
and as the values of x increase, the values of y decrease,
meaning the straight line has a negative slope,
we will learn how to compute a correlation statistic
and when we do, this statistic would equal
a negative one for this chart.
One is perfect, and the negative means a negative slope.
In our next chart,
the data is not perfectly on a straight line,
but still has a negative slope,
our correlation statistic is less than one.
The third chart shows that the data is not correlated.
We can always draw a straight line through any set of data,
but in this graphic, many of the points do not fall
on a straight line, our correlation statistic is zero.
In the last two charts,
as x values increase, so do the y values.
Mathematically, this is described
as having a positive slope.
In both of these examples,
the correlation statistic is positive
and becomes one when all the data points
fall perfectly on the straight line.
Let's begin with an example.
The Sales Manager of Copier Sales of America,
which has a large sales force
throughout the United States and Canada,
wants to determine whether there is a relationship
between the number of sales calls made in a month
and the number of copiers sold that month.
This is a pretty reasonable business question to ask,
"If we make more sales calls, do we sell more?"
And wouldn't it be cool if we could develop an equation
to help us predict how many copiers will be sold
based upon the number of sales calls?
The manager selects a random sample of 15 representatives
and determines the number of sales calls
each representative made in the last month,
and the number of copier sold.
This data is shown to the right.
Now, we're not going to call a meeting of managers
to debate what they think,
instead, let's use the power of data and analysis
to guide us in what we should think.
As we have learned in the past,
before we begin any number-crunching,
let's look at a graph of the data
so that we might see what is going on.
The graph that we will use for this data scenario
is called a scatter diagram.
A scatter diagram is a chart
that portrays the relationship between two variables.
It is usually the first step in correlation analysis.
When graphing two datasets, you do need to decide
which variable goes on the x-axis
and which one goes on the y-axis.
The dependent variable is the variable being predicted,
or estimated, and it is plotted on the y-axis.
The independent variable provides the basis for estimation,
it is the predictor variable
and it is plotted on the x-axis.
Make sure that you understand this distinction,
because if you get the x and y variables reversed,
your results for regression analysis will be wrong.
This type of mistake has been made by students on exams.
In our example, the question is,
does the number of copiers sold
depend on the number of sales calls?
The number of sales calls is our independent variable,
and the dependent variable is the number of copiers sold.
A scatter diagram can illustrate
something about the relationship,
but statistics can probe deeper.
Are these two sets of data correlated?
To answer this question, we are going to calculate
a statistic called the coefficient of correlation,
most oftentimes represented by little r.
The coefficient of correlation is a measure
of the strength of the relationship between two variables.
It requires interval or ratio scale data.
It can range from minus one to plus one.
Values of minus one or one indicate perfect
and strong correlation,
values close to zero indicate weak correlation,
negative values indicate an inverse relationship,
and positive values indicates a direct relationship.
Here is a graphic illustrating the correlation coefficient
and its interpretation.
I also have this little animation that shows what happens
when the coefficient of correlation
goes from zero to one, to minus one.
The equation to calculate the correlation coefficient,
well, looks pretty ugly.
It requires a lot of intermediate calculations,
all those Xs multiplied by Ys, and other junk.
As you should be able to guess by now,
we are going to use Microsoft Excel
to calculate the correlation coefficient.
Can you imagine having to memorize this equation, not I.
For this example, r calculates to 0.865,
which is positive, meaning a positive relationship
and because it is close to one,
we can say that the association is strong.
We have to be careful
in interpreting a very subtle wording of this association.
Saying that the association is strong does not mean
that more sales calls causes more copier sales,
it only means that they seem to be related.
To make this point with another example,
let's assume that we have some data on,
oh, how frequently a cat meows in a night,
and we have some data whether or not there is a full moon.
The data between cat meows
and whether there's a full moon may be related,
and you might want to say
that the full moon is causing the cat to meow,
or that the cat meow is causing the moon to be full.
But statistically, all we can say
is that there seems to be a relationship.
Maybe the cat meowing is caused by some other factors,
this remains to be investigated.
The interpretation about the relationship
between two variables
is often misconstrued in popular press.
You might sometimes hear the results of some odd studies
saying that one thing is causing another thing,
and they seem to be completely unrelated.
It might be that the data is related,
but again, we can only statistically say
that they are related,
we can't say that one is causing the other,
until there is further investigation.
The name for these weird associations
is spurious relationship.
And I give you a little bit more information about this
on our Resources webpage.
Another measurement for strength of relationships
is called the coefficient of determination,
sometimes represented as r squared,
which is the proportion of the total variation
in the dependent variable, y,
that is explained or accounted for
by the variation in the independent variable, x.
It is the square of the coefficient of correlation
we see once again having two parameters,
the coefficient of correlation
and the coefficient of determination,
that are directly related, one is a square of the other.
Remember, this same thing happened
with the standard deviation and variance.
The coefficient of determination ranges from zero to one.
If the coefficient of correlation happens to be zero,
zero squared is zero, if it happens to be either positive
or negative, remember that scoring a negative number
always results in a positive number.
This is why the coefficient of determination
can never be negative, it does not give any information
on the direction between the variables.
This is the strength of the coefficient of correlation.
In our copier problem,
if we square the coefficient of correlation which was 0.865,
the coefficient of determination becomes 0.748.
We can then say that approximately 75% of the variation in y
is explained by the number of sales calls, x.
In our example problem, we calculated
the coefficient of correlation to be 0.865.
However, this was based on a sample of only 15 sales people.
Could this relationship be due to chance?
Maybe so, maybe the correlation is really zero,
meaning no correlation.
We can test the significance of the correlation coefficient
in a similar way that we tested hypotheses
in our previous sessions.
In this case, the null hypothesis
is that data is not related,
meaning that the coefficient of correlation is zero,
it is up to the data to prove that there is a relationship.
For this test, we use the t statistic
and a two-tailed test is appropriate
because of the not-equal-to statement
being used for the alternative hypothesis.
Just like before with the test of hypotheses,
there are two ways that people can perform this test.
The old method is by using the t distribution table
in the back of the textbook.
Again, I prefer the p-value approach,
if the p value is less than our desired level of confidence,
we reject the null, meaning that the data suggests
that there is a relationship.
You'll see me do this in our next session using Excel.
When exploring the relationship between two variables,
a common question is, can you develop
an equation for the relationship?
This technique is called regression analysis.
The most common and easiest approach
is to calculate a linear equation,
meaning a straight-line equation.
Before we learn about this technique,
I want to share with you some data
that I collected many years ago,
this data is about the performance of an industrial robot.
Back when I worked for John Deere,
at the John Deere Technical Center,
we needed to analyze robot performance
as to how accurate it was.
I was involved in a project
where we built a robot repeatability analyzer.
We would mount a probe in the robot's gripper,
program the robot to go to a point,
zero, zero, zero, in 3D space,
and then we would have the robot move
in some kind of exercise motions,
and finally tried to return to this zero, zero, zero point.
This illustration shows an example of a plot of the data.
You might notice that one set of data is for the x-axis,
one for the y-axis, and one for the z-axis.
Well, you might think that the robot should be very precise,
and this particular robot actually is, but it doesn't mean
that every point is on the same straight line.
If you look at the x-axis and compare it to the y-axis,
you might visually conclude
that the y-axis varies more than the x-axis.
Take a moment and look at the z-axis data,
it seems to be almost on a straight line,
but this straight line is moving over time.
Later on, we were able to determine
that the z-axis, which is the up and down motion
of this particular robot, was indeed the least precise
and it wandered based upon temperature.
Pretty interesting data.
The picture portrays a visual image of what's going on,
but we still wanted to analyze this data statistically
to calculate its overall precision
and to see if there's any trend.
We needed to know if this particular robot
was meeting its advertised performance statistics.
Hence, we used linear regression analysis.
Sometimes this is referred to
as bivariate regression analysis, and it's used to analyze
the relationship between two variables.
It specifies one dependent, or response variable,
and one independent, or predictor variable.
This hypothesized relationship may be linear,
quadratic, or some other equation.
Now, I'm going to take you back
to, oh, maybe geometry or algebra.
Whenever you have two or more pairs of x and y values,
you can always calculate a straight line between them.
A straight line is defined by two parameters,
a slope and a point called the y-intercept.
Sometimes mathematicians will use different variables
to represent the slope and the y-intercept,
so I show you both here.
The error term is not observable,
it is assumed normally distributed
with a mean of zero and a standard deviation of sigma.
This is how our author prefers
to show the linear regression equation.
Y hat, is the estimate of y given x
and a is the y intercept, b is the slope of the line,
and x is any value of the independent variable.
This graphic illustrates the overall concept
of linear regression analysis.
It shows how we might try to draw
a straight line through our data points.
You might notice that our data points
will never exactly fall on a straight line.
Which line is best?
Well, the best line is that line
that minimizes all the errors
between where the line thinks the data point should be,
and where it actually is.
The mathematical technique for calculating the straight line
is called the least squares method.
This method determines a regression equation
by minimizing the sum of the squares
of the vertical distances between the actual y value
and the predicted y value.
Here are the equations to calculate the slope
and the y-intercept, they look a little bit complex,
and they really are,
if we were going to do these calculations by hand.
Don't worry, you don't have to memorize these equations
because we are going to do these calculations
in Microsoft Excel.
For our copiers problem, the linear regression equation
becomes y hat equals 19.9632
plus 0.2608x.
We will revisit this example in our next session
and find this regressed line using Microsoft Excel.
Once we have this equation,
we can use it to predict a y value,
meaning how many copiers are sold for any x value,
meaning any number of sales calls,
I want you to realize how powerful this technique is,
we are predicting an outcome given some input condition,
we are acting as a fortune teller,
except we are using data instead of mystic intuition.
For example, how many copiers would we expect to sell
if we made 96 sales calls?
We put 96 into our equation and come up with 45.
Just as we tested the significance
of the coefficient of correlation,
we can test for the significance of the slope.
The null hypothesis is there is no slope,
meaning the slope is equal to zero.
The alternative hypothesis is
the slope is not equal to zero.
We again use the t distribution to make this test.
We will do this using Excel.
As with many techniques,
there are some assumptions underlying linear regression.
For each value of x, there is a group of possible y values
and these y values are normally distributed.
The means of these normal distributions of y values
all lie on the straight regressed line.
The standard deviations
of these normal distributions are equal
and the y values are statistically independent.
This means that in the selection of a sample,
the y values chosen for a particular x value
do not depend on the y values for any other Xs.
Here is a graphic illustration of these assumptions.
It is possible to calculate confidence intervals
and prediction intervals
for both the slope and the intercept.
The prediction interval occurs
when we have only one x value of interest.
The confidence interval occurs
when we have a group of x values of interest,
when we have a group of x values, meaning more data,
our interval, meaning the confidence interval,
will be smaller, meaning more accurate.
If, for example, a group of sales persons, in aggregate,
were to make 50 sales calls, our 95% confidence interval
would be between 27.4 up to 38.6 copies.
If we were interested in just a single salesperson
making 50 calls, our 95% prediction interval
would be between 17.4 up to 48.6 copiers.
Notice that the prediction interval
is wider than the confidence interval.
This graphic shows the data points,
the regressed line, in the center,
the confidence intervals shown in red larger dash lines
and the prediction intervals
shown as green small dashed lines.
The prediction interval is always wider
than the confidence interval.
As we attempt to make a prediction for an x value,
the further away this x value is from the overall mean,
the confidence and prediction intervals widen.
Well, my guess is that your head might be spinning
after seeing all these equations.
When I was an undergraduate student taking statistics,
I had to calculate all these statistics
with a calculator, it took time.
I think you will find that the calculation
have become much easier when done in Microsoft Excel.
Well, there we have it,
correlation and linear regression analysis.
I am very eager to show you
how to do this with my Excel model.
(funky 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.
(funky music)