How is Excel used in statistics

Excel statistical functions: NORMSINV

This article is a translation of the following Microsoft Knowledge Base article:
826772 Excel Statistical Functions: NORMSINV

Summary

This article has the following objectives:

  • Description of the NORMSINV function in Microsoft Office Excel 2003 and in later versions of Excel

  • Demonstrates how to use this feature

  • Comparison of the results of the function for Excel 2003 and later versions of Excel with results of the function as used in earlier versions of Excel

Additional Information

NORMSINV (p) returns the value z such that, with a probability p, a standard normally distributed random variable assumes a value that is less than or equal to z. A standard normally distributed random variable has a mean value of 0 and a standard deviation of 1 (and a variance of 1, since variance = standard deviation squared).

syntax

Here p is a numeric value. Since p corresponds to a probability, p must be greater than 0 and less than 1.

Usage example

NORMSINV and NORMSDIST are related functions. If NORMSDIST (z) returns p, NORMSINV (p) returns z.

Create a blank Excel worksheet and copy the following table. Select cell A1 on the blank Excel worksheet, and then paste the entries so that the table fills cells A1: C24 in your worksheet.

z

NORMSDIST (z)

NORMSINV (NORMSDIST (z))

0

= NORMSDIST (A3)

= NORMSINV (B3)

0,2

= NORMSDIST (A4)

= NORMSINV (B4)

0,4

= NORMSDISTANCE (A5)

= NORMSINV (B5)

0,6

= NORMSDIST (A6)

= NORMSINV (B6)

0,8

= NORMSDIST (A7)

= NORMSINV (B7)

1

= NORMSDIST (A8)

= NORMSINV (B8)

1,5

= NORMSDIST (A9)

= NORMSINV (B9)

2

= NORMSDIST (A10)

= NORMSINV (B10)

2,5

= NORMSDIST (A11)

= NORMSINV (B11)

p

NORMSINV (p)

0,5

= NORMSINV (B3)

0,6

= NORMSINV (A15)

0,9

= NORMSINV (A16)

0,95

= NORMSINV (A17)

0,975

= NORMSINV (A18)

correct NORMSINV (p)

0,001

= NORMSINV (A19)

-3,09023

0,0001

= NORMSINV (A20)

-3,71902

0,00001

= NORMSINV (A21)

-4,26489

0,000001

= NORMSINV (A22)

-4,75342

0,0000003

= NORMSINV (A23)

-4,99122

0,0000002

= NORMSINV (A24)

-5,06896

Note: After pasting this table into a new Excel worksheet, click on Paste options and then on Apply the formatting of the target cells. Still leaving the pasted area highlighted, do one of the following, depending on the version of Excel you are using:

  • In Microsoft Office Excel 2007, click the tab begin, click in the group Cells on format, and then click Automatically adjust column width.

  • In Excel 2003, point to the menu format on column, and then click Optimal width.

It is recommended that columns B and C be formatted so that they are displayed consistently (e.g. "numbers" with 5 decimal places).

Cells A1: B11 make a small normal distribution table, similar to what you may know from statistical texts, except that such tables contain rows for many values ​​of z that are between those in A2: A11 and values ​​that are higher than that Value 2.5 in A11.

Cells A13: B24 show the use of NORMSINV. Since 0.5 from cell A14 is displayed in cell B3, it follows that the corresponding value z that results in NORMSDIST = 0.5 is 0 and NORMSINV (0.5) returns 0. We want to display the value of z in cell B15, where NORMSDIST (z) = 0.6. Entries in A4: B5 indicate that the corresponding value of z must be between 0.2 and 0.4. It must be greater than 0.2 because NORMSDIST (0.2) is less than 0.6; and it must be less than 0.4 because NORMSDIST (0.4) is greater than 0.6. The calculation of NORMSINV in B15 gives the value 0.25335, and this is actually greater than 0.2 and less than 0.4. Similarly, NORMSINV (0.9) in B16 must be greater than 1 and less than 1.5, as the entries in A8: B9 show. The result of 1.28155 is actually within this range. NORMSINV (0.95) in B17 must also be greater than 1.5 and less than 2.0, as the entries in A9: B10 show. The result of 1.644485 is actually within this range. Finally, NORMSDIST (0.975) must be between 1 and 1.5 according to A10: B11. Since 0.975 is closer to 0.977 than 0.933, one would expect NORMSIN (0.975) to be closer to 2 than 1.5. In fact, the value is 1.965996.

