Dynamic Range for Offset
Try
=CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n))
and so on
HTH
Bob
"VBApprentice :)" wrote in message
...
Dear MVP T. Valko,
Thank you very much for your comment and solution proposal.
I wonder if I could change the "A1" & "B1" so that, different
starting points for the series could de performed. Each analysis
may start from a new Reference cell and I could not find how to
handle this.
Once again, thank you so much for your time and expertise.
Regards,
VBApprentice :)
"T. Valko" wrote:
Try it like this...
=CORREL(A1:INDEX(A1:A5,n),B1:INDEX(B1:B5,n))
Where n = the number of rows you want to include in the calculation. For
example, if n = 2 then this is what the formula will evaluate:
=CORREL(A1:A2,B1:B2)
Note, you might have to change the argument separator from a comma to a
semicolon depending on your location.
--
Biff
Microsoft Excel MVP
"VBApprentice :)" wrote in
message
...
Dear Gurus,
Trying to change the Reference Cell of the Offset funtion, but
unfortunately, can not succeed in doing so. For example,
1 2
A 100 30
B 105 43
C 106 45
D 107 21
E 120 45
Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and
then
offset(b1,1,0,0,4) - range(105:120)
then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX.
,1,0,0,2))
Thanks and Regards 2 all Gurus.
.
|