NOSMSDIST Limit

[crapatmath]

Hi All,

Why is
1-NORMSDIST(7.8) = 3.10862e-15
while
1-NORMSDIST(7.9) = 0

Is there a limit to how far out the Gaussian PDF curve you can go before
Excel rounds to zero?

Thanks

Barry

[Tim C]

Excel works with 16 digits and rounds to 15 digits for display.

Once NORMSDIST(x) (or anything) gets above 15 and a half nines, it's 1. It
happens at about NORMSDIST(7.87375095140855).

Tim C


"crapatmath" <[email protected]> wrote in message
news:[email protected]...
> Hi All,
>
> Why is
> 1-NORMSDIST(7.8) = 3.10862e-15
> while
> 1-NORMSDIST(7.9) = 0
>
> Is there a limit to how far out the Gaussian PDF curve you can go before
> Excel rounds to zero?
>
> Thanks
>
> Barry
>

[[email protected]]

"Tim C" wrote:
> Excel works with 16 digits and rounds to 15 digits for display.

Good answer. Just some minor nitpicks ....

Actually, it is limited by the IEEE 754 standard floating-point
format. All computation is limited by 52 binary "significant"
digits (mantissa), which is approximately 15.65 decimal
digits. Consequently, decimal representation is accurate
to only 15 significant digits.

> Once NORMSDIST(x) (or anything) gets above 15 and a
> half nines, it's 1. It happens at about
> NORMSDIST(7.87375095140855).

Well, __that__ happens around NORMSDIST(8.02695035989519).

However, the OP is computing 1 - NORMDIST(x). That
requires one extra significant digit to compute. That
expression becomes zero at NORMSDIST(7.87375095170324)
on my computer (Intel Pentium, Excel 2003 revision
11.5612.5606).

Caveat: The actual point at which we hit the IEEE limit
might vary depending on Excel revision and CPU type
(e.g, Intel v. Mac). Basically, it might vary depending on
the actual implementation and machine language
compilation of the NORMDIST() function.

[crapatmath]

Thanks for the info - is there any way around this - or am I stuck with this
limit regardless of what program I use - I tried Matlab on a Sun workstation
and it did a similar thing.

Barry


"[email protected]" wrote:

> "Tim C" wrote:
> > Excel works with 16 digits and rounds to 15 digits for display.
>
> Good answer. Just some minor nitpicks ....
>
> Actually, it is limited by the IEEE 754 standard floating-point
> format. All computation is limited by 52 binary "significant"
> digits (mantissa), which is approximately 15.65 decimal
> digits. Consequently, decimal representation is accurate
> to only 15 significant digits.
>
> > Once NORMSDIST(x) (or anything) gets above 15 and a
> > half nines, it's 1. It happens at about
> > NORMSDIST(7.87375095140855).
>
> Well, __that__ happens around NORMSDIST(8.02695035989519).
>
> However, the OP is computing 1 - NORMDIST(x). That
> requires one extra significant digit to compute. That
> expression becomes zero at NORMSDIST(7.87375095170324)
> on my computer (Intel Pentium, Excel 2003 revision
> 11.5612.5606).
>
> Caveat: The actual point at which we hit the IEEE limit
> might vary depending on Excel revision and CPU type
> (e.g, Intel v. Mac). Basically, it might vary depending on
> the actual implementation and machine language
> compilation of the NORMDIST() function.
>

[Ian Smith]

If it can be avoided, it is a bad idea to calculate 1-val where val is close to 1. It is guaranteed to lead to serious cancellation errors eventually, as in the examples you give.

If you use NORMSDIST(-7.8) = 3.09536E-15 and NORMSDIST(-7.9) = 1.39452E-15 you get accurate answers.


Ian Smith

[[email protected]]

"crapatmath" wrote:
> is there any way around this - or am I stuck with this
> limit regardless of what program I use - I tried Matlab
> on a Sun workstation and it did a similar thing.

Well, not as long as the program relies on IEEE formats
for floating-point arithmetic.

But why would you want to? What application do you
have where the difference between 1E-15 and 0 makes
a difference?

You are talking about over 7 sd on the std norm curve
-- "a frontier where no man has gone before" ;-).

If you are worried about not being asymptotic to the
axis in appearance, blame "pixel resolution" ;-). If
you are worried about more than one z-value having
exactly the same result in a table, put an arbitrary
limit on z -- something like 7.8 should suffice ;-).

Your curiosity is understandable. But I cannot imagine
any practical need for more accuracy at that limit. I
am curious and would appreciate some enlightenment.

[[email protected]]

crapatmath wrote:
> Hi All,
>
> Why is
> 1-NORMSDIST(7.8) = 3.10862e-15
> while
> 1-NORMSDIST(7.9) = 0
>
> Is there a limit to how far out the Gaussian PDF curve you can go before
> Excel rounds to zero?
>
> Thanks
>
> Barry

If it can be avoided, it is a bad idea to calculate 1-val where val is
close to 1. It is guaranteed to lead to serious cancellation errors
eventually, as in the examples you give.

If you use NORMSDIST(-7.8) = 3.09536E-15 and NORMSDIST(-7.9) =
1.39452E-15 you get accurate answers.


Ian Smith

[Jerry W. Lewis]

Your answer presumes that the OP has Excel 2003. For earlier versions, the
lower tail of NORMSDIST is not accurate this far out. In versions prior to
2003, use
=CHIDIST(x^2,1)/2
instead of either 1-NORMSDIST(x) or NORMSDIST(-x) for x>1.5.

Alternately, use cdf_normal(-x) from Ian's excellent VBA library
http://members.aol.com/iandjmsmith/Examples.xls

Jerry

"[email protected]" wrote:

> crapatmath wrote:
> > Hi All,
> >
> > Why is
> > 1-NORMSDIST(7.8) = 3.10862e-15
> > while
> > 1-NORMSDIST(7.9) = 0
> >
> > Is there a limit to how far out the Gaussian PDF curve you can go before
> > Excel rounds to zero?
> >
> > Thanks
> >
> > Barry
>
> If it can be avoided, it is a bad idea to calculate 1-val where val is
> close to 1. It is guaranteed to lead to serious cancellation errors
> eventually, as in the examples you give.
>
> If you use NORMSDIST(-7.8) = 3.09536E-15 and NORMSDIST(-7.9) =
> 1.39452E-15 you get accurate answers.
>
>
> Ian Smith
>
>