# Normdist Function - Equivilant in VB ?

Using the Normdist function in Excel.
Is there an equivilant function in VB or will I have to code it ?
And if I need to code it , whats the code ? (I will increase the question points if code is supplied ...)
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hm... I don't think there is such a function. In the Excel help it explains what exactly the NormDist function does though.
0
Commented:
Gee, it looks like you're going to have fun with this one :-(

http://www2.pitt.edu/~wpilib/statfaq/gaussfaq.html
0
Commented:
0
progCommented:
Are you interested in the cummulative distribution or the probability mass function?

Here's the function for the latter, eg =+NORMDIST(42,40,1.5,FALSE)

Private Function normdist(x, mu, rho) As Double

Dim pi As Double

pi = Atn(1#) * 4

normdist = 1 / (Sqr(2 * pi) * rho) * Exp(-(mu - x) ^ 2 / (2 * rho ^ 2))

End Function

The cummulative distribution requires a bit of of iteration using the trapezium rule, I could code it but accuracy might be a bit less than Excel.
0
progCommented:
'heres a group of functions giving a good approx

Private Sub Command1_Click()
MsgBox normdist(42, 40, 1.5, False)
MsgBox normdist(42, 40, 1.5, True)
End Sub

Private Function normdist(x, mu, rho, bool) As Double

Dim pi As Double

If Not bool Then
pi = Atn(1#) * 4

normdist = 1 / (Sqr(2 * pi) * rho) * Exp(-(mu - x) ^ 2 / (2 * rho ^ 2))

Else

normdist = normdistx(x, mu, rho)

End If

End Function

Private Function normdistx(x, mu, rho) As Double

Dim y As Double
Dim c As Long

For c = -4000 To 4000

If mu + rho * (c / 1000) > x Then
normdistx = y
Exit Function
End If
y = y + rho * normdist(mu + rho * (c / 1000), mu, rho, False) / 1000

Next

normdistx = y

End Function

0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

progCommented:
Here's a quicker method, based on a very good approximation for the cummulative distribution.

MsgBox normdist(42, 40, 1.5, False)
MsgBox normdist(42, 40, 1.5, True)

Private Function normdist(x, mu, rho, bool) As Double

Dim pi As Double

If Not bool Then
pi = Atn(1#) * 4

normdist = 1 / (Sqr(2 * pi) * rho) * Exp(-(mu - x) ^ 2 / (2 * rho ^ 2))

Else

normdist = normdistx(x, mu, rho)

End If

End Function

Private Function normdistx(x, mu, rho) As Double

Dim normal As Double
Dim t As Double
Dim p As Double

p = 0.33267

'Let t = 1 / (1 + pX)

'Where P = 0.33267

'then

'P(x) = 1 - Z(x) * [a_1*t + a_2*t^2 + a_3*t^3]

'Where

'a_1 = 0.4361836
'a_2 = -0.1201676
'a_3 = 0.937298

normal = (x - mu) / rho

t = 1 / (1 + p * normal)

normdistx = 1 - normdist(normal, 0, 1, False) * (0.4361836 * t + -0.1201676 * t * t + 0.937298 * t ^ 3)

End Function

0
Author Commented:
Deighton. If this works I will raise the points to 150.
Give me a day or so to check.
(I have hit bad sinus ...)
Thanks.
0
Author Commented: