Confidence Intervals In Excel Worksheet

In this lab we will use Excel to construct a confidence intervals for the true mean mileage per gallon (MPG) for a particular SUV.

The following data represent the mileage per gallon (mpg) of gas for a particular SUV:

17.6 17.7 18.1 22.0 17.0 19.4 18.9 17.4 21.0 19.2
18.3 19.1 20.7 16.7 19.4 18.2 18.4 17.1 17.4 15.8
17.9 18.0 16.3 17.5 17.3 20.4 19.1 21.0 18.1 19.0
19.6 18.9 16.8 18.2 17.6 19.1 18.0 16.8 20.9 17.9
17.7 20.3 18.6 19.0 16.5 19.4 18.6 18.6 17.3 18.7

We will first construct a 90% confidence interval, then construct a 95% confidence interval, and then finally construct a 99% confidence interval. We will want to compare the confidence intervals and determine how a larger percentage confidence interval affects the range of values. Ultimately, these confidence levels can be used by consumers when they are deciding to purchase a new car, particularly if they are concerned about driving long distances for work or travel, and therefore gas prices.

4) Find the Mean and Standard Deviation of the raw data using Excel's built-in functions
Use Excels' built-in mean and standard deviation functions to find the sample mean and sample standard deviation for this data. Note that this will be a Sample Standard Deviation! (You should know how to do this already from Lab #2!) I have rounded both of these calculations to the nearest hundredths place (2 decimals).

Label each value accordingly. I put the answer for the mean in cell D4, and the sample standard deviation in cell D5.

5) Find the Margin of Error "E" using Excel's built-in "CONFIDENCE.T" function...90%
Use Excel to calculate the margin of error "E" by using the CONFIDENCE.T function, since we only have a small sample of data, and only the sample standard deviations. (We will be doing this three separate times...first for a 90% confidence level, then for a 95% confidence level, and finally for a 99% confidence level.) We will start with the 90% confidence level.

In cell C7, label "90% CI." In cell C8, label "Margin of Error." In cell D8, you will be calculating the value for "E" using Excel's built-in function. Go to the formulas tab and find "CONFIDENCE.T." The following box will appear:

Alpha: Alpha is the level of significance, which is NOT the confidence percentage! Remember that alpha is the complement of the confidence percentage, 1-C!

Standard_dev: Use the sample standard deviation you have just calculated with Excel. Click on the actual cell you had the answer appear in, instead of manually typing in the value of the calculation.

Size: The total sample size, the n value

When you hit "OK" your answer should appear in cell D8. I had Excel round this value automatically to the nearest hundredth, two decimal places.

6) Find the 90% Confidence Interval (find the Lower Bound and Upper Bound)
Type in your own formulas to find the 90% confidence interval, using the margin of error you just found.

Recall that to find a confidence interval, we add and subtract the margin of error from the sample mean, in hopes to represent the true population mean.
(CI = - E to + E)

In cell C9, label "LB" to represent the Lower Bound of the interval. In cell D9, type in "=" to have Excel begin to do a manual calculation. Then click on cell D4 which hosts the sample mean you have already calculated, and then "-" to subtract. Then click on cell D8 which hosts your margin of error, E. When you hit enter, the lower bound should occur.

In cell C10, label "UB" to represent the Upper Bound of the interval. In cell D10, type in "=" to have Excel begin to do a manual calculation. Then click on cell D4 which hosts the sample mean you have already calculated, and then "+" to add. Then click on cell D8 which hosts your margin of error, E. When you hit enter, the lower bound should occur.

To fully represent the Confidence Interval, you need to bring the Lower Bound together with the Upper Bound. In cell D7, represent the interval. (I typed in the interval using parentheses)

7) Write your Confidence Interval Conclusion Statement
Type in a conclusion statement (in a full sentence!) to describe your result, just like the statements we practiced in class. (Type this right in the Answer Worksheet, Word Document)

8) Find the 95% Confidence Interval
Repeat steps 5, 6, and 7 for a 95% confidence interval. (I started in cell C12)

9) Find the 99% Confidence Interval
Repeat steps 5, 6, and 7 for a 99% confidence interval. (I started in cell C17)

10) Compare the Confidence Intervals
What do you notice about the confidence intervals as you increase the percentage of confidence?

12) Check your Control Page/Formula Page

13) Answer the additional questions/reflection in the Answer Worksheet, making sure it is complete.

The manufacturer of this SUV has stated that the car owner can expect to get 16 mpg for city driving, 20 mpg for highway driving, and 18 mpg overall. Based on the confidence intervals you just found, does the manufacturer's claim seem to be accurate? Explain why or why not.

Answer Worksheet

Example 1

1) What is the sample mean of the raw data?

2) What is the 90% confidence interval?

3) What can you conclude regarding the 90% confidence interval?

4) What is the 95% confidence interval?

5) What can you conclude regarding the 95% confidence interval?

6) What is the 99% confidence interval?

7) What can you conclude regarding the 99% confidence interval?

8) What do you notice about the confidence intervals as you increase the percentage of confidence?

9) The manufacturer of this SUV has stated that the car owner can expect to get 16 mpg for city driving, 20 mpg for highway driving, and 18 mpg overall. Based on the confidence intervals you just found, does the manufacturer's claim seem to be accurate? Explain why or why not.

10) Why did we use the "Confidence.T" formula in this lab for the margin of error calculation? Explain.

Reflection

What are your opinions and observations regarding using Excel for these calculations? Explain your thoughts and reasoning.