NORMSDIST() and NORMSINV()

S

Schizoid Man

Currently, I have a spreadsheet in which I need to calculate the
cumulative distribution function of the standard z, as well the inverse
of the cdf for p(x).

For the sake of speed(?), I am using Application.NormSDist and
Application.NormSInv.

NormSDist is respectable, but the accuracy of NormSInv is laughable. I
was thinking of substituting both functions with custom methods.

For NormSDist() I was thinking of using the algorithm in Numerical
Recipes, and for NormSInv() I was thinking of using P.J. Acklam's algorithm.

The function that I am evaluating is of the form: (all variables are double)
y = NormSDist( (NormSInv(x) - a * m) / Sqr (1 - a * a) )

and I would be evaluating this function approximately 6000 times.

Would the substitution of the canned functions with custom ones
hamstring my project?
 
V

vandenberg p

Hello:

There is a big difference between Excel 2003 and all of the other versions.
If you are not using 2003 then your results are not surprising. Here
is a link to discussion of the issue:

http://support.microsoft.com/gp/xl2003statfunc

Pieter Vandenberg

: Currently, I have a spreadsheet in which I need to calculate the
: cumulative distribution function of the standard z, as well the inverse
: of the cdf for p(x).

: For the sake of speed(?), I am using Application.NormSDist and
: Application.NormSInv.

: NormSDist is respectable, but the accuracy of NormSInv is laughable. I
: was thinking of substituting both functions with custom methods.

: For NormSDist() I was thinking of using the algorithm in Numerical
: Recipes, and for NormSInv() I was thinking of using P.J. Acklam's algorithm.

: The function that I am evaluating is of the form: (all variables are double)
: y = NormSDist( (NormSInv(x) - a * m) / Sqr (1 - a * a) )

: and I would be evaluating this function approximately 6000 times.

: Would the substitution of the canned functions with custom ones
: hamstring my project?
 
Top