How to use the CORREL function

What is the CORREL function?

The CORREL function calculates the correlation between two groups of numbers.

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 co-movement 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 y-axis and the temperature on the x-axis. 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 y-axis and the number of hours on the x-axis. 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 y-axis and the average daily temperature on the x-axis. 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:

1. 1. 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
   2. 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
   3. 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
   4. Sum all the deviation products.
      S = Σ(x_i - x̄)(y_i - ȳ) calculated in cell G10
   5. Calculate the sum of squared deviations of each dataset.
      SS_x = Σ(x_i - x̄)² calculated in cell H10
      SS_y = Σ(y_i - ȳ)² calculated in cell I10
   6. 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

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 73 functions in the 'Statistical' category.

Excel function categories

Excel categories

Leave a Comment