## What Is CORREL Function In Excel?

The

CORREL function in Exceldetermines the correlation coefficient of two datasets or variables, where the coefficient value lies between -1 and 1. The function helps to evaluate the relationship between two variables, such as height and weight, price and sales, a specific stock and market index, etc.

The **Excel** **CORREL function **is an inbuilt **Statistical** function, so we can insert the formula from the “**Function Library**” or enter it directly in the worksheet.

For example, using the **CORREL function in Excel,** we will find the correlation between the two variables, i.e., the height and weight values given in the following table.

Select cell **B13**, enter the formula =CORREL(A2:A11,B2:B11), and press “**Enter**”.

The result is **0.686647098, **as shown below. A positive correlation coefficient implies that the two given datasets are proportionate, i.e., the more the height, the higher the weight.

##### Table of contents

###### Key Takeaways

- The
**CORREL function**computes the correlation coefficient of the two specified arrays, two cell ranges representing different variables. - The correlation coefficient can be a value from
**-1**to**1**.- If the output is greater than
**0**, the correlation between the two given datasets is**Positive**. - If the coefficient value is less than
**0**, the correlation between the two given datasets is**Negative**.

- If the output is greater than
- If the coefficient value is exactly
**1**or**-1,**it indicates that the correlation between the two given arrays is perfectly positive or negative.

### CORREL() Excel Formula

The syntax of the** Excel CORREL Formula **is,

The arguments of the** Excel CORREL formula **are,

**array1**: The first dataset or cell range of the first variable. It is a mandatory argument.**array2**: The second dataset or cell range of the second variable to find the correlation with the first variable. It is a mandatory argument.

### How To Use CORREL Excel Function?

We can use the **CORREL Excel Function **in 2 ways, namely,

**Access from the Excel ribbon.****Enter in the worksheet manually.**

#### Method #1 – Access from the Excel ribbon

Choose an empty cell for the output → select the “**Formulas**” tab → go to the “**Functions Library**” group → click the “**More Functions**” option drop-down → click the “**Statistical**” option right-arrow → select the “**CORREL**” function, as shown below.

The “**Function Arguments**” window opens. Enter the arguments in the “**Array1**” and the “**Array2**” fields → click “**OK**”, as shown below.

#### Method #2 – Enter in the worksheet manually

The steps to enter the **CORREL excel function** are as follows:

