Lab 7a Confidence Intervals in Excel(1)

STAT 1400 Lab 7: Confidence Intervals Name: _Khadija Abdikadir ________________ Published by Columbus State Community College, 550 E. Spring St., Columbus, Ohio, 43215. Copyright © 2022 Columbus State Community College. All rights reserved. OBJECTIVES: • To explore properties of confidence intervals related to confidence level and sample size. • To calculate confidence intervals for a variety of real-world applications and interpret the results. EXCEL PROCEDURES Retrieve LAB 7 DATA COMMUTING.XLXS from your blackboard course for STAT 1400. This data contains commuting information for randomly selected Franklin County residents as follows: Column A: Commuting distances for 100 randomly selected Franklin County residents (in miles) Column B: Commuting distances for 50 randomly selected Franklin County residents (in miles) Column C: Do you listen to the radio on your commute to work? (0=no, 1=yes) EFFECT OF CONFIDENCE LEVEL 1. To investigate how increasing a confidence level will affect the width of the interval, use Excel to produce 90%, 95% and 99% confidence intervals for the mean commuting distance for all Franklin County residents from our sample of size n = 100. a. In cell E2, find the sample mean by select the Formulas tab and clicking on the More Functions icon and selecting Statistical . Under the statistical menu select AVERAGE . You will see the Function Arguments dialog box open. In the Function Arguments dialog box select cells A2 through A101 for number 1 and click OK . The sample mean will now be displayed. b. In cell E3, find the sample standard deviation by following the steps in part a. above but now selecting STDEV.S from the Statistical menu. c. In cell E4, find the sample size by following the steps in part a. above but now selecting COUNT from the Statistical menu. d. In cell E6, find the margin of error by selecting the Formulas tab and clicking on the More Functions icon and selecting Statistical . Under the Statistical menu select CONFIDENCE.T . You will see the Function Arguments dialog box open. Enter the appropriate alpha level ( 0.10 for 90% confidence) and select cell E3 for the standard deviation and cell E4 for the sample size. e. In cell E7, find the lower confidence limit by using the formula =(E2 – E6). f. In cell E8, find the upper confidence limit by using the formula =(E2+E6). g. In cell E9, find the width confidence interval by using the formula =(E8 – E7) h. Copy/Paste cells D2-D9 and E2-E9 here for the 90% confidence interval. i. Next calculate the 95% and 99% confidence intervals by changing the first argument in cell E6 to appropriate alpha level (0.05 for 95% confidence and 0.01 for 99% confidence). j. Copy/Paste cells D2-D9 and E2-E9 here for the 95% and 99% confidence intervals.

Lab 7 Page 2 of 4 2. Fill in the table with the appropriate confidence interval information. Confidence Interval Information for Sample Size n = 100 Confidence Level Margin of Error Lower Conf. Limit Upper Conf. Limit Width of Interval 90% 1.875776647 18.48422335 22.23577665 3.751553294 95% 2.241609037 18.11839096 22.60160904 4.483218075 99% 2.967102062 17.39289794 23.32710206 5.934204124 3. Does a higher confidence level result in a narrower or wider interval? Explain. A higher confidence level results in a wider interval because as the confidence level increased from 90% to 95% to 99%, the width of the interval increases. The highest confidence level has the widest interval. 4. Interpret the 95% confidence interval based on the context of the information. We are 95% confident that the mean commuting distance for Franklin County residents is between 18.11839096 and 22.60160904. EFFECT OF SAMPLE SIZE 5. To investigate how the sample size affects the resulting confidence interval, use Excel to produce 90%, 95% and 99% confidence intervals for the mean commuting distance for all Franklin County residents from our sample of size n = 50 and compare these results to those you found in #2 using n=100. a. Copy cells E2 to E9 into cells F2 to F9. b. The sample mean, sample standard deviation, and sample size will now be displayed for our sample of size n = 50. c. In cell F6, change alpha level back to 0.10 (for 90% confidence) and make sure the standard deviation and sample size is F3 and F4 respectively. d. Copy/Paste cells D2-D9 and F2-F9 here for each interval. 6. Fill in the table below with the appropriate confidence interval information. Confidence Interval Information for Sample Size n = 50 Confidence Level Margin of Error Lower Conf. Limit Upper Conf. Limit Width of Interval

Lab 7 Page 4 of 4 . Confidence Interval: __ Z______________________ 10. Interpret the above confidence interval in the context of the problem. We are 95% confident that the population proportion of commuters who listen to the radio on their commute to work is between 0.621064272 and 0.798935728.