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

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.

Channel: Quantitative Specialists

Views: 909,708

Rating: 4.9142799 out of 5

Keywords: Microsoft Excel (Software), Correlation, Microsoft Excel, Data Analysis Toolpak, Correlation in Excel, Pearson Correlation, Linear Relationship, Pearson r, Pearson’s r, Pearson’s r in Excel, p-value in Excel, statistics in Excel, Inferential Statistics, Introduction to Statistics, Introductory Statistics, Quantitative Specialists, statisticsinstructor, hypothesis testing, Statistics (Field Of Study), Correlation And Dependence (Literature Subject), P-value

Id: vFcxExzLfZI

Length: 5min 15sec (315 seconds)

Published: Mon Sep 15 2014