[Bsims]

I recognize this isn't a straight forward sports related question, but it came out of another thread. It involves the use of the NORMSDIST in Excel. I need to replicate this function in a PowerBasic program. From Excel, the following formula returns the correct value;

f(z)= (1/sqrt(2*pi)) * e^(-z^2/2)

I used the function in a spreadsheet and got the following correct answer;

Test Value 1.3333
normsdist results 0.9087833

To use it in a program I attempted to replicate the function by dividing it into two factors and multiplying them as follows;

2*pi 6.28318
square root 2.5066272
fac1 0.3989424

value squared 1.7776889
divide by 2 0.8888444
negative -0.8888444
fac2 (EXP) 0.4111306

product fac1*fac2 0.1640174

This does not match the correct answer.

OK someone with sharp eyes tell me where I've gone wrong. I've looked at this for 3 hours and can't see it.

[Ganchrow]

f(z)= (1/sqrt(2*pi)) * e^(-z^2/2)

This equation corresponds to the standard normal probability density function (the "pdf"), while NORMSDIST() corresponds to the standard normal cumulative distribution function (the "cdf").

The former represents the value of the function at any given point, while the latter represents the probability that a random variable with a standard normal distribution will have the specified value or less (i.e., it's the area under the curve of the pdf evaluated from -∞ through the point in question).

In other words:

The pdf is analogous to the probability mass function (or "pmf") of a discrete random variable. So while the pmf of the binomial distribution would tell you the probability of getting exactly 5 heads out of 10 flips of a coin (~24.61%), the cdf would tell you the probability of getting at most 5 heads out of 10 flips of a coin (~62.30%).

Similarly, the cdf of the standard normal would tell you the probability of being at most 1.3333 standard deviations above the mean.

Unfortunately, no closed form solution exists for the above integral. Following, however, is a "pretty good" approximation implemented in VBA:

Public Function NormProb(x As Double) As Double
' Note: Approximation not sufficiently accurate
' for targeting of nuclear warheads.
' Please use with caution.

  Dim t As Double
  Const b1 As Double = 0.31938153
  Const b2 As Double = -0.356563782
  Const b3 As Double = 1.781477937
  Const b4 As Double = -1.821255978
  Const b5 As Double = 1.330274429
  Const p As Double = 0.2316419
  Const c As Double = 0.39894228

  If x >= 0 Then
      t = 1# / (1# + p * x)
      NormProb = (1# - c * Exp(-x * x / 2#) * t * _
      (t * (t * (t * (t * b5 + b4) + b3) + b2) + b1))
  Else
      t = 1# / (1# - p * x)
      NormProb = (c * Exp(-x * x / 2#) * t * _
      (t * (t * (t * (t * b5 + b4) + b3) + b2) + b1))
  End If
End Function

NORMPROB(1.3333) returns a value of 0.90878325 in Excel, while NORMSDIST returns a value of 0.90878331, off by about 0.0000068%.

[Bsims]

Thank you kind sir. You not only pointed out the error in my understanding of the formula, but gave me the code I need.

I do truly appreciate it.