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

29 August 2007 | Software engineering, VB6 | Comments

3 Responses to “Excel NORMDIST Function in VB6”

  1. 1 Idetrorce 15 December 2007 @ 9:26 pm

    very interesting, but I don’t agree with you
    Idetrorce

  2. 2 Anne Marie 25 September 2008 @ 3:22 pm

    According to my testing it is also accurate - thanks :-)

    I have stricter code options than you, so had to convert your objects to doubles.

    Public Function NormDist(ByVal x As Double, ByVal mean As Double, ByVal std As Double) As Double

	NormDist = CDec(Phi3(x, mean, std))

End Function

Private Function Phi3(ByVal z As Double, ByVal mu As Double, ByVal sigma As Double) As Double

	Phi3 = CDec(Phi((z - mu) / sigma))

End Function

Private Function Phi(ByVal z As Double) As Double

	Phi = CDec(0.5 * (1 + erf(z / System.Math.Sqrt(2))))

End Function

Private Function erf(ByVal z As Double) As Double

	Dim t As Double
	Dim ans As Double
	Dim result As Double

	t = CDec(1 / (1 + (0.5 * System.Math.Abs(z))))
	ans = CDec(1 - (t * System.Math.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

  3. 3 Rizal 25 September 2008 @ 3:44 pm

    Thanks Anne Marie! Glad you found it useful :-D

Comments:

  1.  
  2.  
  3.