calculate a correlation with ranges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paco - 20 Sep 2007 17:48 GMT

All i need is to calculate a correlation of two ranges and assign it to a
variable. I tried this but i get an 91 error (object or with block not set):

dim correlation as range
Set correlation.Formula = "=correl(inSerie" & i & ",inSerie" & j & ")"

but, if i replace "correlation" by range("a1"), then it works fine.

Reply to this Message

Tom Ogilvy - 20 Sep 2007 18:10 GMT

dim correlation as range
set correlation = Range("A1")
correlation.Formula = "=correl(inSerie" & i & ",inSerie" & j & ")"

Signature

Regards,
Tom Ogilvy

> All i need is to calculate a correlation of two ranges and assign it to a
> variable. I tried this but i get an 91 error (object or with block not set):
[quoted text clipped - 3 lines]
>
> but, if i replace "correlation" by range("a1"), then it works fine.

Reply to this Message

Paco - 20 Sep 2007 18:38 GMT

Hi Tom! you see, I cannot do that. My database changes so I don't know where
will i have empty cells. Do you know how can I assign that correlation value
to a variable (that doesn't require a cell)?

> dim correlation as range
> set correlation = Range("A1")
[quoted text clipped - 7 lines]
> >
> > but, if i replace "correlation" by range("a1"), then it works fine.

Reply to this Message

Tom Ogilvy - 20 Sep 2007 19:28 GMT

I don't know what i and j contain or what inSerie is. These may be defined
names.

But if you look at the example in help for the correl worksheet function you
see the formula

=CORREL(A2:A6,B2:B6)

to do that in VBA

Dim correlation as double
correlation = application.Correl(Range("A2:A6"), Range("B2:B6"))

Just to demonstrate from the immediate window in the VBE:

correlation = application.Correl(Range("A2:A6"), Range("B2:B6"))
? correlation
0.997054485501581

A2:A6 and B2:B2 contain the numbers shown in the help example.

if Inserie1 is a named range and inserie2 is a named range then it would be

Dim i as Long, j as Long, correlation as double
i = 1
j = 1
correlation = application.Correl(Range("inSerie" & i), Range("inSerie" & j))

Signature

Regards,
Tom Ogilvy

> Hi Tom! you see, I cannot do that. My database changes so I don't know where
> will i have empty cells. Do you know how can I assign that correlation value
[quoted text clipped - 11 lines]
> > >
> > > but, if i replace "correlation" by range("a1"), then it works fine.

Reply to this Message

Paco - 20 Sep 2007 21:14 GMT

This is great!!! thanks a lot Tom!!! I've been trying for days!!!

> I don't know what i and j contain or what inSerie is. These may be defined
> names.
[quoted text clipped - 39 lines]
> > > >
> > > > but, if i replace "correlation" by range("a1"), then it works fine.

Reply to this Message