what is the correlation coefficient 
the correlation coefficient is a statistical measure of the strength of a linear relationship between two variables its values can range from 1 to 1 a correlation coefficient of 1 describes a perfect negative or inverse correlation with values in one series rising as those in the other decline and vice versa a coefficient of 1 shows a perfect positive correlation or a direct relationship a correlation coefficient of 0 means there is no linear relationship correlation coefficients are used in science and finance to assess the degree of association between two variables factors or data sets for example as high oil prices are favorable for crude producers one might assume that the correlation between oil prices and forward returns on oil stocks is strongly positive calculating the correlation coefficient for these variables based on market data reveals a moderate and inconsistent correlation over lengthy periods 1understanding the correlation coefficientdifferent types of correlation coefficients are used to assess correlation based on the properties of the compared data by far the most common is the pearson coefficient known as pearson s r which measures the strength and direction of a linear relationship between two variables the pearson coefficient uses a mathematical statistics formula to measure how closely the data points combining the two variables with the values of one data series plotted on the x axis and the corresponding values of the other series on the y axis approximate the line of best fit the line of best fit can be determined through regression analysis the pearson coefficient the most common correlation coefficient cannot assess nonlinear associations between variables and or differentiate between dependent and independent variables the further the coefficient is from zero whether it is positive or negative the better the fit and the greater the correlation the values of 1 for a negative correlation and 1 for a positive one describe perfect fits in which all data points align in a straight line indicating that the variables are perfectly correlated in other words the relationship is so predictable that the value of one variable can be determined from the matched value of the other the closer the correlation coefficient is to zero the weaker the correlation until at zero no linear relationship exists at all assessments of correlation strength based on the correlation coefficient value vary by application in physics and chemistry a correlation coefficient should be lower than 0 9 or higher than 0 9 for the correlation to be considered meaningful while in social sciences the threshold could be as high as 0 5 and as low as 0 5 for correlation coefficients derived from sampling the determination of statistical significance depends on the p value which is calculated from the data sample s size as well as the value of the coefficient formula for the correlation coefficientto calculate the pearson correlation start by determining each variable s standard deviation as well as the covariance between them the correlation coefficient is covariance divided by the product of the two variables standard deviations x y cov x y x y where x y pearson product moment correlation coefficient cov x y covariance of variables x and y x standard deviation of x y standard deviation of y begin aligned rho xy frac text cov x y sigma x sigma y textbf where rho xy text pearson product moment correlation coefficient text cov x y text covariance of variables x text and y sigma x text standard deviation of x sigma y text standard deviation of y end aligned xy x y cov x y where xy pearson product moment correlation coefficientcov x y covariance of variables x and y x standard deviation of x y standard deviation of y standard deviation is a measure of the dispersion of data from its average covariance shows whether the two variables tend to move in the same direction while the correlation coefficient measures the strength of that relationship on a normalized scale from 1 to 1 the formula above can be elaborated asr n x y x y n x 2 x 2 n y 2 y 2 where r correlation coefficient n number of observations begin aligned r frac n times sum x y sum x times sum y sqrt n times sum x 2 sum x 2 times n times sum y 2 sum y 2 textbf where r text correlation coefficient n text number of observations end aligned r n x2 x 2 n y2 y 2 n x y x y where r correlation coefficientn number of observations correlation statistics and investingthe correlation coefficient is particularly helpful in assessing and managing investment risks for example modern portfolio theory suggests diversification can reduce the volatility of a portfolio s returns curbing risk the correlation coefficient between historical returns can indicate whether adding an investment to a portfolio will improve its diversification correlation calculations are also a staple of factor investing a strategy for constructing a portfolio based on factors associated with excess returns meanwhile quantitative traders use historical correlations and correlation coefficients to anticipate near term changes in securities prices limitations of the pearson correlation coefficientcorrelation does not imply causation as the saying goes and the pearson coefficient cannot determine whether one of the correlated variables is dependent on the other nor does the correlation coefficient show what proportion of the variation in the dependent variable is attributable to the independent variable that s shown by the coefficient of determination also known as r squared which is simply the correlation coefficient squared the correlation coefficient also does not describe the slope of the line of best fit the slope can be determined with the least squares method in regression analysis the pearson correlation coefficient can t be used to assess nonlinear associations or those arising from sampled data not subject to a normal distribution it can also be distorted by outliers data points far outside the scatterplot of a distribution those relationships can be analyzed using nonparametric methods such as spearman s correlation coefficient the kendall rank correlation coefficient or a polychoric correlation coefficient finding correlation coefficients in excelthere are a few ways to calculate correlation in excel the simplest way is to input two data series in adjacent columns and use the built in correlation formula if you want to create a correlation matrix across a range of data sets excel has a data analysis plugin to use it you must first enable the data analysis toolpak this can be done by clicking on file and then options which should open the excel options dialogue box in the box click on add ins and then on the manage dropdown select excel add ins and click on go this will cause the add ins box to appear check the checkbox for analysis tookpak then click ok the enable process should now be complete to use the data analysis plugin click on the data ribbon and then select data analysis which should open a box in the box click on correlation and then ok the correlation box will now open and you can enter the input ranges either manually or by selecting the relevant cells in this case our columns are titled so we want to check the box labels in first row so excel knows to treat these as titles then you can choose to output on the same sheet or on a new sheet hitting enter will produce the correlation matrix you can add some text and conditional formatting to clean up the result 