# Chicago State University Confidence Interval Excel Task

Confidence IntervalsAlbright, S. C., & Winston, W. L. (2017). Business analytics: Data analysis and decision

making (6th ed.). Retrieved from https://redshelf.com/

In this week’s reading, we discussed different types of samples, their distributions,

sampling errors, probabilities, and confidence intervals. Using Excel or StatTools,

complete the following Problem. This is similar to Problem 33 in Chapter 8 on page 351.

Problem – You have been assigned to determine whether more people prefer Coke or

Pepsi. Assume that roughly half the population prefers Coke and half prefers Pepsi.

How large a sample do you need to take to ensure that you can estimate, with 90%

confidence, the proportion of people preferring Coke to within 5% of the actual value?

Show all of your work.

8-3 CONFIDENCE INTERVAL FOR A MEAN

We now come to the main topic of this chapter: using properties of sampling distributions to

construct confidence intervals. We assume that data have been generated by some random

mechanism, either by observing a random sample from some population or by performing

8-3 Confidence Interval for a Mean 317

Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

Activate Windows

8-3 CONFIDENCE INTERVAL FOR A MEAN

We now come to the main topic of this chapter: using properties of sampling distributions to

construct confidence intervals. We assume that data have been generated by some random

mechanism, either by observing a random sample from some population or by performing

8-3 Confidence Interval for a Mean 317

Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

Activate Windows

a

a randomized experiment. The goal is to infer the values of one or more population param-

eters such as the mean, the standard deviation, or a proportion from sample data. For each

such parameter, you use the data to calculate a point estimate, which can be considered a

best

guess for the unknown parameter. You then calculate a confidence interval around the

point estimate to measure its accuracy.

We begin by deriving a confidence interval for a population mean u, and we discuss its

interpretation. Although the particular details pertain to a specific parameter, the mean, the

same ideas carry over to other parameters as well, as will be described in later sections. As

usual, the sample X is used as the point estimate of u.

To obtain a confidence interval for y, you first specify a confidence level, usually

90%, 95%, or 99%. You then use the sampling distribution of the point estimate to deter-

mine the multiple of the standard error (SE) to go out on either side of the point esti-

mate to achieve the given confidence level. If the confidence level is 95%, the value used

most frequently in applications, the multiple is approximately 2. More precisely, it is a

t-value. That is, a typical confidence interval for u is of the form in Equation (8.4), where

SE(X) = s/Vn.

=

To obtain the correct t-multiple, let a be one minus the confidence level (expressed as

a decimal). For example, if the confidence level is 90%, then a = 0.10. Then the appro-

priate t-multiple is the value that cuts off probability a/2 in each tail of the t distribution

with n – 1 degrees of freedom. For example, if n = 30 and the confidence level is 95%,

cell B25 of Figure 8.2 indicates that the correct t-value is 2.045. The corresponding 95%

confidence interval for u is then

X + 2.045(s/Vn)

n =

If the confidence level is instead 90%, the appropriate t-value is 1.699 (change the proba-

bility in cell B24 to 0.10 to see this), and the resulting 90% confidence interval is

X + 1.699(s/Vn)

If the confidence level is 99%, the appropriate t-value is 2.756 (change the probability in

cell B24 to 0.01 to see this), and the resulting 99% confidence interval is

Activate Windows

X + 2.756(s/Vn)

Go to Settings to activate W

Excel-only method at first and then move to StatTools when you are more comfortable with

the procedure, but this is totally up to you. These comments apply to the other confidence

intervals in this chapter as well.

EXAMPLE

8.1 CUSTOMER RESPONSE TO A NEW SANDWICH

A

fast-food restaurant recently added a new sandwich to its menu. To estimate the popu-

wich were surveyed. Each of these customers was asked to rate the sandwich on a scale

of 1 to 10, 10 being the best. The results of this survey appear in column B of Figure 8.3

(with several rows hidden). (See the file Satisfaction Ratings.xlsx.) The manager wants to

estimate the mean satisfaction rating over the entire population of customers by finding a

95% confidence interval. How should she proceed?

Figure 8.3

Analysis of New Sandwich Data

A

B

с

D

E

F

G

1

Customer

Satisfaction

2

1

7

