normsdist function maximum

[jasontferrell]

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.

 

[Harlan Grove]

(e-mail address removed) 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.

 

[joeu2004]

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.)

 

[Harlan Grove]

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.

 

[joeu2004]

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)).

 

[jasontferrell]

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.