Microsoft Excel CORREL Function
Part 1: Introduction
Definition
The CORREL function in Microsoft Excel is a statistical function that returns the correlation coefficient of two cell ranges.
Purpose
The CORREL function is used to determine the relationship between two properties. For example, you can examine the relationship between a location’s average temperature and the use of air conditioners.
Syntax & Arguments
The syntax for the CORREL function is as follows:
CORREL(array1, array2) Arguments:
- array1: This is a required argument. It refers to the first range of cell values.
- array2: This is also a required argument. It refers to the second range of cell values.
Return Value
The CORREL function returns the correlation coefficient of the two cell ranges. The correlation coefficient is a statistical measure that calculates the strength of the relationship between the relative movements of the two variables. The range of values for the correlation coefficient is -1 to 1.
Remarks
- If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with zero values are included.
- If array1 and array2 have a different number of data points, CORREL returns a #N/A error.
- If either array1 or array2 is empty, or if the standard deviation of their values equals zero, CORREL returns a #DIV/0! Error.
- As much as the correlation coefficient is closer to +1 or -1, it indicates a positive (+1) or negative (-1) correlation between the arrays. A positive correlation means that if the values in one array increase, the values in the other also increase. A correlation coefficient that is closer to 0 indicates no or weak correlation.
Part 2: Examples
Let’s look at examples of using the CORREL function in a business context.
Example 1
Purpose of Example: To find the correlation between the number of units sold and the total sales revenue.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Units Sold | Revenue |
| 2 | 100 | 1000 |
| 3 | 200 | 2000 |
| 4 | 150 | 1500 |
| 5 | Formula | Result |
| 6 | =CORREL(A2:A4, B2:B4) | 1 |
Explanation: In this example, we list units sold and the corresponding revenue for three periods. We use the CORREL function to find the correlation between the number of units sold and the total sales revenue. The formula =CORREL(A2:A4, B2:B4) returns 1, which indicates a perfect positive correlation. This means that as the number of units sold increases, the total sales revenue also increases.
Example 2
Purpose of Example: To find the correlation between the number of employees and the total sales revenue.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Employees | Revenue |
| 2 | 10 | 1000 |
| 3 | 20 | 1500 |
| 4 | 15 | 1200 |
| 5 | Formula | Result |
| 6 | =CORREL(A2:A4, B2:B4) | 0.98198 |
Explanation: In this example, we list the number of employees and the corresponding revenue for three periods. We use the CORREL function to find the correlation between the number of employees and the total sales revenue. The formula =CORREL(A2:A4, B2:B4) returns 0.98198, which indicates a strong positive correlation. This means that the incremental sales revenue increases as the number of employees increases.
Example 3
Purpose of Example: To find the correlation between the number of hours worked and the total sales revenue.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Hours Worked | Revenue |
| 2 | 40 | 1000 |
| 3 | 50 | 1200 |
| 4 | 45 | 1100 |
| 5 | Formula | Result |
| 6 | =CORREL(A2:A4, B2:B4) | 1 |
Explanation: In this example, we list the hours worked and the corresponding revenue for three periods. We use the CORREL function to find the correlation between the number of hours worked and the total sales revenue. The formula =CORREL(A2:A4, B2:B4) returns 1, which indicates a perfect positive correlation. This means that as the number of hours worked increases, the total sales revenue also increases.
Example 4
Purpose of Example: To find the correlation between the number of products sold and the total sales revenue.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Products Sold | Revenue |
| 2 | 10 | 1000 |
| 3 | 20 | 2000 |
| 4 | 15 | 1500 |
| 5 | Formula | Result |
| 6 | =CORREL(A2:A4, B2:B4) | 1 |
Explanation: In this example, we list the number of products sold and the corresponding revenue for three periods. We use the CORREL function to find the correlation between the number of products sold and the total sales revenue. The formula =CORREL(A2:A4, B2:B4) returns 1, which indicates a perfect positive correlation. This means that as the number of products sold increases, the total sales revenue also increases.
Example 5
Purpose of Example: To find the correlation between the number of customers and the total sales revenue.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Customers | Revenue |
| 2 | 100 | 1000 |
| 3 | 200 | 2000 |
| 4 | 150 | 1500 |
| 5 | Formula | Result |
| 6 | =CORREL(A2:A4, B2:B4) | 1 |
Explanation: In this example, we list the number of customers and the corresponding revenue for three periods. We use the CORREL function to find the correlation between the number of customers and the total sales revenue. The formula =CORREL(A2:A4, B2:B4) returns 1, which indicates a perfect positive correlation. This means that incremental sales revenue increases as the number of customers increases.
Example 6: CORREL with IF
Purpose of Example: To find the correlation between the number of units sold and the total sales revenue, only considering sales above $1000.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Units Sold | Revenue |
| 2 | 100 | 1000 |
| 3 | 200 | 2000 |
| 4 | 150 | 800 |
| 5 | Formula | Result |
| 6 | =CORREL(IF(B2:B4>1000, A2:A4), IF(B2:B4>1000, B2:B4)) | 1 |
Explanation: In this example, we want to find the correlation between the number of units sold and the total sales revenue, but only for sales above $1000. We use the CORREL function nested with an IF function. The IF function creates arrays of units sold and revenues only for sales above $1000, and the CORREL function calculates the correlation between these arrays. The formula =CORREL(IF(B2:B4>1000, A2:A4), IF(B2:B4>1000, B2:B4)) returns 1, which indicates a perfect positive correlation.
Example 7: CORREL with SUM
Purpose of Example: To find the correlation between the total units sold and the total sales revenue.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Units Sold | Revenue |
| 2 | January | 100 | 1000 |
| 3 | February | 200 | 2000 |
| 4 | March | 150 | 1500 |
| 5 | Formula | Result | |
| 6 | =CORREL(SUM(A2:A4), SUM(B2:B4)) | 1 |
Explanation: In this example, we want to find the correlation between the total units sold and the total sales revenue. We use the CORREL function nested with the SUM function. The SUM function calculates the total units sold and sales revenue, and the CORREL function calculates the correlation between these totals. The formula =CORREL(SUM(A2:A4), SUM(B2:B4)) returns 1, which indicates a perfect positive correlation.
Example 8: CORREL with VLOOKUP
Purpose of Example: To find the correlation between the number of units sold and the total sales revenue for a specific month.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Units Sold | Revenue |
| 2 | January | 100 | 1000 |
| 3 | February | 200 | 2000 |
| 4 | March | 150 | 1500 |
| 5 | Formula | Result | |
| 6 | =CORREL(VLOOKUP(“February”, A2:C4, 2, FALSE), VLOOKUP(“February”, A2:C4, 3, FALSE)) | #N/A |
Explanation: In this example, we want to find the correlation between the number of units sold and the total sales revenue for February. We use the CORREL function nested with the VLOOKUP function. The VLOOKUP function returns the units sold, the payment for February, and the CORREL function attempts to calculate the correlation between these values. However, the CORREL function returns a #N/A error since only one pair of steals exists. The correlation coefficient cannot be calculated from a single couple of values.
Example 9: CORREL with COUNTIF
Purpose of Example: To find the correlation between the number of units sold and the total sales revenue, only considering months with sales above average.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Units Sold | Revenue |
| 2 | January | 100 | 1000 |
| 3 | February | 200 | 2000 |
| 4 | March | 150 | 1500 |
| 5 | Formula | Result | |
| 6 | =CORREL(IF(B2:B4>AVERAGE(B2:B4), A2:A4), IF(C2:C4>AVERAGE(C2:C4), C2:C4)) | 1 |
Explanation: In this example, we want to find the correlation between the number of units sold and the total sales revenue, but only for months with sales above average. We use the CORREL function nested with an IF function. The IF function creates arrays of units sold and revenues only for months with sales above the average sales, and the CORREL function calculates the correlation between these arrays. The formula =CORREL(IF(B2:B4>AVERAGE(B2:B4), A2:A4), IF(C2:C4>AVERAGE(C2:C4), C2:C4)) returns 1, which indicates a perfect positive correlation.
Example 10: CORREL with AVERAGE
Purpose of Example: To find the correlation between the average number of units sold and the average sales revenue.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Units Sold | Revenue |
| 2 | January | 100 | 1000 |
| 3 | February | 200 | 2000 |
| 4 | March | 150 | 1500 |
| 5 | Formula | Result | |
| 6 | =CORREL(AVERAGE(A2:A4), AVERAGE(B2:B4)) | #N/A |
Explanation: In this example, we want to find the correlation between the average number of units sold and the average sales revenue. We use the CORREL function nested with the AVERAGE function. The AVERAGE function calculates the average number of units sold and the average sales revenue, and the CORREL function attempts to calculate the correlation between these averages. However, the CORREL function returns a #N/A error since there is only one pair of values. The correlation coefficient cannot be calculated from a single couple of deals.
Example 11: CORREL with MAX
Purpose of Example: To find the correlation between the maximum number of units sold and the maximum sales revenue.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Units Sold | Revenue |
| 2 | January | 100 | 1000 |
| 3 | February | 200 | 2000 |
| 4 | March | 150 | 1500 |
| 5 | Formula | Result | |
| 6 | =CORREL(MAX(A2:A4), MAX(B2:B4)) | #N/A |
Explanation: In this example, we want to find the correlation between the maximum number of units sold and the maximum sales revenue. We use the CORREL function nested with the MAX function. The MAX function calculates the maximum number of units sold and the maximum sales revenue, and the CORREL function attempts to calculate the correlation between these maximums. However, the CORREL function returns a #N/A error since there is only one pair of values. The correlation coefficient cannot be calculated from a single couple of deals.
Example 12: CORREL with MIN
Purpose of Example: To find the correlation between the minimum number of units sold and the minimum sales revenue.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Units Sold | Revenue |
| 2 | January | 100 | 1000 |
| 3 | February | 200 | 2000 |
| 4 | March | 150 | 1500 |
| 5 | Formula | Result | |
| 6 | =CORREL(MIN(A2:A4), MIN(B2:B4)) | #N/A |
Explanation: In this example, we want to find the correlation between the minimum number of units sold and the minimum sales revenue. We use the CORREL function nested with the MIN function. The MIN function calculates the minimum number of units sold and the minimum sales revenue, and the CORREL function attempts to calculate the correlation between these minimums. However, the CORREL function returns a #N/A error since there is only one pair of values. The correlation coefficient cannot be calculated from a single couple of deals.
Example 13: CORREL with ROUND
Purpose of Example: To find the correlation between the rounded number of units sold and the rounded sales revenue.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Units Sold | Revenue |
| 2 | January | 100.5 | 1000.5 |
| 3 | February | 200.5 | 2000.5 |
| 4 | March | 150.5 | 1500.5 |
| 5 | Formula | Result | |
| 6 | =CORREL(ROUND(A2:A4, 0), ROUND(B2:B4, 0)) | 1 |
Explanation: In this example, we want to find the correlation between the rounded number of units sold and the rounded sales revenue. We use the CORREL function nested with the ROUND function. The ROUND function rounds the number of units sold, and the sales revenue to the nearest whole number, and the CORREL function calculates the correlation between these rounded values. The formula =CORREL(ROUND(A2:A4, 0), ROUND(B2:B4, 0)) returns 1, which indicates a perfect positive correlation.
Part 3: Tips and Tricks
Check for Errors: Always check for errors when using the CORREL function. If array1 and array2 have a different number of data points, CORREL returns a #N/A error. If either array1 or array2 is empty, or if the standard deviation of their values equals zero, CORREL returns a #DIV/0! Error.
Interpret the Results Correctly: Remember that a correlation coefficient closer to +1 indicates a strong positive correlation, a coefficient closer to -1 indicates a strong negative correlation and a coefficient closer to 0 indicates no or weak correlation.
Use with Other Functions: The CORREL function can be used with other functions to perform more complex analyses. For example, you can use the CORREL function with the IF function to calculate the correlation coefficient for a subset of data.
Data Cleaning: If your data contains text, logical values, or empty cells, these will be ignored by the CORREL function. Ensure your data is clean and properly formatted before using the CORREL function.
Use for Forecasting: The CORREL function can be used in forecasting and developing business strategies. For example, suppose a strong positive correlation exists between the number of products sold and revenue. In that case, a business might decide to increase the number of products sold to increase revenue.