Changing the number of cells calculated
 

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

Changing the number of cells calculated
 

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


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 cells address in column E €“ N
rows:current cells address in column E, current cells
address in column F €“ N rows:current cells 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|
+-------------------------------------------------------------------+



--
zealot

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

thanks!!!!!