In the previous video, you learned about how correlation is calculated. In this video, explore an example of calculating a correlation in Microsoft Excel.
- [Instructor] One way to analyze the relationship between two sets of data is to calculate their correlation. In the previous movie, I provided an overview of how correlation is calculated. And in this movie, I would like to give you an example of calculating correlation in Microsoft Excel. My sample file is the SingleCorrelation workbook. And you can find it in the Chapter05 folder of your exercise files collection. To calculate correlation in Excel, you use the correl, or C-O-R-R-E-L function. I've clicked in cell D3, where I will create my correlation formula.
So I'll type an equal sign followed by C-O-R-R-E-L. Now all I need to do is to enter in my two arrays of values. So array one will be my first column of data. That's A3 through A12. I don't need to include the column header, and in fact, I shouldn't. Then I'll type a comma, and the second array is in B3 through B12. Type a right parenthesis and enter and I get a correlation value of 0.52.
So the next question is whether that correlation is significant or not. Well, I have 10 value pairs. And my correlation formula result is 0.52. So 10 and 0.52. So now I can switch over to a lookup table and see if this correlation value for a two-tailed test is significant. I have a table here which is a correlation lookup table for two-tailed tests. And remember that the correlation lookup for a two-tailed test means that it can be either high or low on either side of the value you're testing.
I had an N, or number of data pairs of 10, so I'll go down to the 10 column. And then my value was 0.52. And I'm looking for the first column and I see that the value for significance is .55. So what that means is that my value of .52 is almost significant at the 90% level, but in fact, it is not. If I'd had 15 samples, then my value of 0.52 would be significant at both the 90%, or 0.1 and 95%, .51 levels.
As you can see, the more samples you have, the lower correlation value you need for your result to be significant.
- Distinguish between the mean, median, and mode.
- Describe the relationship between variance and standard deviation.
- Identify a nondirectional hypothesis.
- Point out the difference between COVARIANCE.P and COVARIANCE.S.
- Explain correlation.
- Analyze Bayes’ rule.