WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » CORREL Excel Function (Correlation)

CORREL Excel Function (Correlation)

By Tanuj KumarTanuj Kumar | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

CORREL in Excel

CORREL function is categorized as statistical functions in Excel. The CORREL formula in Excel is used to find out the correlation coefficient between two variables. It returns the correlation coefficient of the array1 and array2.

You can use the correlation coefficient to determine the relationship between the two properties.

For example, – The correlation between a particular stock and the market index.

CORREL Formula in Excel

CORREL Formula in Excel

CORREL Formula in Excel has two compulsory parameters, i.e., array1, array2.

Compulsory Parameters:

  • array1: It is required a set of an independent variable.
  • array2:  It is a set of the dependent variable.

Remarks

The correlation coefficient formula is:

correlation coefficient equation

where and are the sample means and calculated by average(array1) and average(array2).

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

If the value of the correlation coefficient 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 the CORREL Function in Excel?

The CORREL function in Excel is very simple and easy to use. Let us understand the working of CORREL with some examples. CORREL function in Excel can be used as a worksheet function and as a VBA function.

You can download this CORREL Function Excel Template here – CORREL Function Excel Template

CORREL function as a worksheet function.

Example #1

In the first example, let’s consider two sets of data Data1 and Data2, as shown in the below table.

CORREL Example 1

=CORREL(B4:B22,C4:C22) =0.6642909

Example #2

In this example, we consider the data set of weekly changes for a stock A as data1 and SP 500 weekly changes as data 2 show in the below table. Now calculate the correlation coefficient function using CORREL formula in excel, =CORREL(F3:F23,G3:G23), and output will be- 0.89011522.

Example 2

Example #3

In this example, we are taking a perfect positive correlation; for example, considering a variable X value increases with the value of a variable, and the value of variable X decreases with the value of variable Y decreases as shown in the below table.

Example 3

Example #4

Here we consider an example of a perfect negative correlation, as the value of variable X increases when the value of variable Z decreases, and as the value of variable X decreases, the value of variable Z increases, as shown in the below example.

Example 4

CORREL function in Excel can be used as a VBA function.

Let’s consider a dataset in excel that starts from A2.

Sub CORRELfunction()

Dim r As Double

Dim ra As Range

Dim rb As Range

Set ra = Range(“A2”, Range(“A2”).End(xlDown)) //ra stores the ranges from A2 to last entry in column A.

Set rb = ra.Offset(, 1) //rb stores the array values from C2 to all values  in column C.

r = Application.WorksheetFunction.correl(ra, rb)  //Correlation function value is stored in r variable.

MsgBox r //prints the output in Message Box.

End Sub

Things to Remember 

  • #N/A error – (CORREL) Correlation function in Excel through the #N/A error if the given arrays are of different lengths. Means, if array1 and array2 contain different numbers of data points, CORREL will return the #N/A error value.
  • #DIV/0 error – Correlation function in Excel through the #DIV/0 error if either of the given arrays (array1, array2) are empty or if the excel standard deviation of the values equals zero.

If the supplied array or reference argument contains text/string, logical values, or an empty value, then those values are ignored automatically.

  • (CORREL) Correlation function in Excel includes the value zero in its calculation.

CORREL Excel Function Video

Recommended Articles

This has been a guide to (CORREL) Correlation Function in Excel. Here we discuss the CORREL Formula in excel and how to use the CORREL function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • Average Formula in Excel
  • VBA MsgBox Function
  • Correlation Matrix in Excel
  • 5 Different Types of Arrays in VBA Excel
  • OFFSET Excel
  • FIND Formula in Excel
  • INT Formula in Excel
  • LN Excel Function
  • TAN in Excel
  • SUBTOTAL Excel Function
  • DATE Excel Function
2 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download CORREL Function Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More