Correlation analysis
 

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?

Correlation analysis
 

Just use the CORREL() function.

CORREL(array1,array2)

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

"todd012976459210" wrote:

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?

Correlation analysis
 

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" wrote:

Just use the CORREL() function.

CORREL(array1,array2)

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

"todd012976459210" wrote:

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?

Correlation analysis
 

=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)),INDIR ECT(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" wrote:

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" wrote:

Just use the CORREL() function.

CORREL(array1,array2)

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

"todd012976459210" wrote:

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?

Correlation analysis
 

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

"Sean Timmons" wrote:

=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)),INDIR ECT(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" wrote:

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" wrote:

Just use the CORREL() function.

CORREL(array1,array2)

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

"todd012976459210" wrote:

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?

Correlation analysis
 

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:B10 000,COUNT(B2:B10000)-19))



--


Regards,


Peo Sjoblom

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

"Sean Timmons" wrote:

=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)),INDIR ECT(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" wrote:

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" wrote:

Just use the CORREL() function.

CORREL(array1,array2)

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

"todd012976459210" wrote:

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?

Correlation analysis
 

OK! Thanks, I'll try it.

"Peo Sjoblom" wrote:

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:B10 000,COUNT(B2:B10000)-19))



--


Regards,


Peo Sjoblom

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

"Sean Timmons" wrote:

=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)),INDIR ECT(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" wrote:

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" wrote:

Just use the CORREL() function.

CORREL(array1,array2)

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

"todd012976459210" wrote:

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?