Correlation analysis

[todd012976459210]

HELP!

I need to do a correlation analysis of mutiple variables against an
observable daily event. Can someone profiicient in Excel and statistics guide
me? Specifically, I have 10 variables whose correlation I want to measure
against a daily observation, both individually and collectively. I also want
to mearsure the correlation for all available samples, but also for just the
most recent 20.

Please, can anyone help me accomplish this?

 

[Sean Timmons]

Just use the CORREL() function.

CORREL(array1,array2)

Just grab the necessary arrays to accomplish your goal and you are solid!

 

[todd012976459210]

Is there a function in Excel that automatically queries just the most recent
20 occurances or do I have to manually adjust the parameters each day? Thanks!
Todd

 

[Sean Timmons]

=CORREL(INDIRECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<>""),A2:A1000),A:A,1,0),A:A,0)-20,1)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(A2:A1000<>""),A2:A1000),A:A,1,0),A:A,0),1)),INDIRECT(ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<>""),B2:B1000),B:B,1,0),B:B,0)-20,2)&":"&ADDRESS(MATCH(VLOOKUP(LOOKUP(2,1/(B2:B1000<>""),B2:B1000),B:B,1,0),B:B,0),2)))

This assumes your values are down columns A and B with headers in row 1.

Change A's to your first array column name, B's to your 2nd array column name.

Also where I have:

A:A,0),1

Change to your column name and the number of the column (i.e. - Column D
would be 4 instead of 1)

Same with the 2nd formula.

If you use rows, just change A and B for row #'s

 

[todd012976459210]

Great!! Thank you very much. Let me dive into this and play with it and see
if I can get it to work.

 

[Peo Sjoblom]

I don't think you need to make it that complicated nor using volatile
functions as long as there will be no empty cell
in-between


=CORREL(INDEX(A2:A10000,COUNT(A2:A10000)):INDEX(A2:A10000,COUNT(A2:A10000)-19),INDEX(B2:B10000,COUNT(B2:B10000)):INDEX(B2:B10000,COUNT(B2:B10000)-19))



--


Regards,


Peo Sjoblom

 

[todd012976459210]

OK! Thanks, I'll try it.

I don't think you need to make it that complicated nor using volatile
functions as long as there will be no empty cell
in-between


=CORREL(INDEX(A2:A10000,COUNT(A2:A10000)):INDEX(A2:A10000,COUNT(A2:A10000)-19),INDEX(B2:B10000,COUNT(B2:B10000)):INDEX(B2:B10000,COUNT(B2:B10000)-19))



--


Regards,


Peo Sjoblom