Forum des VBA-Tutorials

Korrelation von gefilterten Werten berechnen (VBA)

Julian, Mittwoch, 16.01.2019, 09:45 (vor 92 Tagen) @ Julian

Function Correl8(R1 As Range, R2 As Range) As Double
' correlation coefficient function that ignores hidden values
Dim Sig1 As Double
Dim Sig2 As Double
Dim S1 As Double
Dim S2 As Double
Dim Mu1 As Double
Dim Mu2 As Double
Dim N As Integer
Dim I As Integer
 
 
 
Dim o As Integer
Dim p As Integer
Dim l As Integer
 
 
'Zellennummer bestimmen

For o = 4 To R1.Cells.Count
If R1.Rows(o).Hidden Then
Exit For
End If
Next o
 
For p = 4 To R2.Cells.Count
If R2.Rows(p).Hidden Then
Exit For
End If
Next p
 
l = p - o
 
Sig1 = 0: Sig2 = 0: Mu1 = 0: Mu2 = 0: S1 = 0: S2 = 0
N = 0
 
For I = 1 To R1.Cells.Count
If Not R1.Rows(I).Hidden And Not R2.Rows(I + l).Hidden Then
N = N + 1
Mu1 = Mu1 + R1.Cells(I)
Mu2 = Mu2 + R2.Cells(I + l)
S1 = S1 + R1(I) ^ 2
S2 = S2 + R2(I + l) ^ 2
End If
Next I
 
Sig1 = Sqr((N * S1 - Mu1 ^ 2)) / N
Sig2 = Sqr((N * S2 - Mu2 ^ 2)) / N
Mu1 = Mu1 / N
Mu2 = Mu2 / N
 
Correl8 = 0
For I = 1 To R1.Cells.Count
If Not R1.Rows(I).Hidden And Not R2.Rows(I + l).Hidden Then
Correl8 = Correl8 + (R1.Cells(I) - Mu1) * (R2.Cells(I + l) - Mu2)
End If
Next I
 
Correl8 = Correl8 / Sig1 / Sig2 / N
 
End Function

Das hier funktioniert schonmal nicht