Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOSMSDIST Limit
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOSMSDIST Limit
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOSMSDIST Limit
"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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOSMSDIST Limit
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOSMSDIST Limit
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 " 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOSMSDIST Limit
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 -- Ian Smith ------------------------------------------------------------------------ Ian Smith's Profile: http://www.excelforum.com/member.php...o&userid=27612 View this thread: http://www.excelforum.com/showthread...hreadid=498492 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOSMSDIST Limit
"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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NOSMSDIST Limit
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 x1.5. Alternately, use cdf_normal(-x) from Ian's excellent VBA library http://members.aol.com/iandjmsmith/Examples.xls Jerry " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum limit and marking | Excel Worksheet Functions | |||
Is there a limit to number of successive IF-THEN statements? | Excel Discussion (Misc queries) | |||
Make the row limit of excel flexible -not fixed at 65536. | Excel Discussion (Misc queries) | |||
How do I add a limit line to a chart, so I can see where a data p. | Charts and Charting in Excel | |||
Any way to get around the 240-character line limit on text output? | Excel Discussion (Misc queries) |