In Google Sheets, the correlation coefficient measures the strength and direction of a linear relationship between two variables. It's a statistical value that ranges from -1 to 1, where:
Google Sheets uses the CORREL function to calculate the correlation coefficient. The syntax for this function is:
CORREL(range1, range2)
Where:
These ranges must be of equal size and contain numeric values. The CORREL function then calculates the Pearson correlation coefficient between these two ranges which helps you understand the linear relationship between them. This can be useful for various data analysis tasks, such as understanding how two variables move with respect to each other in financial, scientific, and other types of data.
Follow the steps below to calculate correlation coefficient in Google Sheets,
First, input your data into two columns. Let's use an example where column A represents the hours studied per week (Variable X) and column B represents test scores (Variable Y) for a group of students. Each row corresponds to a different student's data.
Find an empty cell where you want the correlation coefficient result to appear. This is where Google Sheets will display the calculation.
In the selected cell, type =CORREL( to initiate the correlation coefficient calculation. This tells Google Sheets you're going to calculate the correlation coefficient.
Continue your formula by specifying the data ranges for your two variables. If your data for hours studied is in cells A2 through A11, and your test scores are in cells B2 through B11, your formula should look like =CORREL(A2:A11, B2:B11). Make sure these ranges accurately reflect where your data is in the sheet.
Close the parentheses and press Enter. Google Sheets will now calculate the correlation coefficient for the data you specified and display the result in your selected cell.
Look at the result to understand the relationship between your variables. A value near 1 suggests a strong positive relationship (as hours studied increases, so do test scores), a value near -1 indicates a strong negative relationship, and a value around 0 suggests no linear relationship.
We hope that you now have a better understanding of how to find correlation coefficients in Google Sheets. If you enjoyed this article, you might also like our article on how to add a secondary axis in Google Sheets or our article on MINIFs in Google Sheets.
