# Measuring correlation using Excel



## nizar (3 November 2007)

Im not sure if this is possible but using Excel, can you put up 2 sets of data like in 2 columns and then use some sort of function to measure their correlation?

Any suggestions?


----------



## Nyden (3 November 2007)

nizar said:


> Im not sure if this is possible but using Excel, can you put up 2 sets of data like in 2 columns and then use some sort of function to measure their correlation?
> 
> Any suggestions?





http://www.bized.co.uk/timeweb/crunching/crunch_relate_illus.htm#excelexp


Guess it depends on the variables in question though? Dates, or comparing 2 stocks; or a certain commodity to the price of one stock?

I guess an overly simplistic way would be to have 2 charts overlapping each other, & do it through the eye


----------



## Synergy (3 November 2007)

Just about anything is possible using excel. Depending on what sort of correlation you're looking for you could have a third column to display whether both stocks moved up or down together on a given day. eg..

=IF(OR(AND(A2-A1>0,B2-B1>0),AND(A2-A1<0,B2-B1<0)),1,"")

That will display a 1 in the third column if both stocks went up or down together. Then you can add up the 1's and get a % of the total days that the stocks moved up or down together.

or something like that anyway


----------



## doctorj (3 November 2007)

Am I missing something?  Why not just use the correlation coefficient?

=correl(array1,array2)

Go to wikipedia for info on what the correlation coefficient means and how it can be used.


----------



## nizar (3 November 2007)

The purpose is to be able to input results from 2 systems and determine their correlation.

eg. Say you've got a table with 2 columns, and the data in these columns is the monthly profits for N number of periods, and you want to see what sort of correlelation there is between -1 (perfect negative correlation) and 1 (perfect positive correlation).


----------



## nizar (3 November 2007)

doctorj said:


> Am I missing something?  Why not just use the correlation coefficient?
> 
> =correl(array1,array2)
> 
> Go to wikipedia for info on what the correlation coefficient means and how it can be used.




Yes doc thank you.

You are correct

http://www.businessbookmall.com/Excel_RP_Pearson's_Correlation_Coefficient.htm


----------



## Ashpool (7 November 2007)

Excel has a data analysis add in.  It comes standard with excel although needs to be enabled.

On Excel 2003
Tools--> Add Ins -->Tick data Analysis Toolpak
 and
Tools--> Add Ins -->Tick data Analysis Toolpak - VBA

Once these are enabled.

Then you can have your two data series in separate columns and go 
Tools --> Data Analysis
And 
Then select correlation, you can do a heap of other functions as well.
A wizard will start and ask for your input range etc.  

Do a google for excel data analysis toolpak and you should be able to find a few good sites that explain what all the functions do.


----------

