CORREL function is categorized as statistical functions in Excel. The CORREL formula in Excel is used to find out the correlation coefficient between two variables. It returns the correlation coefficient of the array1 and array2.
Syntax:= CORREL(array1, array2)
The CORREL function syntax has the following arguments:
- Array1 (required argument) – This is the set of independent variables. It is a cell range of values.
- Array2 (required argument) – This is the set of dependent variables. It is the second cell range of values.
Example: Let’s look at some Excel CORREL function examples and explore how to use the CORREL function as a worksheet function in Microsoft Excel:
Suppose we are given data about the weekly returns of stock A and percentage of change in a market index (S&P 500):
Syntax: =CORREL(B2:B26,C2:C26)
Result: 0.873394755
This result in the value of 0.873394755, which indicates a strong positive correlation between the two sets of values.
When we plot x and y we get the plot as shown below:
Note:
- 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:
where
are the sample means AVERAGE(array1) and AVERAGE(array2).
