Dynamic Range for Offset

[VBApprentice :)]

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.

 

[T. Valko]

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.

 

[VBApprentice :)]

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

 

[Bob Phillips]

Try

=CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n))

and so on

HTH

Bob

 

[VBApprentice :)]

Dear Guru Bob Philips,

Thank you so much for your comment and solution proposal.

In your proposing notation, " : >
=CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n))"
writing the Reference cells in "B1" and "C1" notation, then the spirit of
automation is gone and manual intervention is needed to change this.

I wonder, if you had used a Worksheet function without using VBA, to define
this "A1" "B1" "C1" Reference points parametrically ? I tried +Address but
it didn't work.

Thank you so much for your patience and time.

Regards,

VBApprentice

 

[Bob Phillips]

You need to explain this bit ...

then the spirit of
automation is gone and manual intervention is needed to change this.

it makes no sense to me, and I am not seeing your problem.

HTH

Bob

 

[T. Valko]

I think they mean something like this...

Data in the range A1:B10

C1 = 2 = start position
D1 = 5 = end position

=CORREL(INDIRECT("A"&C1&":A"&D1),INDIRECT("B"&C1&":B"&D1))

Or, this non-volatile version:

=CORREL(INDEX(A1:A10,C1):INDEX(A1:A10,D1),INDEX(B1:B10,C1):INDEX(B1:B10,D1))

Both of which evaluate to:

=CORREL(A2:A5,B2:B5)

 

[VBApprentice :)]

Dear MVPs and Gurus,

T. Valko,
Bob Philips,

Thank you very much for your time and solution proposals.

T. Valko's last proposal may solve the trick.

Once again, I thank you for your time and expertise, and wish you
a jolly good 2010.

VBApprentice

 

[T. Valko]

You're welcome. Thanks for the feedback!