228 Fundamentals of Statistics
For example, if you want to construct a confi dence interval around the
sample average that will capture the value of m 99.9% of the time, calculate
the sample average 63. 3 times the standard error. Admittedly, this will
tend to be a very large interval.EXCEL TIPS
To calculate the value of z 12 a/ 2 with Excel, use the function
NORMSINV(x), where x 512 a/2.
To fi nd the probability associated with a z test statistic, use the
function NORMSDIST(z), where z is the z test statistic.Calculating the Confi dence Interval with Excel
You can use Excel’s functions to calculate a confi dence interval if you know
the standard deviation of the underlying probability distribution. For exam-
ple, suppose you are conducting a survey on the cost of a medical procedure
as part of research on health care reform. The cost of the procedure follows
the normal distribution, where s51, 000. After sampling 50 different hos-
pitals at random, you calculate the average cost to be $5,500. What is the
90% confi dence interval for the value of m—the mean cost of all hospitals?
(That is, how far above and below $5,500 must you go to say, “I’m 90% con-
fi dent that the mean cost of this procedure lies in this range”?)To calculate the 90% confi dence interval:1 Start Excel and open a blank workbook.
2 Ty p e Average in cell A1, Std. Error in cell B1, Alpha in cell C1,
Lower in cell D1, and Upper in E1.
3 Click cell A2 and type 5500 (the observed sample average).
4 Type 5 1000/sqrt(50) in cell B2. This is the standard error of the
sample average.
5 Type 10% in cell C2. This is the alpha value for your confi dence
interval.
6 Type 5 A2 2 B2*NORMSINV(1-C2/2) in cell D2. Note that we use the
NORMSINV(0.95) function to return the z value from the standard
normal distribution.
7 Type 5 A2 1 B2*NORMSINV(1-C2/2) in cell E2. Figure 6-3 shows the
resulting 90% confi dence interval.