normsdist function maximum

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jasontferrell@gmail.com - 15 Jun 2007 21:31 GMT

I was just doing some investigation and found that the normsdist
function errors when given a value higher than 2147483647. I worked
on this when doing some calculations for options based on Black
Scholes and I thought it might be helpful for others running into
problems with the 1.#INF infinity code from Excel.

Reply to this Message

Harlan Grove - 15 Jun 2007 23:07 GMT

jasontferr...@gmail.com wrote...

>I was just doing some investigation and found that the normsdist
>function errors when given a value higher than 2147483647. I worked
>on this when doing some calculations for options based on Black
>Scholes and I thought it might be helpful for others running into
>problems with the 1.#INF infinity code from Excel.

Excel isn't a reliable tool for simulation.

Anyway, 2147483647 is the largest signed 32-bit integer, and in this
particular context means over 2 billion standard deviations from the
mean. So =NORMSDIST(2147483647+ABS(anothernumber)) *should* give the
same result as =NORMSDIST(2147483647), namely, 1, but Excel's
implementation of lots of statistical functions is sloppy and/or
shortsighted, and this is just another example of that.

Reply to this Message

joeu2004 - 15 Jun 2007 23:36 GMT

On Jun 15, 1:31 pm, jasontferr...@gmail.com wrote:

> I was just doing some investigation and found that the normsdist
> function errors when given a value higher than 2147483647.

Hmm, the largest signed 32-bit value. I'm a little surprised because
"z" seems to be treated as a floating-point number (normsdist(1)
differs from normsdist(1.1), for example). I can only guess that
there is an optimization (table lookup?) when "z" can be expressed as
an integer; and that algorithm fails to anticipate input larger than
2^31-1.

And for good reason: normsdist stops returning useful
(discriminating) results when "z" exceeds about 8.02696.

> I worked on this when doing some calculations for options
> based on Black Scholes

I suspect you have a computation error or you are applying the BS
algorithm ;-) incorrectly. It is pointless to go further out on the
standard normal distribution curve than a "z" of 8 or 9, where the
cumulative probability is nearly 100%. (I'm sure many would say it is
pointless to go even that far out.)

Reply to this Message

Harlan Grove - 16 Jun 2007 00:34 GMT

joeu2004 <joeu2...@hotmail.com> wrote...
...

>And for good reason: normsdist stops returning useful
>(discriminating) results when "z" exceeds about 8.02696.

...

True, but it should then return 1 rather than #NUM!. That it returns
an error is simply piss poor programming.

Reply to this Message

joeu2004 - 16 Jun 2007 07:15 GMT

> joeu2004 <joeu2...@hotmail.com> wrote...
> And for good reason: normsdist stops returning useful
> (discriminating) results when "z" exceeds about 8.02696.
>
> True, but it should then return 1 rather than #NUM!. That it returns
> an error is simply piss poor programming.

I didn't say or imply otherwise. I even offered a conjecture about
the precise internal programming error.

My comment was intended to help the OP uncover __his__ error, or so I
presume. Even if he does not have bona fide error himself, it should
not be difficult for him to program around it by ensuring that the
parameter to NORMSDIST() is constrained to a reasonable amount, e.g.
NORMSDIST(min(10,z)).

Reply to this Message

jasontferrell@gmail.com - 25 Jun 2007 15:22 GMT

Yes, that's essentially what I did. I like clean, brief coding, so I
hated to add a constraint to say when z is greater than some number,
do something different. I guess that like joeu2004, I expected it to
return 1 for very large values of z, at least up to the "double"
variable input that it takes. If it only accepts a "long" input, it
should say so.

> > joeu2004 <joeu2...@hotmail.com> wrote...
> > And for good reason: normsdist stops returning useful
[quoted text clipped - 11 lines]
> parameter to NORMSDIST() is constrained to a reasonable amount, e.g.
> NORMSDIST(min(10,z)).

Reply to this Message

Rate this thread: