What is the CORREL function?

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

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.

CORREL Function Syntax

CORREL(array1, array2,…)

CORREL Function Arguments

array1	Required. The first set of numbers.
array2	Required. The second set of numbers.

CORREL function example

Formula in cell B12:

The value in cell B12 shows that there is a strong correlation between ice cream sales and temperature.

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.

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

Excel function categories

Excel categories

Leave a Comment