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 example, 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 s 5 1,000. After sampling 50 different hospitals 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% confi 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. Type 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 51000/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 5A22B2*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 5A21B2*NORMSINV(1-C2/2) in cell E2. Figure 6-3 shows the resulting 90% confi dence interval. • • Chapter 6 Statistical Inference 229 Figure 6-3 Using Excel to calculate a confidence interval

8. Close your workbook. You do not have to save the changes. Excel returns a 90% confidence band ranging from $5,267.38 to $5,732.62.

If you were trying to estimate the mean cost of this procedure for all hospitals, you could state that you were 90% confi dent that the cost was not less than $5,267.38 or more than $5,732.62.

Related Articles

Leave a Reply

Check Also
Back to top button