In our last video, we went ahead and calculated the correlation on these
values,
but we didn't get a significance value for the correlation to see whether it
was statistically significant or not.
And unfortunately in Microsoft Excel, when we run the correlation procedure through
the data analysis toolpak it does not give us a p-value,
so we can't assess whether a given correlation is statistically significant
or not. But thankfully there is a work- around here and what we need to do is if
we go to regression, and select that, we can obtain a p-value this way.
Now what we'll do is - notice there's two boxes here - input Y range and input X
range;
for input Y range we'll go ahead and select the exam grade values and I'm
also going to select the label or the variable name in the first row here,
exam grade.
Next click in the input X range box here and then select hours studied and all the
values there and then be sure if you do select the variable names as I have here
be sure to select labels. Okay that looks good,
let's go ahead and click OK. And then here we get our output; I'm going to
expand this a little bit just to the values we need there's a lot of
information here but I really only need a few values and let's go ahead and
highlight those here.
First of all let's make this font a little bit bigger, so it's easier to read.
All right and then we'll expand what we need here. Now first of all if you watched
the last video on obtaining a correlation under where it says multiple
R in regression
if we have just two variables, like we do in this correlation example where we
have hours studied and exam grade then with two variables the multiple R is
just identical to the Pearson r. So notice how this is also once again
.86.
So we could
really just run regression to get our correlation if you look under multiple
R here. Now for the p-value what we want to do is we can go to the ANOVA
table and under significance here this is our p-value and it is very small
there. Now just in case you're interested there's also the exact same p-value when
we have just two variables also is located under hours studied where it says p
value.
So notice how these two are the exact same here significance F
and p-value; they're identical. Now what we're going to do here is we'll use alpha
of .05
and the decision rule is as follows: if our p-value, as given by the significance F,
or p-value right here
if it's less than .05, then the correlation is statistically
significant
and since .0001 is definitely less than .05,
that indicates the correlation is in fact significant.
Since
this correlation coefficient of .86 is statistically significant,
that means that there is a significant positive relationship between hours
studied and the grade on the exam. And we can go ahead and write these results as
follows:
There is a significant positive relationship between the number of hours
spent studying and the grade on the exam and then I have r, and that's for
pearson's r, 11, and that's equal to the degrees of freedom, where df is equal to N
minus 2 and N is equal to 13, so 13-2 is 11.
So
r(11) equals .86 and that was the value of Pearson's r if you remember. And I put p is less
than .001. Now, alternatively, you could put p is less
than .05 if you wanted to.
Now this p-value gives us more information it is more informative than
the p-value of less than .05. But since we used an alpha of point zero
five in this test, it would be acceptable to put p is less than .05 if
we wanted to, but this does provide more information. And the reason why I said p
is less than .001, is because if you look back at our window in Excel
in our output
this value is less than .001 as it goes to four decimal places
before the one appears, but it's not less than point .0001.
In any event, in most cases when you see a p-value reported like this
it usually doesn't get smaller than less than .001; that's about
the limit where we report it in our written results.
This concludes the video on obtaining the p-value for the correlation
coefficient using the Data Analysis Toolpak in Microsoft Excel.