# A to Z of Excel Functions: the CORREL Function

23 October 2017

*Welcome back to our regular A to Z of Excel Functions blog. Today we look at the CORREL function. *

** **

**The CORREL function**

Do we need to corral you regarding **CORREL**? If you say no, we might get into a fight concerning the OK corral… Anyway… The *correlation coefficient*, sometimes also called the *cross-correlation coefficient*, is a quantity that gives the quality of a least squares fitting to the original data. Clear as mud? OK, let me try it in English instead.

The correlation coefficient is a measure that determines the degree to which two variables' movements are associated. The range of values for the correlation coefficient is between -1 and +1. A correlation of -1 indicates what is called perfect negative correlation, whilst a correlation of +1 indicates a perfect positive correlation.

To be clear, a value of exactly +1 means there is a perfect positive relationship between the two variables. For a positive increase in one variable, there is also a proportional positive increase in the second variable. A value of exactly -1 means there is a perfect negative relationship between the two variables. This shows the variables move in opposite directions: for a positive increase in one variable, there is a proportional decrease in the second variable. If the correlation is zero, this simply means there is no relationship between the two variables. The strength of the relationship varies in degree based on the value of the correlation coefficient. For example, a value of 0.25 indicates there is a positive relationship between the two variables, but it is weak.

It should be noted that this statistic only measures the linear relationship between the variables. Nonlinear relationships between two variables cannot be captured or expressed by this coefficient.

The **CORREL** function returns the correlation coefficient of the **array1** and **array2** cell ranges. This can be used to determine the relationship between two properties (*i.e. *the correlation coefficient). For example, you can examine the relationship between a location's average temperature and the use of air conditioners.

The **CORREL **function employs the following syntax to operate:

**CORREL(array1, array2)**

The **CORREL** function has the following arguments:

**array1:**this is required. This represents a cell range of values.**array2:**this is also required and denotes a second cell range of values.

It should be further noted that:

- If an array or reference argument contains text, logical values or empty cells, those values are ignored; however, cells with the value zero are included
- If
**array1**and**array2**have a different number of data points,**CORREL**returns the*#N/A*error value - If either
**array1**or**array2**is empty, or if**s**(the standard deviation) of their values equals zero,**CORREL**returns the*#DIV/0!*error value

The equation for the correlation coefficient is:

where

are the sample means, *i.e.* **AVERAGE**(**array1**) and **AVERAGE**(**array2**).

Please see my example below:

*We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.*

*A full page of the function articles can be found here. *