NORMSDIST() and NORMSINV()
 

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?

NORMSDIST() and NORMSINV()
 

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

Schizoid Man wrote:
: 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?

NORMSDIST() and NORMSINV()
 

There is a world-class library of VBA probability functions freely available at
http://members.aol.com/iandjmsmith/examples.xls

Jerry

"Schizoid Man" wrote:

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?

NORMSDIST() and NORMSINV()
 

Schizoid Man -

There were many improvements in Excel 2003. For more info, see

Description of improvements in the statistical functions in Excel 2003 and
in Excel 2004 for Mac
http://support.microsoft.com/default...product=xl2003

- Mike
www.mikemiddleton.com

"Schizoid Man" wrote in message
...
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?