staffiidba
asked on
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 ...)
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 ...)
Hm... I don't think there is such a function. In the Excel help it explains what exactly the NormDist function does though.
Gee, it looks like you're going to have fun with this one :-(
http://www2.pitt.edu/~wpilib/statfaq/gaussfaq.html
http://www2.pitt.edu/~wpilib/statfaq/gaussfaq.html
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
Give me a day or so to check.
(I have hit bad sinus ...)
Thanks.
ASKER
Adjusted points to 100