What Is CORREL Function in Google Sheets?
The CORREL function in Google Sheets can calculate the correlation coefficient between two sets of variables. This coefficient measures the strength of a linear relationship between the variables. The values range from -1 (perfect negative correlation) to 1 (perfect positive correlation). A result close to 0 indicates no linear correlation.
For example, we have the stocks of two companies, Google and Tesla, in two separate columns in a sheet. We use the following formula =CORREL(B2:B6, D2:D6) to see if their prices move together. If the result is -0.04, it means they have a slightly negative correlation, towards zero—showing very little correlation. This is useful in financial analysis, portfolio diversification, and identifying trends.
Key Takeaways
- The CORREL function measures the relationship between two sets of numbers, showing how closely they move together either positively or negatively.
- Its formula is as follows: =CORREL(range1, range2), for example =CORREL(A2:A10, B2:B10)
- It is used to find out if two datasets are positively, negatively, or not related at all
- The result is a value between -1 and 1, where 1 means strong positive, -1 means strong negative, and 0 means there is no correlation.
Syntax
The CORREL function helps you find the relationship between two sets of numbers. The Google Sheets CORREL function syntax is as follows
=CORREL(data_y, data_x)
Arguments:
data_y: The range or array of dependent values
data_x: The range or array of independent values
Values and their meaning:
- 1 = perfect positive correlation
- -1 = perfect negative correlation
- 0 = no correlation
How To Use CORREL Function in Google Sheets?
Let us look at the steps involved in creating a correlation matrix in Google Sheets with a simple example.
Correlation Using Brackets
You can use the CORREL function by entering two arrays of values directly in the formula.
Step 1: Type this formula directly in a cell where you want the result.
=CORREL({23,25,28,30,35, 32}, {2000,2200,2500,2700,3200, 1800})
Step 2: Press Enter.
The result shows the correlation coefficient between the two data sets. Here, we have compared two sets of variables to find their relationship.
Note: We use this method when you’re working with small datasets.
Correlation Using Range References
Let us look at this with a simple example. Look at the dataset below.
Step 1: Enter the dataset in columns A to D.
To calculate correlation between Age and Income, use the following formula:
=CORREL(B2:B6, D2:D6)
Step 3: We can also find the correlation between Education and Income:
=CORREL(C2:C6, D2:D6). Press Enter.
You’ll see values between -1 and 1.
Interpretation
A result like 0.77 for education vs income means positive correlation.
A result close to 0.97 means strong positive correlation between age and income.
Examples
Here are a few practical examples that demonstrate the working of the CORREL function in Google Sheets. These examples help visualize the relationships between variables like age, education, and income can be measured.
Example #1 – Positive Correlation
The correlation matrix below can be used to find a correlation between variables in the example below. The table below shows a person’s number of hours of work and lunch break along with his performance rating on a scale of 10 given by his manager.
Step 1: Find the correlation between these variables. Construct the correlation matrix below this table.
Step 2: Now, we use the CORREL function to find the relationships between the variables. Let us first complete the diagonal formulas.
Step 3: Now, let us find the other correlations between other sets of data using CORREL.
Analysis: Almost all variables have a positive correlation, and the matrix has a negative value for two of the variables.
- As work hours increases, the performance rating increases.
- As the lunch break increase, the performance rating decreases slightly.
Example #2 – Negative Correlation
Let’s demonstrate a negative correlation scenario at a gym dealing with an increase in exercise hours that leads to decrease in weight. Let us look at how to use the CORREL function in this scenario.
Step 1: Enter the data in Google Sheets as shown below.
Step 2: Let us apply the CORREL Function
Select a blank cell and enter the following formula:
=CORREL(B2:B8, C2:C8)
Step 3: Observe the result. It will be a negative value, approximately -0.77, showing a strong negative correlation.
It means that we can safely conclude that as the exercise hours increase, the weight decreases and there is a consistent inverse relationship between the two variables.
This example shows us how the CORREL function can quantify negative relationships in real-world data. We can also add a scatter plot to this to visualize the inverse relationship.
Step 4: First, highlight the data from B1:C8, including the headers.
Go to the menu bar. Click Insert → choose Chart.
Step 5: In the Chart Editor on the right, under “Chart type”, we select Scatter chart. The x-axis must be the “Exercise Hours/Week” and y-axis must be the “Weight.” You can observe the downward trend using the trendline or the negative relationship. As the time spent in the gym increases, the weight of the person definitely decreases!
Example #3 – No Correlation
In this example, we look at the entertainment expenses for a family and also their monthly grocery spending. Our dataset consists of the data for six months. There can be no significant linear relationship between these two variables as the entertainment expenses will not be dependent on the grocery purchase. We can verify this understanding by using the CORREL function.
Step 1: Enter all the details in a spreadsheet as seen below. We have the month name in Column A, the grocery expenses in Column B and the entertainment expenses in Column C.
Step 2: Use the CORREL formula in an empty cell as shown below:
=CORREL(B2:B7,C2:C7). Press Enter.
In this case, the correlation coefficient is expected to be close to 0. As seen above it is almost zero, -0.001. Thus, there is no known linear relationship between the entertainment expenses and the money spent on groceries! Thus, the zero correlation between the variables can be obtained from the CORREL function.
Important Things To Note
- You can use named ranges to make the formulas easier to understand especially when finding the relationship between multiple variables.
- Both the ranges, that of the dependent and independent variables, should be of the same length.
- The result of the CORREL function in Google Sheets is a number between -1 and 1.
- Here, a correlation value of 1 indicates a positive correlation, -1 indicates a negative correlation, and 0 indicates that there is no correlation.
- If the two data sets give a strong correlation, there is no evidence that changes in one variable will cause changes in the other.
Frequently Asked Questions (FAQs)
The following are the steps to use the CORREL function.
1. We enter the data to be compared in two columns with numeric values. For instance, the Age can be entered in Column A and the income in Column B.
2. Select a blank cell where you wish the result to appear. Type the formula as we can see below in an empty cell. =CORREL(range1, range2)
3. You should use the correct cell ranges, and they should be of the same length.
4. Press Enter to and you will get the correlation coefficient.
Let us look at how to interpret correlation coefficient. The correlation coefficient is a value of between −1 and 1.
1. When the correlation coefficient is 1, it indicates that the two variables are in a perfect linear relationship. Here, if x increases, y will also increase.
2. When the correlation coefficient is 0, it suggests that there is no correlation between the variables.
3. A value of −1 means that the variables are in a perfectly correlated negative relationship.
Here are some uses of the CORREL function in Google Sheets:
1. It is used to find whether two variables move together positively or negatively. For example, age vs. income, study hours vs exam scores and so on.
2. It is used to analyze stock returns to assess portfolio diversification and risk in financial analysis.
3. It is used to explore trends like marketing spend vs sales revenue to make meaningful decisions.
4. It is also used in scientific research to check for experimental variables, like dosage vs. recovery time.
5. It is used in data validation.
Download Template
This article must help understand CORREL Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is CORREL Function in Google Sheets. We learn how to use it to find the correlation coefficient between two sets of variables. You can learn more from the following articles. –
Leave a Reply