Excel Yogi’s

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.

 
Joseph Harris
ExcelYogi.com

Read original blog post

correl() is often misunderstood but can be very powerful. I use it almost daily as a business analyst. It is hard to beat for quick, two variable, correlation.

The problem (as Joe points out in the original post) is that people confuse correlation with causation.