In this video we want to take a look at
how to calculate the p-value for the
Pearson correlation coefficient in
Microsoft Excel using the function
approach. Now unfortunately the p-value
isn't very direct in Excel for the
correlation coefficient, we have to get a
little bit creative, so let me show you
what I mean here.
First let's go ahead and type p value in
this cell and then we'll work in here to
get our p-value. Now the formula for the
p-value is as follows: we have equals one
minus F distribution, where F distribution
is a function, and then we have all this
information here. Now in this formula
I've underlined, to make it a little bit
more palatable, those values which we are
going to input. We're going to input
these values or, in other words, these are
the values that are going to change from
one problem to another, when calculating
the p-value. All of these other things, 1
minus F distribution, squared, one,
true, and one, those will always stay the
same. So in our example here we had a
correlation of .86 with 13 people, 13 sets
of scores. Let's go and take a look at
that to make sure. So here in Excel
notice that our correlations .86, if we
round to two decimal places, and we do in
fact have 13 people. Each person provided
a number of hours studied for the exam and
they had an exam score. So our N here
is equal to 13. So going back to our
formula, what we can do is if we plug in
r here of .86, right here, and right here,
and then we plug in,
I have N - 2, and N - 2, so
instead of 13, since N - 2 is 11, we
can plug in 11 right here, and right here.
Let's take a look and see what that
would look like.
OK that's what we would have here,
notice .86 for r, .86 for r, once again,
and then 11 for the N - 2, and then
the rest of the values, as I had said
before, just stay the same, they're
constants, they're always the same. Now in
Excel this value here indicates a
squared value, so raised to the power of.
So here we have .86, raised to the second
power, or in other words just square it. This
is .86, squared. What I'll do is I'll go
ahead and copy this here and enter it
into Excel and I'll enter it right here. OK
and you can see it up here in the
formula bar. When I enter that value, I
get a p-value of .00016284.
And if you saw the video on obtaining
the p-value using the data analysis
toolpak for these the same data here, you
would see that this p value is equal to
the p value that we found using the
regression approach, within rounding
error.
So in summary of a p-value .0001 or .0002
if round. And now we want to do is
test this for significance. so if we use
an alpha of .05
we want to ask ourselves is this p
value, is it less than .05? And we
can see that a p-value of.00016 is
definitely less than .05, so that
indicates that this correlation is
statistically significant, meaning that
the .86 is significant, which
indicates that there's a significant
positive relationship between the number
of hours studied on the exam and the exam
grade. So before we close here let's go ahead
and take a look at our written results.
And the written results are as follows:
there's a significant positive
relationship between the number of hours
spent studying and the grade on the exam,
and then we have r, and that stands for the
Pearson r correlation, with 11 in
parentheses, that stands for the degrees
of freedom, which I'll show you in just a
moment.
This is our correlation coefficient that
we saw earlier and then we have p is
less than .001. OK now a few
things here, our df, first of all, is equal to
N - 2. And if we went back to our
example, we would see that there were 13
people in that example, so the df is equal to
13-2 or 11. And that's why there's an 11
here in parentheses, the degrees of
freedom go here. And then for the p of
less than .001, since we did test at
an alpha of .05, it would be acceptable to
go ahead and write, instead of p is less
than .001, to write p is
less than .05, if we wanted to.
However this provides us a bit more
information. Since our p-value was in
fact .0001 or .0002, it is in fact less
than .001. So we could say our
p-value, this p-value here, is less than,
or p is less than
.001. And that's why I put that in the
written results. Now once again you could
put p is less than .05 if you
wanted to. But this value gives the reader
a bit more information.
It lets them know how significant the results
were. And usually we don't go any
further than p is less than
.001. For example I don't see something
like that (p < .00001) very often. Usually it goes to
about one one-thousandth of a place or
so and that's where it stops.
So if this gets smaller and smaller we'll
usually just stop at p is less than
.001.
This concludes the video on finding the
p-value using the function approach in
Microsoft Excel. Thanks for watching.