Sanjida Ahmed
In Excel, the CORREL function is used to determine how closely two sets of data are associated with each other. In this article, we will show you how to use the CORREL function in Excel.
Download Workbook
You can download the free practice Excel workbook from here.
Introduction to the CORREL Function
- Description
The CORREL function is a Statistical function in Excel. It calculates the correlation coefficient of two cell ranges. For example, you can calculate the correlation between two stock markets, between height-weight measurements, between exam results of two semesters etc.
- Syntax
=CORREL(array1, array2)
- Arguments Description
| Argument | Required/ Optional | Description |
|---|---|---|
| array1 | Required | A range of cell values. |
| array2 | Required | The second range of cell values. |
- Equation
Here,
means the Average of array1 and array2 respectively.
- Return Value
The correlation coefficient – a value between -1 and +1 – of two sets of variables.
3 Methods of Using CORREL Function in Excel
In this section, we will show you the basic method of how to utilize the CORREL function in Excel. And we will also discuss the Perfect Positive and Negative Correlation between two arrays with the CORREL function.
1. Generic Example of the CORREL Function
We will show you how to implement the CORREL function with an example of calculating the correlation coefficient between Age and Weight. You can also implement these same steps to find out the correlation coefficient between stock markets, results, height-weight measurements, etc.
Steps to calculate the correlation coefficient between Age and Weight are given below.
Steps:
- Pick a cell to store the result (in our case, it is Cell C15).
- Write the CORREL function and pass the array values or cell ranges inside the parentheses.
In our case, the formula was,
=CORREL(B5:B13, C5:C13)Here,
B5:B13 = array1, first range of cells, Column Age
C5:C13 = array2, the second range of cells, Column Weight
- Press Enter.
You will get the correlation coefficient between the range of values defined in your dataset.
2. CORREL Function with Perfect Positive Correlation
Perfect Positive Correlation means a correlation coefficient of +1. In Perfect Positive Correlation, when variable X increases, variable Y increases along with it. When variable X decreases, variable Y decreases too.
Look at the following example to understand more.
Here X and Y axis, both have seen an upward trend therefore it is a Perfect Positive Correlation, produced result 1.
Read More: How to Use TREND Function in Excel (3 Examples)
3. CORREL Function with Perfect Negative Correlation
Perfect Negative Correlation means a correlation coefficient of -1. In Perfect Negative Correlation, when variable X increases, variable Y decreases and when variable X decreases variable Y increases.
Look at the following example.
Here X-axis has witnessed a steady growth while the Z-axis has experienced a downward trend, therefore it is a Perfect Negative Correlation with the result of -1.
Read More: How to Use Excel GROWTH Function (4 Easy Methods)
Similar Readings
- How to Use MODE Function in Excel (4 Examples)
- Use VAR Function in Excel (4 Examples)
- How to Use PROB Function in Excel (3 Examples)
- Use Excel STDEV Function (3 Easy Examples)
- How to Use Excel FREQUENCY Function (6 Examples)
Insert CORREL Function from Excel Command Tool
You can also insert the CORREL function from Excel’s command tool and extract the correlation coefficient between data from there.
Steps to calculate the correlation coefficient between arrays (Height Column and Weight Column) from Excel’s command tool is shown below.
Steps:
- Pick a cell to store the result (in our case, it is Cell C15).
- Next, go to Formulas -> More Functions -> Statistical -> CORREL
- In the Function Arguments pop-up box, select the Array1 by dragging through the whole 1st column or row and the Array2 by dragging through the whole 2nd column or row of your dataset.
In our case,
Array1 = B5:B13, the Height Column
Array2 = C5:C13, the Weight Column
- Press OK.
In this way too, you will get the correlation coefficient between two arrays of your dataset.
CORREL Function in VBA
The CORREL function can also be used with VBA in Excel. Steps to do that are shown below.
Steps:
- Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- In the pop-up code window, from the menu bar, click Insert -> Module.
- Copy the following code and paste it into the code window.
Sub CORRELfunction()
    Dim Z As Double
    Dim xRng As Range
    Dim yRng As Range
    Set xRng = Range("B5", Range("B5").End(xlDown)) 'xRng stores the ranges from B5 to last entry in column B
    Set yRng = xRng.Offset(, 1) 'yRng stores the array values from C5 to all values in column C
    Z = Application.WorksheetFunction.Correl(xRng, yRng) 'Correlation function value is stored in Z variable
    MsgBox Z 'prints the output in Message Box.
End SubYour code is now ready to run.
- Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.
You will get a Microsoft Excel pop-up message box showing the correlation coefficient result between two cell ranges of your dataset.
Things to Remember
- If an array or cell range contains text, logical values or blank cells, those values are ignored. However, cells with zero are counted as arguments.
- #N/A error will be returned if array1 and array2 have different numbers of data points.
- #DIV/0! error will occur if either array1 or array2 is empty, or if the Standard Deviation (S) of their values is equal to zero.
Conclusion
This article explained in detail how to use the CORREL function in Excel with examples. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
