Correlation analysis

  • Thread starter Thread starter todd012976459210
  • Start date Start date
T

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?
 
Just use the CORREL() function.

CORREL(array1,array2)

Just grab the necessary arrays to accomplish your goal and you are solid!
 
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
 
=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
 
Great!! Thank you very much. Let me dive into this and play with it and see
if I can get it to work.
 
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
 
OK! Thanks, I'll try it.

Peo Sjoblom said:
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
Click to expand...
 
Back
Top