A to Z of Excel Functions: The CORREL Function

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:

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.