M[ در

Excel-only confidence interval for mean

Sample size

40

Sample mean

6.25

Sample Std Dev

1.597

3

2

5

=COUNT(B2:B41)

=AVERAGE( B2:B41)

=STDEV.S(B2:B41)

=E4/SQRT(E2)

4

3

5

5

4

6

Std Error of mean

0.253

6

5

8

95%

7

6

7

39

8

7

6

Confidence level

Degrees of freedom

t multiple

Lower limit

Upper limit

2.023

=E2-1

=T.INV.2T(1-E6,E7)

=E3-E8*E5

9

8

7

5.739

10

9

10

6.761

=E3+E8*E5

11

10

7

12

11

9

39

38

9

© Cengage Learning

40

39

5

41

40

4

Activate Windows

Objective To obtain a 95% confidence interval for the mean satisfaction rating of the

new sandwich.

Solution

The Excel-only method is spelled out in Figure 8.3 by the formulas shown in column G.

The calculations follow directly from Equation (8.4). As in Figure 8.2, the T.INV.2T func-

tion is used to find the correct multiple. Its arguments are one minus the confidence level

and the degrees of freedom. The result is that the 95% confidence interval extends from

5.739 to 6.761.

Alternatively, to use StatTools, make sure a StatTools data set has been designated,

select Confidence Interval from the StatTools Statistical Inference dropdown list, and

select the Mean/Std. Deviation option. Then fill in the resulting dialog box as shown in

Figure 8.4. In particular, select One-Sample Analysis as the Analysis type. (Other types

will be used later in the chapter.) You should obtain the output shown in the figure. It

Figure 8.4 StatTools Confidence Interval Method

G

H

K

StatTools – Confidence interval for Mean/Std. Deviation

X

Analysis Type

One-Sample Analyss

Eormat

D

E

12 StatTools confidence interval for mean

13

Satisfaction

14 Conf. Intervals (One-Sample) Ratings Data 1

15 Sample Size

40

16 Sample Mean

6.250

17 Sample Std Dev

1.597

18 Confidence Level (Mean) 95.0%

19 Degrees of Freedom

39

20 Lower Limit

5.739

21 Upper Limit

6.761

22

23

Variables (Select one or more)

Data Set

Rotings Data

Name

Customer

A2:A41

Satisfaction

B2:841

Address

NNNNN

Confidence Intervals to Calculate

For the Mean

For the Standard Deviation

95%

Confidence Level

Confidence level

09

24

© Cengage Learning

25

OK

Cancel

26

Activate Windows

doesn’t show the “ingredients” shown in the Excel-only method, but it arrives at exactly

the same confidence interval. (Note: If you want to place the output next to the data, as

shown here, select Settings from the StatTools ribbon, and, in the Report group, select

either of the last two Placement options.)

From either method, the principal results are that (1) the best guess for the population

mean rating is 6.250, and (2) a 95% confidence interval for the population mean rating

extends from 5.739 to 6.761. The manager can be 95% confident that the true mean rating

over all customers who might try the sandwich is within this confidence interval.

We stated previously that as the confidence level increases, the length of the confi-

dence interval increases. You can convince yourself of this by entering different confidence

levels such as 90% or 99%. The lower and upper limits of the confidence interval will

change automatically, getting closer together for the 90% level and farther apart for the

99% level. Just remember that you, the analyst, can choose the confidence level, but 95%

is the level most commonly chosen.

Before leaving this example, we discuss the assumptions that lead to the confidence

interval. First, you might question whether the sample is really a random sample—or

whether it matters. Perhaps the manager used some random mechanism to select the

customers to be surveyed. More likely, however, she simply surveyed 40 consecutive

customers who tried the sandwich on a given day. This is called a convenience sample and

is not really a random sample. However, unless there is some reason to believe that these

40 customers differ in some relevant aspect from the entire population of customers, citlijndows

probably safe to treat them as a random sample.

Go to Settings to activate

A second assumption is that the population distribution is normal. We made this

assumption when we introduced the t distribution. Obviously, the population distribution

cannot be exactly normal because it is concentrated on the 10 possible satisfaction rat-

ings, and the normal distribution describes a continuum. However, this is probably not a

problem for two reasons. First, confidence intervals based on the t distribution are robust

FUNDAMENTAL INSIGHT

True Meaning of a 95% Confidence

Interval

Given the data in a particular sample, a 95% confidence

interval for the mean will either include the (unknown)

population mean or it won’t. The true meaning of a 95%

confidence interval is that if the same procedure is used

on many different random samples, about 95% of the

resulting confidence intervals will include the popula-

tion mean, and only about 5% won’t. Therefore, you can

be 95% confident that any particular confidence inter-

val you happen to get is a “good one.

PROBLEMS

Level A

32. Elected officials in a California city are preparing the

annual budget for their community. They would like to

estimate how much their constituents living in this city

are typically paying each year in real estate taxes. Given

that there are over 100,000 homeowners in this city, the

officials have decided to sample a representative subset

of taxpayers and study their tax payments.

a. What sample size is required to generate a 95%

confidence interval for the mean annual real estate

tax payment with a half-length of $100? Assume

that the best estimate of the population standard

deviation o is $535.

b. If a random sample of the size from part a is

selected and a 95% confidence interval for the

mean is calculated from this sample, will the half-

length of the confidence interval be equal to $100?

Explain why or why not.

c. Now suppose that the officials want to construct a

95% confidence interval with a half-length of $75.

Again, assume that the best estimate of the population

standard deviation o is $535. Explain the difference

between this result and the result from part a.

33. You have been assigned to determine whether more

people prefer Coke or Pepsi. Assume that roughly half

the population prefers Coke and half prefers Pepsi. How

large a sample do you need to take to ensure that you

can estimate, with 95% confidence, the proportion of

people preferring Coke within 2% of the actual value?

34. You are trying to estimate the average amount a family

spends on food during a year. In the past the standard

deviation of the amount a family has spent on food

during a year has been approximately $1000. If

want to be 99% sure that you have estimated aver-

age family food expenditures within $50, how many

families do you need to survey?

35. In past years, approximately 20% of all U.S. families

purchased potato chips at least once a month. You

are interested in determining the fraction of all U.S.

families that currently purchase potato chips at leastows

you

W11.

Latiu?

familia

i

8-3 CONFIDENCE INTERVAL FOR A MEAN

We now come to the main topic of this chapter: using properties of sampling distributions to

construct confidence intervals. We assume that data have been generated by some random

mechanism, either by observing a random sample from some population or by performing

8-3 Confidence Interval for a Mean 317

Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203

a randomized experiment. The goal is to infer the values of one or more population param-

eters such as the mean, the standard deviation, or a proportion from sample data. For each

such parameter, you use the data to calculate a point estimate, which can be considered a

best guess for the unknown parameter. You then calculate a confidence interval around the

point estimate to measure its accuracy.

We begin by deriving a confidence interval for a population mean u, and we discuss its

interpretation. Although the particular details pertain to a specific parameter, the mean, the

same ideas carry over to other parameters as well, as will be described in later sections. As

usual, the sample X is used as the point estimate of u.

To obtain a confidence interval for y, you first specify a confidence level, usually

90%, 95%, or 99%. You then use the sampling distribution of the point estimate to deter-

mine the multiple of the standard error (SE) to go out on either side of the point esti-

mate to achieve the given confidence level. If the confidence level is 95%, the value used

most frequently in applications, the multiple is approximately 2. More precisely, it is a

t-value. That is, a typical confidence interval for u is of the form in Equation (8.4), where

SE(X) = s/Vn.

=

Confidence Interval for Population Mean

X + t-multiple SE (X)

(8.4)

To obtain the correct t-multiple, let a be one minus the confidence level (expressed as

a decimal). For example, if the confidence level is 90%, then a = 0.10. Then the appro-

priate t-multiple is the value that cuts off probability a/2 in each tail of the t distribution

with n – 1 degrees of freedom. For example, if n = 30 and the confidence level is 95%indows

cell B25 of Figure 8.2 indicates that the correct t-value is 2.045. The corresponding 95%-o activate |

=

=

To obtain the correct t-multiple, let a be one minus the confidence level (expressed as

a decimal). For example, if the confidence level is 90%, then a = 0.10. Then the appro-

priate t-multiple is the value that cuts off probability a/2 in each tail of the t distribution

with n – 1 degrees of freedom. For example, if n = 30 and the confidence level is 95%,

cell B25 of Figure 8.2 indicates that the correct t-value is 2.045. The corresponding 95%

confidence interval for u is then

X + 2.045(s/Vn)

If the confidence level is instead 90%, the appropriate t-value is 1.699 (change the proba-

bility in cell B24 to 0.10 to see this), and the resulting 90% confidence interval is

X + 1.699(s/Vn)

If the confidence level is 99%, the appropriate t-value is 2.756 (change the probability in

cell B24 to 0.01 to see this), and the resulting 99% confidence interval is

X + 2.756(s/Vn)

tinto AG

Note that as the confidence level increases, the length of the confidence interval also

increases. Because narrow confidence intervals are desirable, this presents a trade-off. You

can either have less confidence and a narrow interval, or you can have more confidence and

a wide interval. However, you can also take a larger sample. As n increases, the standard

error s/Vn tends to decrease, so the length of the confidence interval tends to decrease for

any confidence level. (Why won’t it decrease for sure? The larger sample might result in a

larger value of s that could offset the increase in n.)

Example 8.1 illustrates confidence interval estimation for a population mean. Starting

in this edition of the book, we illustrate this in two ways: with Excel-only formulas and

with StatTools. The advantages of the Excel-only method are that no add-in is required,

and it shows exactly how the calculations are performed. The advantage of StatTools is

that it is much faster, requiring only that you fill in a dialog box. You might want to try the