Hi all,

is there a function for the cumulated standard normal distribution in Access, like the NormSDist in Excel? Or is there a way to use the excel function in access, e.g. by referencing the excel object library?

Thanks a lot for your help!

oliver

"=?Utf-8?B?b2xpdmVyMTk3Ng==?=" <(E-Mail Removed)>
wrote in news:0CE55CC9-E76B-4E95-B4D0-(E-Mail Removed):

> is there a function for the cumulated standard normal distribution in
> Access, like the NormSDist in Excel?



'Option Explicit

'***********************************************************************
'* Cumulative Standard Normal Distribution *
'* (this function provides similar result as NORMSDIST( ) on Excel) *
'* Source: http://www.geocities.com/WallStreet/9245/vba6.htm *
'***********************************************************************
Public Function SNorm2(z As Double) As Double

Const c1 = 2.506628
Const c2 = 0.3193815
Const c3 = -0.3565638
Const c4 = 1.7814779
Const c5 = -1.821256
Const c6 = 1.3302744
Dim w As Double, x As Double, y As Double

If z > 0 Or z = 0 Then
w = 1
Else
w = -1
End If

y = 1 / (1 + 0.231649 * w * z)

x = c6
x = y * x + c5
x = y * x + c4
x = y * x + c3
x = y * x + c2
SNorm2 = 0.5 + w * (0.5 - (Exp(-z * z / 2) / c1) * y * x)

' The one line version is too complex for most basic interpreters:
' SNorm = 0.5 + w * (0.5 - (Exp(-z * z / 2) / c1) * (y * (c2 + y * _
(c3 + y * (c4 + y * (c5 + y * c6))))))

End Function




// Hope that helps


Tim F