Juggler_IN
Active Member
- Joined
- Nov 19, 2014
- Messages
- 358
- Office Version
- 2003 or older
- Platform
- Windows
I am trying to generate Pearson's Correlation Matrix; and subsequently Spearman's.
When I use the Excel's in-built Correl function, I get the output. But, when I use my custom function for correl, I get a VALUE# error. That is, if I replace "Application.WorksheetFunction.Correl" with "PEARSONRHO" I do not get an output.
Functions are:
When I use the Excel's in-built Correl function, I get the output. But, when I use my custom function for correl, I get a VALUE# error. That is, if I replace "Application.WorksheetFunction.Correl" with "PEARSONRHO" I do not get an output.
Functions are:
Code:
Public Function PEARSONMAT(ByVal rng As Range) As Variant
Dim outRslt() As Double, i As Long, j As Long, iCols As Long
iCols = rng.Columns.Count
ReDim outRslt(iCols - 1, iCols - 1)
For i = 1 To iCols
For j = 1 To iCols
outRslt(i - 1, j - 1) = Application.WorksheetFunction.Correl(rng.Columns(i), rng.Columns(j))
Next j
Next i
PEARSONMAT = outRslt
End Function
Public Function PEARSONRHO(ByVal x As Variant, ByVal y As Variant) As Variant
Dim sx As Double, sy As Double
Dim s1 As Double, s2 As Double, s3 As Double
Dim k As Long
sx = 0
sy = 0
s1 = 0
s2 = 0
s3 = 0
For k = 1 To x.Rows.Count
sx = x(k) + sx
sy = y(k) + sy
Next
sx = sx / x.Rows.Count
sy = sy / y.Rows.Count
For k = 1 To x.Rows.Count
s1 = s1 + (x(k) - sx) * (y(k) - sy)
s2 = s2 + (x(k) - sx) ^ 2
s3 = s3 + (y(k) - sy) ^ 2
Next
PEARSONRHO = s1 / Sqr(s2 * s3)
End Function
