Calculating Confidence Intervals

Problem

You need to compute confidence intervals for certain estimates and you're not sure what support Excel provides for these calculations.

Solution

Excel offers a built-in function called CONFIDENCE that allows you to compute confidence intervals based on a normal distribution. Moreover, Excel provides functions such as NORMSINV and TINV that allow you to compute inverses for normal and Student's t-distributions, respectively. You can then use these results to compute confidence intervals.

Discussion

In Recipe 5.1, I showed you how to use Excel's built-in functions to compute summary statistics. I also showed you how to compute summary statistics using the Descriptive Statistics tool available from the Analysis ToolPak. In both cases, confidence intervals for the mean were computed and I pointed out that the confidence interval returned by Excel's CONFIDENCE function is different from that returned by the Descriptive Statistics tool. Figure 5-1 shows the different values.

The difference in the computed confidence interval is due to the fact that the CONFIDENCE function uses the inverse of a standard normal distribution to compute the confidence interval, whereas the Analysis ToolPak uses the inverse of Student's t-distribution. Instead of using the CONFIDENCE function, you can use the functions NORMSINV or TINV to compute confidence intervals; this gives you the option of using a normal distribution or Student's t-distribution, which is more suitable for small samples.

NORMSINV computes the inverse of a standard normal distribution with a mean of 0 and standard deviation equal to 1. In other words, NORMSINV returns the z-score corresponding to a given probability. The syntax for NORMSINV is =NORMSINV(probability). For a 95% confidence interval, you need to enter a probability of 1-0.05/2 or 0.975. Referring to the example shown in Figure 5-1, you can compute the confidence interval for the mean using the formula =H9*NORMSINV(0.975), where cell H9's value contains the standard error of the mean. This formula returns a confidence value of 0.015486, which agrees with the results returned using the CONFIDENCE function.

You can use TINV to compute the confidence interval based on Student's t-distribution. TINV computes the inverse of Student's t-distribution given a probability and the degrees of freedom characterizing the distribution. Again, referring back to the example in Figure 5-1, you can compute the confidence interval using the formula =H9*TINV(0.05,H20-1). In this case, the probability for 95% confidence is entered as 1-0.95 or 0.05. As before, H9 refers to the cell containing the standard error of the mean. Cell H20 contains the value returned by COUNT, and the number of degrees of freedom is computed by h20-1. The resulting confidence value is 0.015677, which agrees with the result returned by the Descriptive Statistics tool.

See Also

You can use the formulas discussed in this recipe to compute confidence intervals for other estimates as well. In Recipe 8.8, I show you how to compute confidence intervals for regression curves.