This is one of the following five articles on Confidence Intervals in Excel

z-Based Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013

t-Based Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013

Minimum Sample Size to Limit the Size of a Confidence interval of a Population Mean

Confidence Interval of Population Proportion in 2 Steps in Excel 2010 and Excel 2013

Min Sample Size of Confidence Interval of Proportion in Excel 2010 and Excel 2013

 

Min Sample Size to Limit

Width of an Excel

Confidence Interval of a

Mean

The same procedure using the normal distribution is always used to calculate the minimum sample size needed to limit a confidence interval of population mean to a certain size. The procedure shown here is the same procedure used in the previous section that focused on creating a confidence interval with the t-distribution. This procedure is as follows once again:

The larger the sample taken, the smaller the Confidence Interval becomes. That makes intuitive sense because the more sample information that is gathered, the more tightly the position of the population mean can be defined. The Confidence Interval is an interval believed to contain the population mean with specific degree of certainty.

As sample size increase, the Confidence Interval shrinks because greater certainty has been attained. The margin of error, which is equal to half the width of the Confidence Interval, therefore shrinks as well.

During the design phase of a statistical experiment, sample size should be determined. Sampling has a cost and additional sampling beyond what is necessary to attain a desired level of certainty is often undesirable. One common objective of the design phase of a statistical test involving sampling is to determine the minimum sample size required to obtain a specified degree of certainty.

 

Calculating Min Sample Size Is Done

With z-Based Confidence Intervals,

Not t-Based Confidence Intervals

Calculating the minimum sample size necessary to limit the size of a confidence interval of a population mean can be done using the normal distribution but not the t distribution. A t-based confidence interval requires specifying the degrees of freedom, which is derived from the sample size that is unknown.

 

Requirements of z-Based Confidence Interval

A z-based confidence interval (a confidence interval based upon the normal distribution) requires that the sample mean be normally distributed and the population standard deviation be known. These requirements are met if both of the following are true:

1) The minimum sample size is at least 30. This ensures that the sample mean is normally distributed as per the Central Limit Theorem. If the calculated minimum sample size is less than 30, the sample or the population must be confirmed to be normally distributed.

and

2) Population standard deviation or a reasonable estimate of the population standard deviation is known. Sample standard deviation cannot be used because a sample has not been taken.

 

Calculation of Minimum Sample Size

The minimum sample size, n, to limit the width of a z-based confidence interval of a population proportion to a specific size can be derived with the following algebra:

Confidence Interval = Sample mean ± z Score_{α,two-tailed} * SE

Confidence Interval = x_bar ± NORM.S.INV(1 – α/2) * σ/SQRT(n)

Confidence Interval = x_bar ± NORM.S.INV(1 – α/2) * σ /SQRT(n)

(Half-width of C.I.) = NORM.S.INV(1 – α/2) * σ /SQRT(n)

Squaring both sides gives the following:

(Half-width of C.I.)² = NORM.S.INV² (1 – α/2) * σ ²/n

Further algebraic manipulation produces the following:

n = [NORM.S.INV² (1 – α/2) * σ ² ] / (Half-width of C.I.)²

or, equivalently because Half-width of C.I. = Margin of Error,

n = [NORM.S.INV² (1 – α/2) * σ ² ] / (Margin of Error)²

The count of data observations in a sample, n, must be a whole number so n must be rounded up to the nearest whole number. This is implemented in Excel as follows:

n = Roundup( ( NORM.S.INV² (1 – α/2) * σ² ) / (Half-width of C.I.)² )

 

Example of Calculating Min Sample

Size in Excel

A survey was taken of the monthly salaries full-time employee of the California Department of Transportation. The standard deviation of monthly salaries throughout the entire California DOT is known to be $500.

What is the minimum number of employees that would have to be surveyed to be at least 95% certain that the sample average monthly salary is within $50 of the true average monthly salary of all employees in the California DOT?

In other words, what is the minimum sample size needed to create a 95-percent confidence interval about the population mean that has a margin of error no larger than $50?

Another way to state the problem is to ask how large must the sample size be to create a 95-percent confidence interval about the population mean that has a half-width of no more than $50?

σ = Population standard deviation = $500

Half-width of Confidence Interval = Margin of Error = $50

(The confidence interval must be specified in the same units as the population standard deviation is.)

α = 1 – Level of Certainty = 1 – 0.95 = 0.05

n = Roundup( ( NORM.S.INV² (1 – α/2) * σ² ) / (Half-width of C.I.)² )

n = Roundup( ( NORM.S.INV² (1 – 0.05/2) * (500)² ) / (50)² )

n = Roundup( ( NORM.S.INV² (0.975) * (500)² ) / (50)² )

n = Roundup( ( (1.96)² * (500)² ) / (50)² )

n = Roundup( 384.1459 )

n = 385

A minimum of 385 employees must be surveyed to be 95 percent certain that the average salary of the sample is no more than $50 from the true average salary within the entire California DOT.

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!