How to use the CORREL function
What is the CORREL function?
The CORREL function calculatesĀ the correlation between two groups of numbers.
Table of Contents
1. Introduction
What is the correlation in statistics?
Correlation in statistics refers to the relationship between two variables and how closely they vary or change together.
Correlation measures the association or comovement between two quantitative variables. The output ranges from 1 to 1. Values close to 1 or 1 indicate strong correlation, values near 0 mean weak or no correlation. Positive correlation means variables move in same direction, negative correlation means opposite directions.
Used to identify relationships and make predictions, but not determine cause and effect alone. Does not imply causation  correlation does not necessarily mean direct causality.
What is covariance?
Covariance measures how two random variables or datasets vary together by calculating the average product of their deviations from their individual means. Positive values indicating both tend to be above or below their means together.
What is regression relationships?
Regression relationships describe the correlation between two variables. One variable is modeled as a function of the other using a regression line or curve that best represents the predictive relationship between the variables' values.
2. CORREL Function Syntax
CORREL(array1, array2,ā¦)
3. CORREL Function Arguments
array1  Required. The first set of numbers. 
array2  Required. The second set of numbers. 
4. CORREL Function Example 1
A company wants to analyze the relationship between the temperature outside and their ice cream sales. The data collected is as follows:
Date  Temperature  Ice cream 
Jun 1  97  45 
Jun 2  79  24 
Jun 3  70  21 
Jun 4  87  36 
Jun 5  72  19 
Jun 6  92  39 
Jun 7  76  23 
What is the correlation between ice cream sales and the temperature?
The image above shows the calculation in cell B28:
The first argument contains the first set of numbers, in this example cell range C3:C9. The second set of numbers are in cell range D3:D9. The output in cell B28 is approx. 0.9831
The guidelines for interpreting the strength of correlation based on the value of the correlation coefficient:
0.0 to 0.3: Weak or no correlation
0.3 to 0.5: Moderate correlation
0.5 to 0.7: Strong correlation
0.7 to 1.0: Very strong correlation
The value in cell B12 shows that there is a very strong correlation between ice cream sales and temperature. The closer the value is to 1, the stronger the positive correlation.
If the correlation coefficient is positive (above zero) indicates a positive linear relationship between the two variables. This means that as one variable increases, the other variable tends to increase as well.
The chart above shows the ice cream sales on the yaxis and the temperature on the xaxis. If we evaluate the dots on the scatter plot, a clear positive linear relationship between the two variables is apparent.
A strong correlation between two variables does not necessarily mean that one variable causes the other. It simply indicates that the variables tend to move together in a linear fashion.
5. CORREL Function Example 2
A researcher wants to analyze the relationship between the amount of time students spend studying (in hours) and their final exam scores. The data collected is as follows:
Study Time (hours): 2, 4, 6, 3, 5, 7, 8, 6, 4, 9
Final Exam Scores: 65, 90, 75, 79, 70, 85, 79, 87, 76, 96
Find if there is a significant correlation between study time and final exam scores?
The image shows the calculation
in cell E19, which is used to find the correlation coefficient between two sets of data.
The first set of data is contained in the cell range B19:B28, and the second set is in the cell range C19:C28. The output in cell E19 is approximately 0.6015.
The guidelines for interpreting the strength of the correlation based on the value of the correlation coefficient are as follows:
0.0 to 0.3: Weak or no correlation
0.3 to 0.5: Moderate correlation
0.5 to 0.7: Strong correlation
0.7 to 1.0: Very strong correlation
The value in cell E19, which is approximately 0.6015, indicates a strong correlation between the two sets of data.
A positive correlation coefficient (above zero) suggests a positive linear relationship between the two variables. This means that as one variable increases, the other variable tends to increase as well.
The chart in the image displays the exam score on the yaxis and the number of hours on the xaxis. By evaluating the dots on the scatter plot, a clear positive linear relationship between the two variables is evident.
6. CORREL Function Example 3
A meteorologist wants to study the relationship between the average daily temperature (in Celsius) and the amount of rainfall (in millimeters) for a particular region. The data collected is as follows:
Avg daily temp:  Rainfall (mm) 
22  10 
18  15 
25  5 
20  20 
28  8 
23  12 
26  6 
19  18 
21  9 
20  14 
Determine the correlation between temperature and rainfall?
The image above shows the calculation in cell E19:
The first argument contains the first set of numbers, in this example cell range B19:B28. The second set of numbers are in cell range C19:C28. The output in cell E19 is approx. 0.7849
The guidelines for interpreting the strength of correlation still apply regardless of whether the correlation coefficient is positive or negative. The absolute value of the correlation coefficient determines the strength of the correlation, while the sign (positive or negative) indicates the direction of the relationship.
0.0 to 0.3: Weak or no correlation
0.3 to 0.5: Moderate correlation
0.5 to 0.7: Strong correlation
0.7 to 1.0: Very strong correlation
The value in cell E19 shows that there is a strong correlation between study time and exam score. The closer the value is to 1, the stronger the negative correlation.
If the correlation coefficient is negative (below zero) indicates a negative or inverse linear relationship between the two variables. This means that as one variable decreases, the other variable tends to decrease as well.
The chart above shows the rainfall on the yaxis and the average daily temperature on the xaxis. If we evaluate the dots on the scatter plot, a negative linear relationship between the two variables is clearly visible.
7. CORREL function not working
Text, boolean and empty cells are ignored. This can be good and bad, this is bad if you have numbers stored as text and you don't realize it.
#N/A! error is returned if the arguments don't contain the same number of values, in other words, the size of the cell ranges are not equal.
8. How is the CORREL function calculated
The CORREL function calculates the correlation between two groups of numbers. To calculate the correlation follow these steps:

 Calculate the mean of group of numbers named:
x="Temp"
y="Icecream"
For example:
Mean of X = xĢ is calculated in cell C10
Mean of Y = Č³ is calculated in cell D10  For each data point x_{i} and y_{i} calculate the deviations from the mean.
Deviation of x_{i} = x_{i}  xĢ are calculated in cells E3:E9
Deviation of y_{i} = y_{i}  Č³ are calculated in cells F3:F9  Multiply the deviations between each data point pair to get their products.
For each pair: (x_{i}  xĢ) * (y_{i}  Č³) are calculated in cells G3:G9  Sum all the deviation products.
S = Ī£(x_{i}  xĢ)(y_{i}  Č³) calculated in cell G10  Calculate the sum of squared deviations of each dataset.
SS_{x} = Ī£(x_{i}  xĢ)^{2} calculated in cell H10
SS_{y} = Ī£(y_{i}  Č³)^{2} calculated in cell I10  The correlation is the sum of products divided by the square roots of the sum of squares.
Correlation = S / ā(SS_{x} * SS_{y}) calculated in cell I13
 Calculate the mean of group of numbers named:
These steps calculates the correlation coefficient and the same number is shown in cell B14 which is the output from the CORREL function based on cells C3:C9 and D3:D9.
Functions in 'Statistical' category
The CORREL function function is one of 74 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form