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?