Formula to Calculate Correlation

The value of correlation is limited between -1 and +1 and can be interpreted as follows:

  • -1: If it is -1, then variables are known as perfectly negatively correlated. That means if one variable is moving in one direction, another is moving in the opposite direction.0: That means the variable is not having any correlation.+1: If it is +1, then variables are known as perfectly positively correlated. Both variables are moving in positive directions.

If we have two variables, x and y, then the correlation coefficientCorrelation CoefficientCorrelation Coefficient, sometimes known as cross-correlation coefficient, is a statistical measure used to evaluate the strength of a relationship between 2 variables. Its values range from -1.0 (negative correlation) to +1.0 (positive correlation). read more between 2 variables can be found as:

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Correlation Formula (wallstreetmojo.com)

Where,

  • x(i)= value of x in the sampleMean(x) = mean of all values of xy(i) = value of y in the sampleMean(y) = mean of all values of y

Examples

It is effortless to calculate the correlation in ExcelCorrelation In ExcelCORREL function is a statistical function in Excel. The CORREL formula finds out the coefficient between two variables and returns the coefficient of array1 and array2. The correlation coefficient determines the relationship between the two properties.read more. The syntax of the function used is as follows:

Correlation Coefficient = CORREL (array1, array2)

Example#1

Let’s take the same example above for calculating correlation using Excel.

Solution:

Below are the values of x and y:

The calculation is as follows.

Basis Excel formula = CORREL (array(x), array(y))

Coefficient = +0.95

Since this coefficient is near +1, x and y are highly positively correlated.

Example#2 

Correlation is mainly useful for analyzing companies’ stock prices and creating a stock portfolio based on that.

Let us determine the correlation of Apple stock with the Nasdaq index based on the last one-year stock performance. Apple is a US-based multinational companyMultinational CompanyA multinational company (MNC) is defined as a business entity that operates in its country of origin and also has a branch abroad. The headquarter usually remains in one country, controlling and coordinating all the international branches. read more specializing in IT products such as iPod, iPad, Mac, etc.

Below is the monthly return of Apple and Nasdaq stocks for the last year:

Let’s now input the values –

Correlation Coefficient = ∑(x(i)- mean(x)).(y(i)-mean(y))/√ ∑(x(i)-mean(x)) ^2 ∑(y(i)-mean(y))^2

Correlation between Apple and Nasdaq= 0.039/ (√0.0039)

Coefficient =0.62

Since the correlation between Apple and Nasdaq is positive. Hence, Apple positively correlates with Nasdaq

Example#3

Let us now examine the correlation between Walmart and the Nasdaq index based on last year’s stock performance. Walmart is a US-based company which has a retail supermarket chain.

Below is the monthly performance between Walmart and Nasdaq for the last year:

Let’s now input the values in the formula: –

Therefore, the calculation is as follows:

Correlation between Walmart and Nasdaq= 0.0032/ (√0.0346*0.0219 )

Coefficient =0.12

We can see that Walmart and Nasdaq are positively correlated but not as much compared to Apple’s correlation with Nasdaq.

Relevance and Use

A correlation coefficient is useful in establishing the linear relationshipLinear RelationshipA linear relationship describes the relation between two distinct variables - x and y - in the form of a straight line on a graph. When presenting a linear relationship through an equation, the value of y is derived through the value of x, reflecting their correlation.read more between two variables. It measures how a variable will move compared to the movement of another variable. For example, the practical use of this coefficient is to find out the relationship between stock price movement with the overall market movement. Based on this analysis, a stock analyst will include the proportion of stocks to create an optimal portfolioOptimal PortfolioPortfolio optimization refers to the strategic process of making improvements or positive changes to the investor’s portfolio containing various assets. The Sharpe ratio helps to analyze the returns from an optimal portfolio.read more with minimum risk. Also, it is useful in data science to find the relationship between 2 variables.

Also, the correlation coefficient is used very highly for studying the construct validity of data in factor analysis. Furthermore, it is highly used in regression analysisRegression AnalysisRegression Analysis is a statistical approach for evaluating the relationship between 1 dependent variable & 1 or more independent variables. It is widely used in investing & financing sectors to improve the products & services further. read more to predict the values of dependent variables based on the relationship between dependent and independent variables. Finally, this equation is useful in quantitative analysis to determine the relationship between various variables. The basis of this relationship, if a variable is unrelated to other variables, it can be eliminated from the list.

This article is a guide to Correlation Formula. Here, we discuss calculating correlation using its formula, examples, and a downloadable Excel template. You can learn more about financing from the following articles: –

  • Gini CoefficientGini CoefficientGini Coefficient or Gini Index is statistical dispersion depicting the income dispersions amongst the population of a country i.e. it represents the wealth inequalities of the citizens of a particular country. read moreAdjusted R Squared FormulaAdjusted R Squared FormulaAdjusted R Squared refers to the statistical tool which helps the investors in measuring the extent of the variance of the variable which is dependent that can be explained with the independent variable and it considers the impact of only those independent variables which have an impact on the variation of the dependent variable.read moreCorrelation vs CovarianceCorrelation Vs CovarianceCovariance and Correlation are two terms which are exactly opposite to each other; both are used for statistics and regression analysis. Covariance reflects how two variables vary from each other, whereas correlation depicts the relationship between two variables.read moreNegative CorrelationNegative CorrelationA negative correlation is an effective relationship between two variables in which the values of the dependent and independent variables move in opposite directions. For example, when an independent variable increases, the dependent variable decreases, and vice versa.read more