+ Reply to Thread
Results 1 to 4 of 4

NORMSDIST() and NORMSINV()

  1. #1
    Schizoid Man
    Guest

    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?

  2. #2
    vandenberg p
    Guest

    Re: 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 <[email protected]> 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?

  3. #3
    Jerry W. Lewis
    Guest

    RE: 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?


  4. #4
    Mike Middleton
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1