Using Correlation Analysis in a macro??

[stephenjones]

I have a macro (see below) that should select the range of cells, then run the &quot;correlation&quot; tool from the data analysis add-in (TOOLS < DATA ANALYSIS < CORRELATION), but it doesn't work. Sometimes I get an error that says it can't find the ATPVBAEN.XLA!Mcorrel file. Now, I get no error, but it doesn't run the Macro...just nothing. Any thoughts?

Sub Macro7()
Application.Run &quot;ATPVBAEN.XLA!Mcorrel&quot;, ActiveSheet.Range(&quot;$A$1:$CQ$25&quot, _
ActiveSheet.Range(&quot;$A$27&quot, &quot;C&quot;, True
End Sub

 

[SkipVought]

hi steve,

you are not using the function properly. a function returns a value -- not like a sub.

here's code that works...

    vVal = Application.Correl(Range("rng1"), Range("rng2"))
    MsgBox vVal

hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

BTW,

the two ranges must have an identical number of data points...

HELP on CORREL...
&quot;If array1 and array2 have a different number of data points, CORREL returns the #N/A error value.&quot;

I used two named ranges. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[stephenjones]

Skip-
Thanks for the help. However, I want the macro to run the correlation, then paste the values in cell A27. This part of the macro is part of a larger macro. I used the recorder and that was the code it developed. Why doesn't it run?

 

[Zathras]

Which is it? &quot;Mcorrel&quot; or &quot;Correl&quot;?

 

[SkipVought]

Then

    Range("A27") = Application.Correl(Range("$A$1:$CQ$25"), Range("rng2"))

Your code need two ranges---
one is (&quot;$A$1:$CQ$25&quot

What's the other? -- (substitute for rng2)

Recorder code is &quot;dirty&quot; -- very useful but often not efficient. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I guess I don't have Mcorrel in my analysis toolpak -- sorry Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[stephenjones]

Skip-
Not sure what the second range would be, its a correlation so you only have an input range and then a cell to paste the results.

Also, the code that you gave, should that be in a FUNCTION or SUB?

 

[SkipVought]

My code could go in either a function or a sub. The difference is that a function usually returns a single value like the function SUM (sans error handling)...

Function MySum(rng As Range) As Variant
   dim vVal as Variant
   For Each r In rng
      vVal = vVal + r.Value
   Next
   MySum = vVal
End Function

Using the function on a spreadsheet...

=MySum("A2:A20")
[\code]
Then I can use MySum in a sub
[code]
Sub MyTest
   .....
   nSum = MySum(Range("A2:A20"))
   .....
End Sub

I found out that MCorrel runs from a Dialog. The single input range must be at least 2 columns to correlate. Mcorrel gives me the same value on my two columns as Correl in my example above - except that the MCorrel output is a table rather than a single value.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com