- Select an empty cell for the output.
- Type
**=CORREL(**in the selected cell. [Alternatively, type**=C**or**=COR**and double-click the**CORREL**function from the Excel suggestions.] - Enter the arguments as cell values or cell references in excel.
- Close the brackets and press
**Enter**to get the result.

Let us understand this function using a basic example.

We will find the correlation between the two variables using the **CORREL function.**

The table below contains two datasets.

The steps to find the correlation **using** **CORREL excel function** is,

**Select cell E1, and enter the formula =CORREL(A2:A11, i.e., A2:A11 is the first variable’s cell range.****In continuation with the formula, select B2:B11, i.e., the second variable’s cell range, and close the bracket. The complete formula is =CORREL(A2:A11,B2:B11).****Press “Enter”. We will get the result as 1, as shown below**

[Alternatively, we can apply the**CORREL function**using the option in the**Formulas**tab. First, select cell**E1**and go to**Formulas**→**More Functions**→**Statistical**→**CORREL**to open the**Function Arguments**window.

We must enter the**CORREL excel function**argument values in the**Function Arguments**window.

And once we click**OK**in the**Function Arguments**window, the**CORREL()**will get executed in the target cell.

[**Output Observation:**The**CORREL function**output in the above example is**1**, interpreting that the correlation between the two datasets is perfect positive. In other words, the two perfectly correlated datasets vary linearly, and all the data points lie in a straight line.]

### Examples

We will understand some advanced scenarios using the **CORREL function in Excel examples**.

#### Example #1

The mathematical representation of the **CORREL function** is:

The **x _{mean}** and

**y**are the means of

_{mean}**X**and

**Y**datasets (

**array1**and

**array2**).

This example shows how the above mathematical expression works and results in the same correlation coefficient as the **CORREL excel function**.

The first table contains age-height values. While the age values form the **x** dataset (**array1**), the weight values form the **y** dataset (**array2**).

We will calculate the correlation coefficient for the age-weight data using the **CORREL()** mathematical expression in cell **B11** and the **CORREL function formula **in cell **B12 **as follows:

**Step 1:**Select cell**F2**, enter the formulato determine the average age, and press*=SUM(A2:A7)/6***Enter**. We get the result as**8.5**, as shown below.

**Step 2:**Next, select cell**I2**, enter the formulato determine the average weight, and press*=SUM(B2:B7)/6***Enter**. We get the result as**56.67**, as shown below.

**Step 3:**Select cell**E4**, enter the formula, and press*=A2-$F$2***Enter**. We get the result as**-5.5**, as shown below.

**Step 4:**Drag the formula from cell**E4**to**E9**using the fill handle.

**Step 5:**Next, select cell**F4**, enter the formula, and press*=B2-$I$2***Enter**. We get the result as**-26.67**, as shown below.

**Step 6:**Then, drag the formula from cell**F4**to**F9**using the fill handle.

**Step 7:**Select cell**G4**, enter the formula, and press*=E4*F4***Enter**. We get the result as**146.67**, as shown below.

**Step 8:**Drag the formula from cell**G4**to**G9**using the fill handle.

**Step 9:**Again, select cell**H4**, enter the formula, and press*=E4*E4***Enter**. We get the result as**30.25**, as shown below.

**Step 10:**Here, drag the formula from cell**H4**to**H9**using the fill handle.

**Step 11:**Select cell**I4**, enter the formula, and press*=F4*F4***Enter**. We get the result as**711.11**, as shown below.

**Step 12:**Drag the formula from cell**I4**to**I9**using the fill handle.

**Step 13:**Finally, we will use the**CORREL()**mathematical expression as we have calculated all the required values.

Select cell **B11**, enter the formula ** =SUM(G4:G9)/SQRT(SUM(H4:H9)*SUM(I4:I9)), **and press

**Enter**.

We will get the following output.

**14:**Now, we will find the correlation using the**CORREL formula**.

Select cell **B12**, enter the formula ** =CORREL(A2:A7,B2:B7),** and press

**Enter**.

Thus, the two formulas in cells **B11** and **B12** give the same correlation coefficient, **0.95048419**.

#### Example #2

In this example, we will see how to determine the correlation coefficient using the **CORREL function** and plot the data points to check the correlation trend.

The below table shows the values of two datasets, **X** and **Y**.

The steps to find the correlation between the two variables using the **CORREL function** in excel, and to graphically represent the trend using the **Scatter plot**, are as follows:

**Step 1:**Select cell**D2**, enter the formula**=CORREL(A2:A11,B2:B11),**and press**Enter**. The result is**0.893437198,**as shown below.

**Step 2:**To create a chart, choose the cell range**A1:B11**→ select the “**Insert**” tab → go to the “**Charts**” group → click the “**Insert****Scatter (X, Y) or Bubble Chart”**option drop-down → select the “**Scatter”**chart type from the “**Scatter**” group, as shown below.

**Step 3:**Click the chart to enable the**Chart Elements**(‘**+**’ icon) and select the options shown below.

**Step 4:**Update the chart and axes titles using the respective elements in the chart area, as required.

And thus, the final **Scatter plot **is shown below. The trendline shows a positive correlation between the given **X **and **Y **datasets. It implies that as the **X **array values increase, the **Y **array values increase.

**Step 5:**Let us see the Scatter plot trendline for a negative correlation coefficient. And for that, assume the new**X**and**Y**datasets and the corresponding**CORREL function**output in cell**D2**are as follows:

Now, create the Scatter plot as explained in steps 2, 3, and 4 for the above datasets. The calculated correlation coefficient between the new datasets is negative, shown below in the trendline in the graph.

**Step 6:**Let us see the Scatter plot trendline for zero correlation coefficient. And for that, assume the new**X**and**Y**datasets and the corresponding**CORREL function**output in cell**D2**are as follows:

Now, create the Scatter plot as explained in steps 2, 3, and 4 for the above datasets.

The calculated correlation coefficient between the new datasets is **0**. And the trendline in the Scatter plot confirms the no correlation between the two datasets.

#### Example #3

This example shows how the **CORREL function** behaves when the supplied arrays are of different sizes, negative or empty.

The following table contains the ten weeks of stock N and S & P weekly change data.

We will find the correlation coefficient between the two given datasets with some scenarios as follows:

**Scenario 1**:**#N/A error**if the**array1**and**array2**sizes are unequal.

Select cell **E2, **enter the formula** =CORREL(B2:B9,C2:C11), **and press “

**Enter**”. The result is the

**#N/A**error. The reason is that the

**array1**and

**array2**sizes are unequal.

When we enter the right size array ranges, then the function executes correctly. We get the coefficient value as **0.236194374**, as shown below.

**Scenario 2**: Some cells in the above-given datasets are empty, but the two supplied array ranges are correct. The function ignores such cells to return the correlation coefficient value for the provided data points.

For example, the two datasets with empty cells are:

Select cell **E2**, enter the formula **=CORREL(B2:B11,C2:C11),** and press **Enter**. The output will be

–**0.923506323**, as shown below. The correlation between them is negative.

**Scenario 3**: If one of the datasets is empty, as depicted in the image below.

Select cell **E2**, enter the formula **=CORREL(B2:B11,C2:C11),** and press **Enter**. The result is **#DIV/0!** Error, as shown below.

Thus, when we supply two datasets of the same size, with all the data points accurate, to the **CORREL()**, it will return the correct correlation coefficient value.

### Important Things To Note

- The
**CORREL function**in excel ignores cells containing text or logical values and empty cells. But it considers cells containing zero value. - If we supply two datasets of different array sizes to the
**CORREL()**, it returns the**#N/A**error. - If any datasets are empty or the standard deviation of the given data points is
**0**, the**CORREL function**throws the**#DIV/0!**error.

### Frequently Asked Questions (FAQs)

**1. Where is the CORREL function in Excel?**

The **CORREL function in Excel** is in the **Formulas** tab. Go to **Formulas** → **More Functions** → **Statistical** → **CORREL **to apply the function in the target cell.

**2. Why is the CORREL function not working in Excel?**

The **CORREL function** may not work for the following reasons:**• **The provided arrays have different sizes.**• **If the provided arrays are empty.**• **The standard deviation of the data points in the given datasets is **0**.

**3. How do we apply the CORREL function in Excel VBA?**

We can apply the **CORREL function in Excel** VBA using the method:**Application.WorksheetFunction.Correl(array1,array2)**

For example, the table below shows the number of hours studied and marks secured out of 100.

The steps to apply the method for the **CORREL()** in Excel VBA are as follows:**• 1: **In the worksheet with the above table open, press the keys **Alt** + **F11** to access the VBA Editor.**• 2: **Pick the required **VBAProject** and select **Insert** → **Module** to open a new module, **Module1**.**• 3: **In the newly opened module window, enter the **VBA code**, shown in the image below.**Sub CORREL_fn()**

Range(“B12”) = Application.WorksheetFunction.Correl(Range(“A2:A10”), Range(“B2:B10”))**End Sub****• 4: **Click the **Run Sub/UserForm** icon to run the VBA code.**• 5: **Finally, open the active worksheet to view the result in the target cell.

The method in the above VBA code accepts the two given cell ranges as **array1 **and **array2 **and determines their correlation coefficient as **0.876071679**.

### Download Template

This article must help understand the **CORREL function**, with its formula and examples. We can download the template here to use it instantly.

### Recommended Articles

This has been a guide to CORREL Function. Here we explain how to calculate correlation using its formula, examples & a downloadable excel template. You can learn more from the following articles –

## Leave a Reply