The CORREL function returns the correlation coefficient of two cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location's average temperature and the use of air conditioners.
The CORREL function syntax has the following arguments:
array1 Required. A range of cell values.
array2 Required. A second range of cell values.
If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with zero values are included.
If array1 and array2 have a different number of data points, CORREL returns a #N/A error.
If either array1 or array2 is empty, or if s (the standard deviation) of their values equals zero, CORREL returns a #DIV/0! error.
As much as the correlation coefficient is closer to +1 or -1, it indicates positive (+1) or negative (-1) correlation between the arrays. Positive correlation means that if the values in one array are increasing, the values in the other array increase as well. A correlation coefficient that is closer to 0, indicates no or weak correlation.
The equation for the correlation coefficient is:
are the sample means AVERAGE(array1) and AVERAGE(array2).
The following example returns the correlation coefficient of the two data sets in columns A and B.