Upper and lower limits define your confidence in your estimations. Learn how to use Excel to calculate these limits.
- [Instructor] Now we learn how to calculate confidence intervals. If we're given the sample mean, the sample standard deviation, the alpha and the sample size, what we can do is use the sampling distribution of the mean to find the one minus alpha confidence interval. Here's an example: A manufacturer wants to estimate with 95% confidence the average amount of time his employees take to complete a particular manufacturing process. The a sample of 100 employees, the sample mean is 75 minutes, and the standard deviation is 30 minutes. What's the 95% confidence interval? In other words, what are the upper and lower 95% confidence limits? Here's the solution. The standard error of the mean is s divided by the square root of N, or 30 divided by the square root of 100, 30 divided by 10 or three. Now, N is greater than 30, so we can use the standard normal distribution as the sampling distribution of the mean. We have to find the cutoffs for .025 in the right rail of the distribution and for .025 in the left tail of the distribution. Those cutoffs are 1.96 in the right tail and minus 1.96 in the left tail. Now we can find those values with the table of the standard normal distribution or the Excel's NORM.S.INV function which I'll show you in a little while. But here's how those confidence limits look in the standard normal distribution. And here's how they look with the sample score scale on the x-axis. It's the 95% confidence limits and the standard normal distribution. 1.96 cuts off 2.5% of the area on the right side and minus 1.96 cuts of 2.5% of the area on the left side. Now, just eyeballing the scale of the sample scores minus 1.96 translates to a little more than 69 and plus 1.96 translates to little less than 81. So finishing up, the upper confidence limit is 75 plus 1.96 times three equals 80.88. The lower confidence limit is 75 minus 1.96 times three, 69.12. So the 95% confidence interval is 69.12 to 80.88. What about with a smaller sample? Imagine that the manufacturer sampled 16 employees instead of 100. Here's how to calculate the confidence intervals. The standard error of the mean is s over the square root of N, 30 over the square root of 16 which is 30 over four, and that's 7.5. N is 16, so we use the t-distribution with 16 minus 1 or 15 degrees of freedom as the sampling distribution of the mean. We have to find the cutoffs for .025 in the right tail of the distribution and for .025 in the left tail of the distribution. Those cutoffs are minus 2.1314 and plus 2.1314. You could use a table of the t-distribution or Excel's T.INV function. Here's how those 95% confidence limits look in the t-distribution for degrees of freedom equal 15. The upper confidence limit is 75 plus 2.1314 times 7.5, 90.99. The lower confidence limit is 75 minus 2.1314 times 7.5 or 59.01. So the 95% confidence interval is 59.01 to 90.99. And here's how that looks with the sample score scale on the x-axis. Here's a spreadsheet that shows you how to calculate those critical values. Alpha is in cell A1. And the critical value for alpha and the standard normal distribution will go into cell B1. We use NORM.S.INV to calculate that critical value. This function takes the probabilities that you give it, returns a z score that cuts off that probability to the left in the standard normal distribution. So on cell B2, we type equal norm.s.inv. And for probability, click cell A2 and slash two to divide that into .025 and 025. Enter and there's the critical value. It's a similar process for the t-distribution but you have to supply value for degrees of freedom. Click cell D2, and equals t.inv, probability, select A2. Once again divide by two, comma, for degrees of freedom, select C2 and minus one, close paren, Enter, and there's your critical value. And those are our left tail critical values for the standard normal distribution and for the t-distribution for the problem we just solved and that's how Excel can help you find confidence intervals.
- Using Excel functions and graphics
- Data types and variables
- Calculating probability
- Mean, median, and mode
- Calculating variability
- Organizing and graphing distributions
- Visualizing normal distributions
- Sampling distributions
- Making estimations
- Testing hypotheses: mean, z- and t-testing, and more
- Analyzing variance
- Performing repeated measure testing
- Regression testing
- Hypotheses testing with correlation