Changing the number of cells calculated

[zealot]

Hello Friends!!

I want to ask if it is possible to make the CORREL function to
calculate custom periods of data. For example CORREL(E52:E181,F52:F181)
calculates correlation for 129 rows of data (181-52). Do you think that
the range that the function takes into account can be instead
represented as the number of the current cell minus a cell N rows above
(e.g. CORREL(current cell’s address in column E – N
rows:current cell’s address in column E, current cell’s
address in column F – N rows:current cell’s address in
column F)) and the N will be set in a separate cell? For example for
N=10 the function will look as follows CORREL(E171:E181,F171:F181) and
CORREL(E151:E181,F151:F181) when the N is set to 30.

Hoping for an answer))

Dima


+-------------------------------------------------------------------+
|Filename: correlation.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=83|
+-------------------------------------------------------------------+

 

[Duke Carey]

This is untested

Maybe the offset function would work. Put the number of cells into cell A1

=CORREL(OFFSET(E181,-A1+1,0,A1,1),OFFSET(F181,-A1+1,0,A1,1))

 

[zealot]

thanks.!!! i have been able to make the offset function work with corre
function....cool!!!
now i need to find a way to rotate the N (number of periods considere
by the Offset function) from 5 to 100 and tabulate the results in eac
case...do you think you can guide me in how this can be done?

thanks!!!!

+-------------------------------------------------------------------
+-------------------------------------------------------------------