calculate a correlation with ranges
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))
--
Regards,
Tom Ogilvy
"Paco" wrote:
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)?
"Tom Ogilvy" wrote:
dim correlation as range
set correlation = Range("A1")
correlation.Formula = "=correl(inSerie" & i & ",inSerie" & j & ")"
--
Regards,
Tom Ogilvy
"Paco" wrote:
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.
|