Measuring correlation using Excel

• 3 November 2007

• #1

N

nizar

Joined

5 January 2006

Posts

4,461

Reactions

1

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?

 

• 3 November 2007

• #2

Nyden

G.E. Money Genie

Joined

23 May 2007

Posts

1,368

Reactions

1

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?

Click to expand...

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

 

• 3 November 2007

• #3

Synergy

Joined

26 February 2007

Posts

358

Reactions

0

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

 

• 3 November 2007

• #4

doctorj

Hatchet Moderator

Joined

3 January 2005

Posts

3,271

Reactions

8

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.

 

• 3 November 2007

• #5

N

nizar

Joined

5 January 2006

Posts

4,461

Reactions

1

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).

 

• 3 November 2007

• #6

N

nizar

Joined

5 January 2006

Posts

4,461

Reactions

1

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.

Click to expand...

Yes doc thank you.

You are correct

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

 

• 7 November 2007

• #7

A

Ashpool

Joined

14 June 2007

Posts

3

Reactions

0

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.