Dynamic Range for Offset
 

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.

Dynamic Range for Offset
 

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.

Dynamic Range for Offset
 

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.



.

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.



.

Dynamic Range for Offset
 

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

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.



.



.

Dynamic Range for Offset
 

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

"VBApprentice :)" wrote in message
...
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" wrote:

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.



.



.

Dynamic Range for Offset
 

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)

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
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

"VBApprentice :)" wrote in
message ...
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" wrote:

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.



.



.

Dynamic Range for Offset
 

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

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)

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
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

"VBApprentice :)" wrote in
message ...
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" wrote:

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.



.



.





.

Dynamic Range for Offset
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"VBApprentice :)" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
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

"VBApprentice :)" wrote in
message ...
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" wrote:

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.



.



.





.