Calculating Confidence Intervals

2017-11-03 09:05:03

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.

Категории