Welcome to Highline BI348 class video number 45.
And if you want to download this workbook, BI348 chapter
4, or the PowerPoint, click on the link below the video.
I'm going to hit Shift-F5 and start this PowerPoint.
Now in the last video, we talked about scatter plots-- one two,
three-- to determine what type of relationship
there was between two variables.
But this was a visual means for determining
whether we had positive, negative, or no relationship.
I'm going to jump ahead to slide number nine.
We want to talk about a numerical measure called
covariance and correlation.
These will be measures to investigate
if there's a relationship.
And these numerical measures will
be more precise than that positive, negative,
and no relationship that we had with scatter charts.
Now next slide.
Before we actually look at the math and the formulas
for calculating covariance and correlation,
we actually want to see how to create this chart here.
And this chart will involve plotting the scatter points.
But we want to plot a x bar line and a y bar line.
Now these plodded lines will help
us understand the math behind covariance and correlation.
Not only that, but later in the class,
we'll be plotting, in particular, this y bar
quite a lot.
So we're going to go over to Excel.
And here we have our data set, weekly ad expense x,
weekly sales y.
And just like we did in the last video, we plotted.
And we have all of our markers out a certain x, up
a certain y.
Now our goal is to plot the y bar line and the x bar.
Now down here I've already, for x, calculated the min, the max.
And look at this.
I've calculated y bar, which is just
the average from the y column.
And I've listed it twice.
Why?
Because we need one, two points to plot a line.
So this point will go out 14,000 and then
up to the y bar of about 370.
That will be one point.
And then 64,700.
That's the max up here, all the way up to y at 370.
That will be the second point.
And then we'll draw a line.
So we come up to the chart.
And we can right click Select Data.
Or we can go up to Design, Select Data.
And when we click on this button,
this is the real power of charting,
because we're allowed to add series of numbers,
edit, remove, and change the horizontal axis.
So I'm going to click the Add button.
And our series name, when I click on y bar,
that'll be the name of the series
or the numbers inside our Select Data dialog box.
That will also show up in the legend.
Our x values, we have our two x's, Tab.
And we want to make sure and delete that little array
and then highlight our y's.
When I click OK, I have plotted those two lines.
Now notice they showed up as two markers just
like these, because that's what this chart is about.
But we can change the chart type for just one of the series.
I'm going to click on the data point.
And right click and I can point to Change Series Chart Type.
Or I can come up to Change Chart Type.
And look at this.
In 2013 and later, it lists each one
of the series, the lists of numbers.
And we can change just that particular series.
So I'm going to say, markers and line.
Click OK.
Now we want to do the exact same thing again.
Right click.
Select data.
Add.
Series name, that's x bar this time.
To have the x values, both of the x bars, Tab.
I'm going to delete that.
And the two y's, the min and the max-- when I click OK,
click OK.
Now we have our two markers.
Right click.
Change Series Chart Type.
X bar, and we want scatter with smooth lines or markers.
Click OK.
And there we have x bar and y bar.
Now we want to think about what these markers mean
in relation to x bar and y bar.
Now y bar is 370,000.
And x bar is 40,000.
Let's just look at this point right here.
You can see in the screen tip-- the little pop
up there-- it says 57,000 for the x value
and 731,000 for the y value.
Compare 730,000 to the 370, that's
a huge positive deviation.
If we compare that 57,000 to the 40,000 for x bar,
that's an x bar deviation positive.
That means that particular point is above the y bar
and above the x bar.
If we were to take both of those deviations--
positive, positive-- and multiply them,
we get some big positive number.
Now let's think about a number down here in this region number
three.
You can see from the screen tip, 17,000 for the x.
Well if this is 40,000 and this is 17,000,
that's a huge minus deviation.
Now look at that screen tip, 110,000 for the y.
Y bar is 370.
If I'm taking the particular value
and subtracting the y bar, that's
a huge negative deviation.
Think about this.
If I take x bar deviation and y bar,
they're both negative, negative.
And multiply them.
Guess what?
I get a huge positive number.
So that's the idea behind our first calculation covariance.
We're going to take all the deviations
for every single point.
Multiply them.
And then add them.
Now for this particular set of markers,
since most of the markers are in quadrant one and three, that
means when we add them all up, we're
going to get some huge positive number.
Whereas in quadrant 2 and 4, think about that
point right there.
We have a negative deviation for x
and a positive deviation for y.
So we're going to get a bunch of negatives times positives.
We will get a huge set of negative numbers over here
and negative numbers down here.
So when the preponderance is in two and 4
and we add all the products, we're
going to get some huge negative number.
Now I want to go over to the sheet
Covariance and Correlation.
Now here's our formula for sample covariance.
We're going to multiply, in the numerator, the deviation
for x times the deviation for y.
And then add, and then we're going to divide by n minus 1.
Now here's our data set.
Here's the weekly ad expense and the weekly sales.
This is the y.
This is the x.
So we're going to calculate deviations and then multiply
them.
Now I actually already did the count of all of the records.
Got 109.
N minus 1-- our average-- that's x bar, y bar.
There is our sample standard deviation.
And there's our samples deviation for our y's.
So you ready?
Our deviations equals-- and there's
our particular x minus our x bar.
There's our x bar.
Now I'm going to make sure F4 to lock it.
Control-Enter and copy it down.
Now these are actually deviations.
And this should be familiar, because we
did a lot of this when we were calculating
standard deviation, particular value minus the x bar.
When we add up all of our deviations, Alt-Equals,
of course we're going to get 0.
So now we're going to calculate deviation for y.
There's the particular y minus our y bar right there.
F4 to lock it, Control-Enter and copy it down.
When I add these up, Alt-Equals, those are deviations.
So the sum of those are always zero.
Now we can multiply.
I'm going to take x deviation times y deviation.
Control-Enter, copy it down.
And when I add these up, Alt-Equals, that's
going to be the numerator.
Now we can come down here and actually calculate covariance.
Equals the sum of all the products
of the deviations, divided by-- and there is our n minus 1.
And when I hit Enter, that's a big positive number.
So the linear association between x and y is positive.
Now we don't have to go through all these steps manually
to calculate it, because there's a built in function.
But certainly seeing this chart and how we're multiplying
and the logic behind that helps.
But once we understand that, we can simply use covariance.
And there's a p for population and an s for sample.
And it has array 1 and array 2.
We've been using the convention we're going to put y first,
because some of our other linear regression functions
require that.
Array 1, array 2, you can put it in any order.
And when I hit Enter, exactly the same thing.
Now covariance is great, except for there is one problem.
Imagine if instead of dollars up here,
we had feet, like someone's height or something.
And we calculated covariance.
And we got a number.
Now imagine if we had the exact same data points,
but they were in inches.
We'd get a much larger covariance,
even though the two data sets had
the same linear association.
So there's a problem with units for covariance with this.
So guess what?
We're actually going to take covariance,
put it in the numerator, and divide it
by standard deviation of x times standard deviation of y.
This is a way of standardizing it
and in essence, getting covariance
per unit of standard deviation.
Now why are we multiplying?
Well, you can see up here we multiplied the deviations.
So we're going to multiply these down here.
And the beauty of this number is that it will always
come out between minus 1 and 1.
If you had a perfect negative correlation,
you'd have a line like this.
And all of the points would be exactly on the line.
If you had a perfect positive relationship,
you'd have that line down the middle
and all the points would be exactly on the line.
So if we get a negative number from correlation that's
close to minus 1, then we'll have
a strong negative relationship.
If it's close to 1, it'll be a strong positive relationship.
We could actually see a picture of this
over in our PowerPoints.
Here you go.
Our R comes out to zero.
That means there are data points in all four quadrants.
If it came out exactly negative 1, it would look like this.
Positive one, it would look like this.
An example of a strong positive relationship
would be something like point.
0.89.
An example of a strong negative relationship
would be something like minus 0.89.
And a moderate direct relationship-- you can see
there's some dots.
But it looks like as x increases, y is increasing.
We get something like R equals 0.6.
So the closer to minus 1, the better
the negative relationship.
The closer to 1, the better the positive relationship.
Now let's go calculate this.
Here is our coefficient of correlation.
I am just going to take my covariance I already calculated
and divide it by-- now I need to multiply the standard deviation
for x and y.
So I'm going to use the product function
and simply highlight the standard deviation for x and y.
And when I hit enter, 0.917.
So that's pretty strong, because it's close to 1.
It's a positive number.
So we know it's direct.
Now we don't have to do all the steps for our calculation,
because there's actually two functions.
Equals PE-- Pearson.
Pearson is named after the statistician who invented this.
So they named this function.
And we're going to have array one, array two.
I'm going to put the y's comma, and then the x's.
I'll get exactly the same thing.
Not only is there Pearson, but we have correl for correlation.
Array one, comma array two, and there it is.
When I hit Enter, I get exactly the same thing.
Pearson or correl.
Now here we saw a positive relationship.
I want to go over to the sheet Covariance and Correlation.
Actually this should be blue.
This should be a blue sheet.
Here's the example we used earlier.
Bike weight in pounds is our x, and price as our y.
These are racing bikes.
So we did our scatter chart.
And we saw that it looked like a negative or indirect
relationship.
So if we do covariance, equals COV.
And we're going to do the .s for sample.
Array one, comma array two, so this will come out negative.
And there it is.
If we do coefficient of correlation equals Pearson.
I'm going to say array one, comma array two.
And there we go.
So minus 0.897.
The negative says it's inverse or negative relationship.
That means as x is increasing, y is decreasing.
And it's close to minus 1.
So that's really strong.
Now let's scroll over and see one last example.
Here's a plot of an x y scatter.
You can see quadrant one, two, three, four.
There's markers everywhere.
So there is not going to be much of anything.
Equals covariance of the sample, array one, comma array two.
And a I'm going to cheat.
watch this.
I'm going to highlighting, Control-C, because I'm
going to use that again, Enter.
So that's very, very, very close to zero.
Not much linear association at all from covariance.
Equals Pearson Tab.
And I'm going to Control-V. When I hit Enter, look at that.
Absolutely almost zero, there is no correlation
between these markers.
There is no positive relationship
or negative relationship.
It's just all over the map.
So in this video, we saw how to calculate covariance
and coefficient of correlation.
And we even saw how to plot our x and y lines,
to visually look at where the markers were
in relation to the two means.
Now next video, when we come back,
we'll see how to create our equation
to make predictions by calculating
the slope and the y-intercept.
All right, we'll see you next week.