Excel NORMDIST Function in VB6
I was converting formulae in Excel to VB6 when I hit a stumbling block: there is no equivalent NORMDIST function in VB6. Fortunately someone had written a NormDist function in C# so it was just a matter of translating C# to VB6. As far as my testing goes it seems to be accurate within seven decimal points.
Note: the following is for NORMDIST(x, mean, standard_dev, TRUE), i.e., it’s cumulative.
Public Function NormDist(ByVal x As Variant, ByVal mean As Variant, ByVal std As Variant) As Variant
NormDist = CDec(Phi3(x, mean, std))
End Function
Private Function Phi3(ByVal z As Variant, ByVal mu As Variant, ByVal sigma As Variant) As Variant
Phi3 = CDec(Phi((z - mu) / sigma))
End Function
Private Function Phi(ByVal z As Variant) As Variant
Phi = CDec(0.5 * (1 + erf(z / Sqr(2))))
End Function
Private Function erf(ByVal z As Variant) As Variant
Dim t As Variant
Dim ans As Variant
Dim result As Variant
t = CDec(1 / (1 + (0.5 * Abs(z))))
ans = CDec(1 - (t * Exp(((-1 * z) * z) - 1.26551223 + t * (1.00002368 + t * (0.37409196 + t * (0.09678418 + t * (-0.18628806 + t * (0.27886807 + t * (-1.13520398 + t * (1.48851587 + t * (-0.82215223 + t * (0.17087277))))))))))))
If z >= 0 Then
result = ans
Else
result = CDec(-1 * ans)
End If
erf = result
End Function
One Response to “Excel NORMDIST Function in VB6”
1 Idetrorce 15 December 2007 @ 9:26 pm
very interesting, but I don’t agree with you
Idetrorce
Comments: