Welcome to Excel 2013, Statistical Analysis,
video number 24.
And if you want to download this workbook and follow along,
click on the link below the video.
Hey, this is our last video for chapter 3, Numerical Measures.
And we want a numerical measure for two variables.
Now, back in chapter 2, we saw that if we have x, y data,
we can plot it on a scatter plot and visually
check to see if there's some relationship.
The relationship could be direct as x increases, y increases.
For temperature and ice cream sales,
it makes sense that probably the hotter it is,
the more the sales for ice cream.
But getting a sample of data points, plotting it
helps us really determine, but here only visually.
So in this video, we're going to see
how to calculate a measure that will tell us
the strength and direction of the relationship.
Now, here's our first one direct or positive relationship.
Here's another example-- oh, chicken soup sales,
are they related to temperature?
Well, we went out and got some data.
And it looks like as temperature increases,
sales of chicken soup decrease.
So this is inverse or negative.
We saw how to chart this and talked about this back
in chapter 2.
Our next example studying time for a test and test score
looks like it's direct or positive.
As time increases, test score tends to increase.
How about our next example, number of years
using Excel and expert rating.
That determines your knowledge of Excel.
As the number of years increases,
there doesn't seem like there is any relationship
between rating.
And that tends to make sense.
Many of us have used Excel for years and years.
We know our one little thing.
And we never learn anything more.
Some people, however, do get better over time.
But there is no relationship from this data set.
And one last data set here is temperature and energy expense.
And we're interested in the relationship
between temperature and energy expense.
Relationship looks non-linear.
As x increases, y decreases for a while and then
starts to increase.
When we calculate our covariance and coefficient of correlation,
we will plot this and talk about what those measures mean
for a data set like this.
Now, let's go to our next sheet.
We're going to go into the sheet to variable x bar lines,
because here's the deal.
We want to try and understand the math, and the calculations,
and the logic of what we're doing when we're
calculating the measures covariance
and coefficient of correlation.
So I want to do a little trick here.
This is just ice cream sales y, temperature x.
We plotted.
We right clicked and did trend line.
And we see that trend line, but I actually want to plot x bar
and y bar.
Hey, we've only talked about x bar and not y bar.
Well, these are some values.
And so are these.
Each one of these would have deviations as
compared to their means.
But what I want to do here is I want to do a visual trick.
I'm going to plot x here, 66 and 66,
with a y value of 0 and 1,000.
That means it will plot an exact vertical line, where x bar is.
And then for my y bar that's just the average.
I'm going to take that value, listed twice.
And I'm noticing that it looks like it goes from 30 to 100.
So I'm going to use those two points.
Why am I going to plot two new lines on this chart?
So I can see the preponderance of values above or below x
bar and above or below y bar.
Now, these are just two points.
And that's all you need to plot a line.
So I'm going to click on the chart, go up to design,
and the real power of charting comes from select data.
Pull this down.
Add the name, x bar-- that's Fahrenheit-- the x values.
Here they are.
And highlight that little one curly bracket--
one curly bracket--
and delete, because that'll get in the way.
And then highlight your Y's.
Whoops, highlight your y's.
The [INAUDIBLE] was still going.
So I just redirected it.
Now I'm going to add y bar ice cream sales, x values.
And I'm going to highlight this, delete, and then highlight
these two, and click OK.
Click OK.
These lines don't usually come out like this.
I'm going to try to right click, change series, chart type.
It usually comes out.
And I'm going to pretend like it came out this way.
So it usually it comes out with two dots.
So you have to right click, change series chart type.
And in 2013, they so improved how
we can change the chart types.
Look at this.
There is all of the series in this chart.
And I can just go to any one of them
and select whichever chart I want.
Now, I'm going to come to the x bar and go from scatter points
to scatter with smooth lines.
Click OK.
Now, why do we do this?
Because check this out.
What we're going to do is we're actually
going to calculate all of the deviations for y
and all of the deviations for x.
Well, here's x bar.
Above, those are all positive.
Below, those are all negative.
How about this one, y bar?
All the ones above are positive.
All the ones below are negative.
Now, there's four quadrants to this plot.
You might remember this from algebra, 1, 2, 3 4.
But check this out.
This is above x and above y.
That means the deviations for both are positive.
So in quadrant 1, if we start multiplying the two deviations,
they all come out positive.
In this quadrant, below x, above y, the deviations, that is,
that means if you multiply them, you get positive, negative.
So you'd get a bunch of negative values.
Down here, negative, negative.
So if you start multiplying them,
you get a positive number.
Here, negative, positive.
So you get a bunch of negative values when you multiply them.
So when we get a preponderance of values in quarter 1
and 3, boom, when we multiply them and add them and do
our calculations, we're going to get something positive.
So that will be our trigger to indicate that this
is a positive relationship.
If they all tend to be up here and down here,
quadrant 2 and 4, then when we multiply them and add them,
it'll tend to be negative.
That calculation will be called covariance.
And we'll do that in just a second, but notice positive.
Let's look at the next example, temperature and ice cream.
Oh, look at this negative.
Preponderance of values are in quadrant 2 and 4.
So when we multiply an atom, we'll get something negative.
That'll be a measure that will indicate
that our relationship tends to be an inverse or negative.
Scroll down here.
Here's that years of using Excel and Excel
Expert Rating, when the preponderance of points
tend to not be any particular quadrant or not
a bunch in 2 and 4 to give us negative
or not a bunch in 1 or 3 to be positive,
then we will get not a very big number,
because it's adding lots of positives and negatives.
And down here-- oh, look at this.
For this particular data set, we're
going to have values in all quadrants.
So actually, covariance and coefficient of correlation
will be really small.
And it will indicate that the linear relationship is weak,
but we have to be careful, because when
we say linear relationship is weak,
it's not necessarily saying anything
about any other type of relationship.
Let's go over to two variables covariance and correlation.
Here's our formula.
Notice the deviation for x bar times the deviation sub y bar.
And then we're simply getting divided by n minus 1.
And this notation right here will
indicate sample covariance.
Now, this measure will be good as a starting point covariance,
but we'll find out that there will be a problem with units.
So we're going to divide it by the standard deviation
to get this top number in units of standard deviation.
Now, because we're dealing with x and y,
we're going to multiply sample standard deviation of x time
sample standard deviation of y.
Now, let's go ahead and make this calculation.
We definitely want to do it longhand,
just like we did standard deviation,
because the process of doing that will help
reveal the meaning of this calculation.
Then after that, we'll just use the covariance
and the correlation or PEARSON functions.
I'm going to scroll over here.
We're going to calculate the mean for our x values
Fahrenheit, Enter, our sample standard deviation.
Notice these are relative cell references.
Enter equals count.
And I'm going to highlight.
That will count them, close parentheses, minus 1.
Now, those are relative cell references.
So I'm in a copy versus over to get our x bar
and our standard deviation for y.
Now, let's calculate our deviations.
Hey, the particular x minus x bar.
And we're going to F4 to lock it.
Control-Enter.
Copy it down.
Double click, and send it down.
Go to the last cell, F2.
It looks like it's got it right.
Hey, those are x value deviations.
Let's do y deviations, equals particular y minus
y bar and F4, Control-Enter, double click, and send it down.
Go to the last cell, F2.
It looks like it's working fine.
Now, let's multiply.
Now, before we multiply, you can already
see, negative negative, negative negative, positive positive.
Oh, there is a positive negative.
So that will go against the positive or direct
relationship.
Positive positive, positive positive, negative negative.
So just looking at the values, if we
were going to multiply them and then add them,
this would tend to look like a direct or positive relationship
equals 1 cell to my left times 2 tells to my left relative cell
reference Control-Enter, double click, and send it down.
Hey, guess what?
If we look at coefficient of correlation, remember,
we said we're going to take the covariance
and divide it by the standard deviations multiplied,
what we just did is we calculated
all of the x bars and the y bars, multiplied them,
and now let's do this sum part.
So you ready?
Alt-Equals, and I'm going to direct that exactly
to the cells I want and Enter.
So that 431436.91, that's the top part
before we divide by 1 minus n.
So now we're going to take equals total
of all the multiply deviations divided by our n minus 1
and Enter.
That is sample covariance.
Now, the problem with covariance is,
yes, if you have the same units, you can compare things,
but sometimes you get units problems.
You can do covariance for inches.
And then on the same data set for feet,
they're not going to be able to be compared,
because they're in different units.
The inches would obviously be much greater.
Now, sample covariance, after we do it a few times
and get the hang of it and understand this visual meaning
of covariance, we're just going to use equals covariance.
Now, there's a P and an S.
The S is for sample, array one, array two.
We're going to see many functions throughout this class
that are dealing with x and y.
When it says array 1 and array 2,
it doesn't matter which one you put in first, but some of them
will not say array 1 or 2.
They'll say y values and x values in a particular order.
So I always get in the hang of when
I'm doing calculations associated with scatter
plots, or linear regression, or covariance,
or anything with x, y, I always put the y in first.
Then when I get to the functions that matter,
I don't mess it up.
It wouldn't matter though.
So array 1, I click comma array 2 and Enter.
Oh, that is amazing.
Now, sample covariance measures the strength
of the relationship, but has problems with units.
Coefficient of correlation will measure
the strength and direction of the linear relationship.
It is not causation though.
Sometimes instead of coefficient of correlation,
they call this r.
So you're ready, equals--
and I'm going to take that one we calculated
longhand and divide it by-- and now, I
need to multiply the two standard deviations.
I could do it in parentheses, but I'm just going
to use the Product function.
I could just highlight a range.
And I have, if I scroll over-- so I make sure I don't mess it
up--
standard deviation s for both of these.
So now you can see that it's got product,
multiplying, Control-Enter.
Now, it comes out 0.95.
Coefficient of correlation always comes out
between minus 1 and 1.
The closer it is to 1, the stronger
the positive direct relationship.
The closer it is to minus 1, the stronger
the negative, or indirect, or inverse relationship.
The closer to 0, the weaker the linear relationship.
So that's a very strong linear relationship.
So as a measure, it says there is
a strong positive relationship.
The reason this is strength is because the closer to 1
or negative 1, that tells you the strength.
The minus and the positive tells you
the direction, minus positive.
Luckily, there's actually two functions.
CORREL for correlation or PEARSON.
Let use equals PEARSON.
And the reason why is that named after the guy who
came up with this calculation.
So array 1 or 2, I'm always going
to put in y first, even though it doesn't matter and Enter.
Pretty amazing to have a measure to tell us
the strength and direction of our linear relationship.
And let's go down and try our example for chicken soup.
Let's just skip to the chase.
Sample covariance equals covariance.
And we're going to do sample, y's, x's, Enter.
We might have a problem with units.
We want to know the strength and direction.
So we're going to use--
and I'm going to just show you that CORREL will work.
So CORREL will give you exactly the same thing as PEARSON.
It's one of those strange situations, where you're
going to have two functions that actually
make the same calculation.
Control-Enter.
So those are coefficient of correlation r or r sub xy.
Now minus 0.84, that's a pretty strong negative
or inverse relationship.
You can see the line here.
Visually, this is how we did it, graphical method.
Here's how we did it with a numerical measure.
Now, let's go look at our next example,
relationship between years, using Excel and Expert Rating
equals covariance.s, y's, x's.
Uh-oh, look at that.
I'm going to point to this.
When I see that little cursor, I'm going to a click
and drag up.
And watch this.
I'm going to cheat.
I'm going to highlight both of them.
Copy.
Enter.
Equals PE, Tab, Control-V. Looks like I want to paste it.
And I want to highlighted the parentheses too.
So Enter.
And there's our coefficient of correlation.
Wow, 0.08.
So that's close to zero, very weak.
Look at sample covariance.
Yes, it's positive, but it's incredibly small.
So both of those measures are telling us
not a good linear relationship.
Now, let's go down here and look at this data set, because this
doesn't have a strong linear relationship,
but visually it says that there might
be some other non-linear relationship.
Equals covariance.s Control-Shift-Down-Arrow.
Control-Backspace-Comma.
Click on the top cell.
Control-Shift-Down-Arrow.
Control-Backspace.
And I'm going to try and highlight this.
Copy and Enter.
Oh, OK, well it's not huge, but it's got a small negative.
Let's do equals PE for PEARSON Control-V and Enter.
So minus 0.34.
Very weak negative relationship, but neither one of these
are strong enough to suggest that we have
a good linear relationship.
But again, if you plot it visually,
you can see that there might be some other type
of relationship.
Control-Home.
So in this video, we talked about calculating covariance
and coefficient of correlation.
We did it longhand.
And then we saw covariance.s for sample data.
And we saw either PEARSON or CORREL.
That's it for chapter three.
We'll see you next chapter, chapter 4,
where we start our studies about probability.
All right, see you next video.