Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
CORREL Function in Excel (Table of Contents)
CORREL in Excel
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. CORREL in Excel returns the correlation coefficient of the array1 and array2.
You can use the correlation coefficient to determine the relationship between two properties.
For example – The correlation between a particular stock and the market index.
CORREL Formula in Excel
CORREL Formula in Excel has two compulsory parameters i.e. array1, array2.
- array1: It is required a set of independent variable.
- array2: It is a set of dependent variable.
The correlation coefficient formula is:
where and are the sample means and 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 correlation.
How to use CORREL Function in Excel?
CORREL function in Excel is very simple and easy to use. Let understand the working of CORREL in Excel by some examples. CORREL function in Excel can be used as worksheet function and as VBA function.
CORREL function as worksheet function.
CORREL in Excel Example #1
In the first example, let’s consider two sets of data Data1 and Data2 as shown in the below table.
CORREL in Excel Example #2
In this example, we consider the data set of weekly changes for a stock A as data1 and SP 500 weekly changes as data 2 show in the below table. Now calculate the correlation coefficient function using CORREL formula in excel, =CORREL(F3:F23,G3:G23) and output will be- 0.89011522.
CORREL in Excel Example #3
In this example we are taking a perfect positive correlation, for example, considering a variable X value increases with the value of variable and value of variable X decreases with the value of variable Y decreases as shown in the below table.
CORREL in Excel Example #4
Here we consider an example of a perfect negative correlation. As the value of variable X increases when the value of variable Z decreases and 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 function.
Let’s consider a dataset in excel starts from A2.
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.
Things to Remember About the (CORREL) Correlation Function in Excel
- #N/A error – (CORREL) Correlation function in Excel through the #N/A error if the given arrays are of different lengths. Means, 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 standard deviation of the values equals zero.
If the supplied array or reference argument contains text/string, logical values, or empty value, then those values are ignored automatically.
- (CORREL) Correlation function in Excel includes the value zero in its calculation.
You can download this (CORREL) Correlation Function in Excel template here – CORREL Function Excel Template
This has been a guide to (CORREL) Correlation Function in Excel. Here we discuss the CORREL Formula in excel and how to use CORREL function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –