Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)®. Register today!

CORREL Function

Calculates the correlation coefficient between two variables

What is the CORREL Function?

The CORREL function is categorized under Statistical functions. It will calculate the correlation coefficient between two variables.

As a financial analyst, the CORREL function is very useful when we want to find the correlation between two variables, i.e., the correlation between a particular stock and the market index.

 

Correlation Formula

=CORREL(array1, array2)

 

The CORREL function uses the following arguments:

  1. Array1 (required argument) – It is the set of independent variables. It is a cell range of values.
  2. Array2 (required argument) – It is the set of dependent variables. It is the second cell range of values.

The equation for the correlation coefficient is:

 

Correlation Coefficient

 

Where:

CORREL are the sample means AVERAGE(array1) and AVERAGE(array2).

 

So, if the value of 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?

The CORREL function was introduced in Excel 2007 and is available in all subsequent Excel versions. To understand the uses of the function, let us consider an example:

 

Correlation Example

Suppose we are given data about the weekly returns of stock A and percentage of change in the market index (S&P 500):

 

CORREL Function

 

The formula used to find the correlation is:

 

CORREL Function - Example 1

 

We get the result below:

 

CORREL Function - Example 1a

 

The result indicates a strong positive correlation.

 

Things to remember about the CORREL Function

  1. #N/A error – Occurs if the given arrays are of different lengths. So, if Array1 and Array2 contain different numbers of data points, CORREL will return the #N/A error value.
  2. #DIV/0 error – Occurs if either of the given arrays are empty or if the standard deviation of their values equals zero.
  3. If an array or reference argument contains text, logical values, or empty cells, the values are ignored; however, cells with the value zero are included.
  4. The CORREL function is exactly same as the PEARSON Function, except that, in earlier versions of Excel (earlier than Excel 2003), the PEARSON function may exhibit some rounding errors. Hence, it is advisable to use the CORREL function in earlier versions of Excel. In later versions of Excel, both functions should give the same results.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!