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.Īlso, the search must be sufficiently refined that it "homes in" on an appropriate answer. The accuracy of the NORMSINV function depends on two factors. On the Accuracy of Statistical Distributions in Microsoft Excel 97, Computational Statistics and Data Analysis, 26, 375-377, 1998. Entries in column C are taken from Table 5 in Knusel, L. Results in Excel 2002 will be improved over earlier versions.Ī19:C24 show values of NORMSINV(p) for your current version of Excel for increasingly smaller values of p. Results in Excel 2003 and in later versions of Excel will be improved over those in Microsoft Excel 2002. However, errors appear only after a large enough number of decimal places that they are unlikely to be of concern to a user. If you re-format these entries to show many more decimal places, you might notice that the result is not exact because of imprecision of NORMSDIST, NORMSINV or both. It should be the case that z = NORMSINV(NORMSDIST(z)). Values in C3:C11 verify the reciprocal relationship between a function and its inverse, in this case between NORMSDIST and NORMSINV. These cutoff values are frequently used for one-tailed and two-tailed hypothesis tests respectively when the probability of rejecting the null hypothesis if true is set at 0.05. 95 and probability 0.025 lies in the right tail above 1.965996 because NORMSDIST(1.965996) =. Probability 0.05 lies in the right tail above 1.644485 because NORMSDIST(1.644485) =. 933, you expect NORMSINV(0.975) to be much closer to 2 than to 1.5 and it is at 1.965996.Īs an aside, past users of statistical tables for statistical hypothesis testing and computation of confidence intervals might recognize the values in A17:B18. Finally, NORMSINV(0.975) must also be between 1 and 1.5 according to A10:B11. Also, NORMSINV(0.95) in B17 must be greater than 1.5 and less than 2.0 as revealed by entries in A9:B10 and the answer, 1.644485, is within this range. Analogously, NORMSINV(0.9) in B16 must be greater than 1 and less than 1.5 as revealed by entries in A8:B9 and the answer, 1.28155, is indeed within this range. The computation of NORMSINV in B15 yields the value 0.25335, this is indeed greater than 0.2 and less than 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. Entries in A4:B5 indicate that the appropriate value of z must be between 0.2 and 0.4. In cell B15, you want that value of z where NORMSDIST(z) = 0.6. Because 0.5 in cell A14 appears in cell B3, it follows that the appropriate z value that yields NORMSDIST = 0.5 is 0 and NORMSINV(0.5) returns 0. You may want to format columns B and C for consistent readability (such as Numbers with 5 decimal places).Ĭells A1:B11 give a "mini-Normal table" similar to what you might have seen in a statistics text except that such tables contain rows for many values of z between those in A2:A11 and higher than the value 2.5 in A11.Ĭells A13:B24 illustrate the use of NORMSINV. In Microsoft Office Excel 2007, click the Home tab, click Format in the Cells group, and then click AutoFit Column Width.Ĭolumn on the Format menu, and then click With the pasted range still selected, use one of the following procedures, as appropriate for the version of Excel that you are running: Note After you paste this table into your new Excel worksheet, click the Paste Options button, and then click Match Destination Formatting.
If NORMSDIST(z) returns p, then NORMSINV(p) returns z.Ĭreate a blank Excel worksheet, copy the following table, select cell A1 in your blank Excel worksheet, and then paste the entries so that the table fills cells A1:C24 in your worksheet. NORMSINV and NORMSDIST are related functions. Because p corresponds to a probability, it must be greater than 0 and less than 1. A standard normal random variable has mean 0 and standard deviation 1 (and also variance 1 because variance = standard deviation squared). NORMSINV(p) returns the value z such that, with probability p, a standard normal random variable takes on a value that is less than or equal to z. To compare results of the function for Excel 2003 and later versions of Excel with results of the function when it is used in earlier versions of Excel To describe the NORMSINV function in Microsoft Office Excel 2003 and in later versions of Excel The purposes of this article are as follows:
0 kommentar(er)
