XL: NORMSINV Function May Return Incorrect Result

• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel 97 for Windows

SYMPTOMS

When you use the NORMSINV worksheet function in Microsoft Excel, the function may return an incorrect result of either -5000000 or 5000000.

CAUSE

This problem occurs if the probability argument of the NORMSINV function has a value greater than zero and less than 0.0000003, or a value greater than or equal to 0.9999997 and less than 1. For example, the following table lists formulas that return incorrect NORMSINV results.

   This formula                Returns
   ------------------------------------

   =NORMSINV(0.0000002)        -5000000
   =NORMSINV(0.000000299999)   -5000000
   =NORMSINV(0.9999997)         5000000 

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

The NORMSINV function in Microsoft Excel returns the inverse of the standard normal cumulative distribution, where the distribution has a mean of zero and a standard deviation of one. The NORMSINV function accepts a single argument, probability, which must have a value greater than zero and less than 1.

Although all values between zero and one are acceptable probability values, the NORMSINV function returns an incorrect result when the probability value is less than 0.0000003 or greater than or equal to 0.9999997.

Additional query words: 5.0 7.0 97 XL97

Keywords          : kbdta xlformula 
Version           : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a,97; winnt:5.0
Platform          : MACINTOSH WINDOWS winnt 
Issue type        : kbbug 

Last Reviewed: February 13, 1999