In the previous video, you learned how to create a formula to calculate correlation between two sets of data. In this video, learn about a quick way to set up a grid, so that you can analyze the correlation between multiple columns of data.
- [Instructor] In the previous movie, I showed you how to create a formula to calculate correlation between a single pair of data sets. In this movie, I'd like to show you a quick way to set up a grid so you can analyze the correlation between multiple pairs of data columns. My sample file is the multiple correlations workbook and you can find it in the chapter five folder of your exercise files collection. This workbook contains a set of four data columns, each of which contains 10 values. And what I'd like to do is find a correlation between each pair of columns.
So between column one and column two, column one and column three, and so on for all possible pairs. I'll create my formulas in G5 through J8. I'll start by calculating the correlation between column one and itself. And yes, the result is going to be one because any dataset is perfectly correlated with itself. By creating the formula properly, we'll be able to copy the formula to other cells and fill out the grid quickly.
So in cell G5, I'll type an equals sign. And then the function is correl, C-O-R-R-E-L. Now I need to identify my first array of values and that will be A2 through A11. That's column one. I don't want this reference to change when the formula is copied to the right. So I will click between the A and the two in the first reference and press F4 to make it an absolute or unchanging reference. I'll do the same thing for A11.
F4. Now I need to identify my second array and this will change. So I'll type a comma and I'll select A2 through A11 again. Right parentheses and enter. And I have my correlation formula and as expected the result is one. Now I need to create formulas for column two, column three, and column four, and I'll work down in column G. One easy way to do this is to double click cell G5 and then on the formula bar, copy the formula by selecting it and then pressing control C.
Now I'll press escape to stop editing the current cell and double click cell G6. Now I press control V and you have pasted in the formula text. But there's still one edit to do, there are actually two. We want to look at column two versus column one so we need to change the reference for the first column from A2 to A11 to B2 to B11. So all we need to do is change these As to Bs.
And press enter, and there's the correlation. Now double click in cell G7, control v. We're looking at column three that's in column C. So the unchanging or absolute references will be C2 to C11. Enter, there we go. And then double click cell G8, control v, and we're looking at column four which is in worksheet column D. So change A2 to A11 to D2 to D11, and enter, and there we have that correlation.
Now we can copy these formulas to the right and they will pick up column one versus column two. I'll refresh first and they will. And the copied formulas will move over correctly and create correlation calculations for each of the pairs of columns. So I'll select cells G5 through G8. Move my mouse pointer over the fill handle which is at the bottom right corner of the selection. I know I'm in the right place when my mouse pointer changes to a black crosshair.
And I'll drag to the right. And I can immediately tell that what I did was correct because the correlations for each pair of columns where we have the same column in each one is one, and that's along the top left to bottom right or main diagonal. Also if I look at column two and column one on top, and column two and column one in the left hand column of my grid, I can see that we have the same values.
So regardless of which pair of datasets or columns you want to look up, you have a correlation calculation that you can compare for significance.
- 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.