AW: Funktion von VBA in excel
18.06.2005 17:39:55
VBA
Sorry natürlich diese Funktion
Function regress_orthols(x, y)
Dim stat
Dim n As Long, i As Long
Dim mean_x As Double, mean_y As Double
Dim sig_x As Double, sig_y As Double
Dim u, v
Dim sum_v2 As Double, sum_u2 As Double, sum_uv As Double
Dim part1 As Double, part2 As Double, r As Double
Dim b(1 To 2)
stat = regress_ls(x, y)
n = UBound(x) - LBound(x) + 1
mean_x = Application.WorksheetFunction.Average(x)
mean_y = Application.WorksheetFunction.Average(y)
sig_x = Application.WorksheetFunction.StDev(x)
sig_y = Application.WorksheetFunction.StDev(y)
u = x
v = y
For i = LBound(x) To UBound(x)
u(i) = x(i) - mean_x
Next
For i = LBound(y) To UBound(y)
v(i) = y(i) - mean_y
Next
sum_v2 = Application.WorksheetFunction.Sum(element_mul(v, v))
sum_u2 = Application.WorksheetFunction.Sum(element_mul(u, u))
sum_uv = Application.WorksheetFunction.Sum(element_mul(u, v))
part1 = sum_v2 - sum_u2
part2 = Sqr((sum_u2 - sum_v2) ^ 2# + 4# * sum_uv * sum_uv)
b(1) = (part1 + part2) / (2# * sum_uv)
b(2) = (part1 - part2) / (2# * sum_uv)
r = sum_uv / Sqr(sum_u2 * sum_v2)
If Sgn(b(1)) = Sgn(stat(1)) Then
stat(1) = b(1)
Else
stat(1) = b(2)
End If
stat(2) = mean_y - stat(1) * mean_x
stat(3) = stat(1) * Sqr((1# - r * r) / n) / r
stat(4) = Sqr(((sig_y - sig_x * stat(1)) ^ 2) / n + (1# - r) * stat(1) * _
(2# * sig_x * sig_y + (mean_x * stat(1) * (1# + r) / (r * r))))
regress_orthols = stat
End Function