Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate a correlation with ranges
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate a correlation with ranges
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate a correlation with ranges
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate a correlation with ranges
This is great!!! thanks a lot Tom!!! I've been trying for days!!!
"Tom Ogilvy" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how i calculate pearson correlation coefficient for excel graph | Charts and Charting in Excel | |||
calculate ranges | Excel Worksheet Functions | |||
How do I calculate an offset between two ranges? | Excel Programming | |||
how to calculate number ranges | Excel Worksheet Functions | |||
How do I use the "Correlation" dialog box with multiple ranges? | Excel Discussion (Misc queries) |