What it does – Find the correlation between two variables.
Syntax=CORREL( data_x, data_y)data_x – The range of independent data.data_y – The range of dependent data.
Sample Usage=CORREL(A3:A9, B3:B9)
//Gives the correlation coefficient of the independent variables in A3:A9 and the dependent variables in the range B3:B9.
Note: Text inputs for the arguments i.e., values of data_x and data_y, will be ignored as the formula works strictly on numbers.
Get the sample template with formulas here.
This article will show you how to use the CORREL function in Google Sheets to find the correlation between two variables. We will do this by determining the correlation coefficient, r, all from our spreadsheet.
But before all that…
- What is correlation?
- CORREL function in Google Sheets
- Ways of finding the CORREL function
- Find correlation coefficient with CORREL function
What exactly is correlation?
Correlation refers to the linear relationship between multiple random variables. The correlation coefficient, also known as the Pearson product member, is a statistical value representing the correlation between two variables.
It is essentially a way of determining if a connection exists between an independent and a dependent variable.
Correlation is important because we are often faced with critical situations where we want to know the possibility of an event X depending on event Y. If the outcome is favourable we might want to find out how to control or leverage the relationship for our benefit.
Other times we may just want to know if there’s a connection between, say, the number of hours spent sleeping and weight gain.
What is the CORREL function in Google Sheets?
The CORREL function calculates correlation in Google Sheets, it finds the Pearson’s product-moment correlation coefficient, r, of two given random sets of variables. It determines how positively or negatively correlated the independent and dependent variables we provide are.
Just like in normal correlation calculations, the correlation in Google sheets returns values within the range of -1 to 1.
- When the value is close to -1 that indicates a strongly negative correlation
- When the value is close to 1 that indicates a strong positive correlation.
Ways to find the CORREL function in Google Sheets
There are multiple ways to find the function.
- From the formula tab, go to Formula ➡ all formulas ➡ scroll down to find the CORREL function.
- From any cell, simply begin to type in =CORREL and Google sheets will auto-suggest it.
How to use the CORREL function in Google Sheets to find correlation coefficient
To demonstrate the usage of the CORREL function we will make use of data from an ad agency.
An ad agency wants to know if their successes in their recent ad campaigns are possibly tied to the amount of money they invested in them. To do this, we are going to determine if there’s a strong correlation between money spent and new customers.
Step 1: Setup the data
To begin our task, we need to first set up our data representing the details of the ad campaign in a sheet. We will be filling the first column with money spent and the second column will be for new customers who joined the company’s audience due to the corresponding ads.
Tip: You can format the data in a column to display its values in currency format by clicking on Cells ➡ Format ➡ Display, then choosing currency as display type.
Step 2: Applying the CORREL function in Google Sheets
- We go to the cell we want our correlation value displayed and begin to type =CORREL
- Then choose the CORREL function from the auto-suggest.
Now we need to provide the formula with an independent data range for parameter data_x and a second range for data_y.
- Select cells A3:A6 for data_x
- Select cells B3:A6 for data_y
Hit enter.
As you can see, the value of the correlation coefficient indicates that there is a strong positive correlation between the money our ad agency spent and the number of new customers they got. I bet no one saw that coming. Or did we?
Note: As you must have guessed, a strongly positive correlation doesn’t always mean that there’s any true relationship between two sets of events. Things could happen totally independently of each other but when plotted on a graph would seem to have a strong correlation. Correlation does not mean causation. You can read more on that here.
Conclusion
Using the CORREL function in Google sheets we can determine the relationship between two events (data sets), whether this is needed for your business research or you just want to know if eating bread makes you look older, you can find the correlation in Google sheets, provided you have the data.
See also
There are several other useful tutorials and guide on Google Sheets and tips and tricks therein on our blog. You can find them all here.
Here are some related articles you may find useful:
https://blog.tryamigo.com/how-to-find-p-value-in-google-sheets-template/
https://blog.tryamigo.com/how-to-calculate-cagr-in-google-sheets/
https://blog.tryamigo.com/calculate-weighted-average-in-google-sheets/