CORREL Function in Excel (Table of Contents)
CORREL in Excel
Correl function in excel is used for calculating Correlation Coefficient whose value ranges from -1 to +1 only and it also shows how strongly any 2 values are related. It is because the range for correlation coefficient is only -1 to +1 which is quite small and the value falling under this range will be less as compared to any other number. As per the syntax, we just need to select the 2 arrays of numbers for which we need to find Correlation Coefficient.
Correlation coefficient – It’s a statistical measure, how strong a relationship between two variables i.e. For a positively correlated variable, Correlation coefficient value increases whereas for a negatively correlated variable, Correlation coefficient value decreases
Correlation coefficients are expressed as values between +1 and -1.
I have two variables (one plotted on the X axis, one on the Y axis)
- If the value is 1, then it means strong positive correlationg. In this case, y increases when x increases (Positive linear relationship)
- If the value is 0: means that there is no relationship between the two variables (x and y)
- If the value is -1 then it means negative correlation: In this case, y decreases when x increases (Negative linear relationship)
Definition
CORREL Function: Returns the correlation coefficient between two datasets or variables
CORREL Formula in Excel
Below is the CORREL Formula:
Where
Array1– It is an independent variable. It is entered as cell reference or range of values.
4.8 (3,740 ratings)
View Course
Array2– It is a dependent variable. It is entered as second cell reference or range of values.
Correlation coefficients are expressed as values between +1 and -1.
A coefficient of zero indicates, No discernable relationship between fluctuations of the variables
It is most commonly used to Calculate correlation coefficient for two sets of values or variables i.e. Correlation between a particular stock or share price and the market index value
To Calculate correlation coefficient for refrigerators & air conditioner sales in summer & winter season and Car models, its year of launch & price difference
Excel Correl function is similar to Pearson Function
CORREL function is used as worksheet function & also in excel VBA.
CORREL function in Excel is easy to use & is very simple function with few arguments
How to use CORREL Function in Excel?
CORREL Function is very simple to use. Let us now see how to use CORREL function in Excel with the help of some examples.
Example #1
For a Set of Positive Variables or Dataset
With the help of Correl function, I need to find out correlation coefficient between two datasets or variables
In the below mentioned example, the table contains two variables, one in the column X & the other in column Y. where both the datasets contain positive values.
Let’s apply Correl function in cell “C14”. Select the cell “C14” where the Correl function needs to be applied.
Click the insert function button (fx) under formula toolbar, the dialog box will appear, Type the keyword “CORREL” in the search for a function box, CORREL function will appear in select function box. Double click on CORREL function.
A dialog box appears where arguments for CORREL function needs to be filled or entered i.e. =CORREL(array1, array2)
For Array1 argument, click inside cell B8 and you’ll see the cell selected, then Select the cells till B12. So that column range will get selected i.e. B8:B12
Similarly, For Array2 argument, click inside cell C8 and you’ll see the cell selected, then Select the cells till C12. So that column range will get selected i.e. C8:C12
i.e. =CORREL(B8:B12,C8:C12) will appear in the cell C14
Click ok, after entering both the arguments.
i.e. =CORREL(B8:B12,C8:C12) returns 0.988104929 as the result. The Correlation coefficient between the two datasets or variables is 0.98
Graphical representation can also be done using line chart under chart options
I have two variables X & Y where one plotted on the X axis, the other one on the Y axis
Select the table range excluding header X & Y, i.e. B8 TO C12
Then Click on the insert tab, under that select line options, select the first one in the line chart option
It will result in chart,
Chart elements such as , legend series (X, Y) axis title (X & Y axis), chart title (POSITIVE CORRELATION) & data label (Values) needs to be updated in the chart.
You can see strong positive correlation i.e. Variables X & Y values are positively correlated (Positive linear relationship)
Example #2
For a Dataset Containing Positive & Negative Values
With the help of Correl function, I need to find out correlation coefficient between two datasets or variables
In the below mentioned example, I have two variables, one in the column x & the other in column Y. where column X datasets contain positive values & column Y datasets contain negative values
Let’s apply Correl function in cell “C29”. Select the cell “C29” where Correl function needs to be applied.
Click the insert function button (fx) under formula toolbar, dialog box will appear, Type the keyword “CORREL” in the search for a function box, CORREL function will appear in select function box. Double click on CORREL function.
A dialog box appears where arguments for CORREL function needs to be filled or entered i.e. =CORREL(array1, array2)
For Array1 argument, click inside cell B23 and you’ll see the cell selected, then Select the cells till B27. So that column range will get selected i.e. B23:B27
Similarly, For Array2 argument, click inside cell C23 and you’ll see the cell selected, then Select the cells till C27. So that column range will get selected i.e. C23:C27
i.e. =CORREL(B23:B27,C23:C27) will appear in the cell C29
Click ok, after entering both the arguments.
i.e. =CORREL(B23:B27,C23:C27) returns -0.988104929 as the result. The Correlation coefficient between two datasets or variables is -0.98
Similar to the above example, it is graphically represented using line chart under chart options
I have two variables X & Y where one plotted on the X axis, the other one on the Y axis
You can see the negative correlation i.e. Variables X & Y values are negatively correlated (Negative linear relationship). In this case, y decreases when x increases.
Things to Remember
- Suppose if Array1 and Array2 have a different number of data points or if the supplied arrays are of different lengths. CORREL results in or returns the #N/A error value.
- The function will return the #DIV/0! error value, if the argument array1 or array2 contains non-numeric data (text, logical values, or blank cells).
- The other method of calculating the correlation coefficient through Data analysis option. In the excel toolbar, data analysis tab is selected under data ribbon, once we click on data analysis, Data analysis popup will appear, we have to select correlation in that dropdown of analysis tools to calculate the correlation coefficient for a given datasets or variables
- It’s an inbuild Analysis Toolpak Add-in which is present in application add-ins.
Recommended Articles
This has been a guide to Excel CORREL function. Here we discuss the CORREL Formula and how to use CORREL function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
