CORREL in Excel

The CORREL function is categorized as a statistical function in Excel. The CORREL formula in Excel is used to find out the correlation coefficient between two variables. For example, it returns the correlation coefficient of array1 and array2.

For example, – The correlation between a particular stock and the market index.

You can use the correlation coefficient to determine the relationship between the two properties.

CORREL Formula in Excel

The CORREL formula in Excel has two critical parameters, i.e., array1 and array2.

Compulsory Parameters:

  • array1: It is required a set of an independent variable.array2:  It is a set of the dependent variable.

Remarks

The correlation coefficient formula is:

X and Y are the sample means calculated by average(array1) and average(array2).

If the value of the correlation coefficient r is close to +1, it indicates a strong positive correlation, and if r is close to -1, it shows a strong negative correlationNegative CorrelationA negative correlation is an effective relationship between two variables in which the values of the dependent and independent variables move in opposite directions. For example, when an independent variable increases, the dependent variable decreases, and vice versa.read more.

How to use the CORREL Function in Excel?

The CORREL function in Excel is very simple and easy to use. Let us understand the working of CORREL with some examples. The CORREL function in Excel can be used as a worksheet function and as a VBA function.

CORREL function as a worksheet function.

Example #1

In the first example, let us consider two sets of data, Data1 and Data2, as shown in the below table.

=CORREL(B4:B22,C4:C22) =0.6642909

Example #2

This example considers the data set of weekly changes for stock A as “Data1” and SP 500 weekly changes as “Data 2,” shown below. Then, calculate the correlation coefficientCalculate The Correlation CoefficientCorrelation Coefficient, sometimes known as cross-correlation coefficient, is a statistical measure used to evaluate the strength of a relationship between 2 variables. Its values range from -1.0 (negative correlation) to +1.0 (positive correlation). read more function using the CORREL formula in Excel =CORREL(F3:F23,G3:G23), and the output will be 0.89011522.

Example #3

In this example, we take a perfect positive correlationPositive CorrelationPositive Correlation occurs when two variables display mirror movements, fluctuating in the same direction, and are positively related. In layman’s terms, if one variable increases by 10%, the other variable grows by 10% as well, and vice versa.read more; For example, considering a variable X value increases with the value of a variable. The value of variable X decreases as the value of variable Y decreases, as shown in the table below.

Example #4

We consider an example of a perfect negative correlation, as the value of variable X increases when the value of variable Z decreases. But, conversely, as the value of variable X decreases, the value of variable Z increases, as shown in the below example.

CORREL function in Excel can be used as a VBA functionCan Be Used As A VBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more.

Let us consider a dataset in excel that starts from A2.

Sub CORRELfunction()

Dim r As Double

Dim ra As Range

Dim rb As Range

Set ra = Range(“A2”, Range(“A2”).End(xlDown)) //ra stores the ranges from A2 to last entry in column A.

Set rb = ra.Offset(, 1) //rb stores the array values from C2 to all values  in column C.

r = Application.WorksheetFunction.correl(ra, rb)  //Correlation function value is stored in r variable.

MsgBox r //prints the output in Message Box.

End Sub

Things to Remember 

  • #N/A Error – The (CORREL) correlation function in Excel through the #N/A error if the given arrays are of different lengths. If array1 and array2 contain different numbers of data points, CORREL will return the #N/A error value.#DIV/0 error – Correlation function in Excel through the #DIV/0 error if either of the given arrays (array1, array2) are empty or if the Excel standard deviation of the values equals zero.

If the supplied array or reference argument contains text/string, logical values, or an empty value, those values are automatically ignored.

  • The (CORREL) correlation function in Excel includes the value zero in its calculation.

CORREL Excel Function Video

This article has been a guide to (CORREL) Correlation Function in Excel. We discuss the CORREL formula in Excel, using the CORREL function and Excel example, and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • Average Formula in ExcelVBA MsgBox FunctionVBA MsgBox FunctionVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read moreCorrelation Matrix in ExcelCorrelation Matrix In ExcelA correlation matrix in excel is a way of summarizing the correlation coefficient data showing the relationship between two variables of a dataset in a tabular form. The “correlation” option of the “data analysis” tab helps users create a correlation matrix.read more5 Different Types of Arrays in VBA ExcelOFFSET ExcelOFFSET ExcelThe OFFSET function in excel returns the value of a cell or a range (of adjacent cells) which is a particular number of rows and columns from the reference point. read moreFIND Formula in ExcelFIND Formula In ExcelFind function in excel finds the location of a character or a substring in a text string. In other words it finds the occurrence of a text in another text, as it gives us the position, the output returned by this function is an integer.read moreINT Formula in ExcelINT Formula In ExcelINT or integer function in excel returns the nearest integer of a given number and is used when we have many data sets and each data in a different format.read moreLN Excel FunctionLN Excel FunctionLN function calculates the logarithm of a given number which takes a single number as the argument and gives the result as the logarithm.read moreTAN in ExcelTAN In ExcelThe TAN Excel function is a trigonometric function built into Excel that is used to calculate the cosine value of a given number or, in terms of trigonometry, the cosine value of a given angle. In this case, the angle is a number in Excel, and this function accepts only one argument, which is the input number.read moreSUBTOTAL Excel FunctionSUBTOTAL Excel FunctionThe SUBTOTAL excel function performs different arithmetic operations like average, product, sum, standard deviation, variance etc., on a defined range.read moreDATE Excel FunctionDATE Excel FunctionThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers.read more