As an aside, previous users of statistical tables for testing statistical hypotheses and calculating confidence intervals may recognize the values ​​in A17: B18. The 0.05 probability is above 1.644485 on the right because NORMSDIST (1.644485) = 0.95, and the 0.025 probability is above 1.965996 on the right because NORMSDIST (0.965996) = 0.975 . These truncation values ​​are often used for one-tailed and two-tailed hypothesis testing, respectively, when the probability of rejecting the null hypothesis if the assumption is TRUE is set to 0.05.

The values ​​in C3: C11 check the mutual relationship between a function and its quantile, in this case between NORMSDIST and NORMSINV. It should be such that z = NORMSINV (NORMSDIST (z)). If you reformat these entries so that they show significantly more decimal places, you may find that the result is not entirely accurate due to inaccuracies in NORMSDIST and / or NORMSINV. However, errors do not appear until after such a large number of decimal places that they should hardly be a problem for the user. Results in Excel 2003 and later versions of Excel have been improved compared to the results in Microsoft Excel 2002. The results in Excel 2002 are already more precise than the results in versions prior to Excel 2002.

A19: C24 shows values ​​of NORMSINV (p) for your current version of Excel for progressively smaller values ​​of p. The entries in column C come from Table 5 of the article On the Accuracy of Statistical Distributions in Microsoft Excel 97 by Leo Knüsel in Computational Statistics and Data Analysis, 26, 1998, 375-377 .

Results in earlier versions of Excel

The accuracy of the NORMSINV function depends on two factors. Because the calculation of the NORMSINV function uses a systematic search over the returned values ​​of the NORMSDIST function, the accuracy of the NORMSDIST function is critical.

The search must also be sufficiently precise that it is aimed at a suitable answer. To use the table of normal probability distribution as it is in the textbook as an analogy, the entries in the table must be very precise. Also, there must be enough entries in the table so that you can find the appropriate row in the table that returns a probability that is correct given a certain number of decimal places.

Of course, if you are using a computer program, you do not need to create and store such a large table. Instead, individual entries are created as needed while the search is performed through the "table". However, the table must be perfectly accurate, and the search must be made far enough so that it does not stop prematurely on an answer (or row in the table) that has a corresponding probability that is too far from the value of pthat you use in the call to NORMSINV (p) use. Therefore, the NORMSINV function has been improved as follows:

  • The accuracy of NORMSDIST function has been improved.

  • The search process has been improved so that the precision has been increased.

The NORMSDIST function was improved in Excel 2003 and in later versions of Excel. Improved clarifications of the search process were introduced with Excel 2002. An article by Leo Knüsel (see Note 2) discusses the numerical shortcomings of the NORMSINV function in Microsoft Excel 97. These shortcomings, documented by Knüsel, persisted until the search improvements in Excel 2002 made the results much more precise, although they still did not match Knüsel's results.

Note 2: Leo Knüsel On the Accuracy of Statistical Distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 1998, 375-377.

Results in Excel 2003 and later versions of Excel

The method for calculating the NORMSINV function in Excel 2003 and later versions of Excel takes advantage of the improvements made to the NORMSDIST function in Excel 2003 and later versions of Excel.

For more information, see the following Microsoft Knowledge Base article:

827369 Description of the NORMSDIST function in Excel Results should always agree with Knüsel's results according to the displayed decimal places.

Résumé

Typically, inaccuracies in earlier versions of Excel occur for extremely small or extremely large values ​​of p in NORMSINV (p) on. The values ​​in Excel 2003 and later versions of Excel are much more precise.

The article on the NORMSDIST function mentions that most users will most likely not be affected by the inaccuracies in the NORMSDIST function that appear in earlier versions of Excel. As a result, the inaccuracies in the NORMSINV function are unlikely to affect Excel 2002 users because Excel 2002 has made improvements to make the search more precise. However, users of earlier versions of Excel (prior to Excel 2002) could experience problems due to the inaccuracy of the NORMSINV function, as both the NORMSDIST function and the search process could be improved in these earlier versions.

Please note: This article is a translation from English. It is possible that subsequent changes or additions to the original English article are not taken into account in this translation. The information in this article is based on the English language product version (s). The correctness of this information in connection with product versions in other languages ​​has not been tested as part of this translation. Microsoft provides this information without guarantee for correctness or functionality and does not guarantee the completeness or correctness of the translation.