Subscribe!

Q:

Hi,

I need to be able to tell if two variable are related/correlated. How can I do this in Excel?

Thanks,

Jalelle

 

A:

Jalelle,

You are in luck, there is a simple built in function in Excel called correl().  Correl() is the Excel function for the correlation coefficient. The correlation coefficient measures the strength and direction of the relationship between two variables. So, if you have two rows of data, lets say sales in row 1 and customers in row 2, you can use the correl() function to tell if they are related. The format would be correl(a1:z1, b2:z2).  Sales and customers (any two variables) could be correlated in two ways. The first way is negative correlation. Negative correlation tells you when one variable goes up, the other will go down. The second type of correlation is positive correlation. Positive correlation tells you that as one variable goes up, the other variable will go up as well.

When you use the correl() function it will return a positive or negative number. The plus and minus indicates the positive or negative relationship and the size of the number indicates the strength of the relationship. The table below helps to explain what the correl() number means for your data set.

Correlation Negative Positive
Small −0.3 to −0.1 0.1 to 0.3
Medium −0.5 to −0.3 0.3 to 0.5
Large −1.0 to −0.5 0.5 to 1.0

 

 

 

 
Joseph Harris
ExcelYogi.com

If you found this article helpful, buy me a cup of coffee!

No related posts.

This entry was posted on Thursday, November 6th, 2008 at 1:11 pm and is filed under Business Analyst, Mail Bag. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Correlation Coefficient in Excel”

  1. Jon Peltier on November 6th, 2008 at 5:42 pm

    I know those values for small, medium, and large correlations were what Wikipedia presented, but a correlation of ± 0.5 (“medium/high” in the table above) means only 25% of the variation is due to covariance between the variables. 25% is a rather small correlation, actually.

    In Understanding
    Correlation
    , Prof. R.J. Rummel says “[S]quaring should be a healthy corrective to the tendency to consider low correlations, such as .20 and .30, as indicating a meaningful or practical covariation.”

    You should evaluate R², not R, and you should not imagine that your correlation is large unless R² is greater than perhaps 0.75.

  2. Joseph Harris on November 7th, 2008 at 10:22 am

    John…
    Thanks for the input. I do not fully agree with your assessment. You cannot really paint correlation with a broad brush whether you are using the correlation coefficient or the coefficient of determination.

    You cannot say that an R² of greater than any specific number is a large amount of correlation; it depends on the context of what you are looking at. For example, if you are saying over 12 years (12 data points) you have an R² of 50% that may or may not mean much, but if you have 4,380 data points (# of days in 12 years) and a R² of 50%, that is most likely a very significant amount of correlation.

    You are right that most statisticians would use R², but for real world views, and simple correlation (not causation), R is perfectly fine to use.

    Joe

Leave a Reply

You must be logged in to post a comment.