NOSMSDIST Limit

C

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
 
T

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
 
J

joeu2004

Tim C said:
Excel works with 16 digits and rounds to 15 digits for display.
Click to expand...

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).
Click to expand...

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

iandjmsmith

crapatmath said:
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
Click to expand...

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
 
C

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
 
I

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
 
J

joeu2004

crapatmath said:
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.
Click to expand...

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

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
 
Top