How to Calculate a Correlation (and P-Value) in Microsoft Excel - YouTube

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.