PDA

View Full Version : Help with calculating Correl from 2-D array



chris111
10-12-2010, 07:26 AM
Hi Guys,

I have a two dimensional array "signals" that is 20 by 200 (roughly).

I want to calculate the correlation of various columns to each other. For example, I want to calculate the correlation of column 1 to column 2... how do I specify it..?

I am trying
Correl = Application.WorksheetFunction.Correl(signals(1), signals(2))


However this isn't working (presumably because i'm treating it as a 1-D array).. Any ideas?

GTO
10-12-2010, 09:39 AM
Hi there,

No promises as I am not really understanding Correl, but to pull the columns from the array, I think you can use Application.Index


Option Explicit

Sub exa()
Dim aryData
Dim dblCorrel As Double

aryData = Range("A2:J21").Value

dblCorrel = Application.Correl(Application.Index(aryData, 0, 1), Application.Index(aryData, 0, 3))

MsgBox dblCorrel
End Sub

Hope that helps,

Mark

Kenneth Hobs
10-12-2010, 09:40 AM
MsgBox WorksheetFunction.Correl(Range("Signals").Columns(1), Range("Signals").Columns(2))