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

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

 

Overview of z-Based

Confidence Interval of a

Population Mean

This confidence interval of a population mean is based upon the sample mean being normally distributed. A 95-percent confidence interval of a population mean is an interval that has a 95-percent chance of containing the population mean.

The sample mean is normally distributed if the sample size is large (n > 30) as per the Central Limit Theorem. The CTL states that the means of large samples randomly taken from the same population will be normally distributed no matter how the population is distributed. Confidence intervals of a population mean can be based upon the normal distribution only if the sample size is large (n > 30).

In addition to the required large sample size that ensures the normal distribution of the sample mean, the population standard deviation must be known as well.

x_bar = Observed Sample Mean

(Click On Image To See a Smaller Version)

Margin of Error = Half Width of C.I. = Z Value_α/2 * Standard Error

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

A confidence interval of a population mean that is based on the normal distribution is z-based. A confidence interval of a population mean that is based on the t distribution is t-based.

It is much more common to use the t distribution than the normal distribution to create a confidence interval of the population mean. Requirements for t-based confidence intervals are much less restrictive than the requirements for a z-based confidence interval.

A confidence interval of a population mean can be based on t distribution if only the sample standard deviation is known and any of the following three conditions are met:

1) Sample size is large (n > 30). The Central Limit Theorem states that the means of large, similar-sized, random samples will be normally distributed no matter how the underlying population is distributed.

2) The population from which the sample was drawn is proven to be normally distributed.

3) The sample is proven to be normally distributed.

A confidence interval of the mean can be created based on the normal distribution only if the sample size is large (n >30) AND the population standard deviation, σ, is known. For this reason, confidence intervals are nearly always created using the t distribution in the professional environment.

This example will demonstrate how to create a confidence Interval of the mean using the normal distribution.

 

Example of a z-Based Confidence

Interval in Excel

In this example a 95 percent Confidence Interval is created around a sample mean using the normal distribution.

A company received a shipment of 5,000 steel rods of unknown tensile strength. All rods originated from the same source. The company randomly selected 100 rods from the shipment and tested each for tensile strength. The average tensile strength of the 100 rods tested was found to be 250 MPa (megapascals). The tensile strength of steel rods of this exact type is known to have a standard deviation of 30 MPa.

Calculate the endpoints of the interval that 95 percent certain to contain the true mean tensile strength of all 5,000 rods in the shipment. In other words, calculate the 95 percent confidence interval of the population (entire shipment) mean tensile strength.

 

Summary of Problem Information

x_bar = sample mean = AVERAGE() = 250 MPa

µ = (population) mean tensile strength of entire shipment = Unknown

σ (Greek letter “sigma”) = population tensile strength standard deviation = 30 MPa

n = sample size = COUNT() = 100

SE = Standard Error = σ / SQRT(n) = 30 / SQRT(100)

SE = 3

Level of Certainty = 0.95

Alpha = 1 - Level of Certainty = 1 – 0.95 = 0.05

As when creating all Confidence of Mean, we must satisfactorily answer these two questions and then proceed to the two-step method of creating the Confidence Interval.

 

The Initial Two Questions That Must be Answered Satisfactorily

What Type of Confidence Interval Should Be created?

Have All of the Required Assumptions For This Confidence Interval Been Met?

 

The Two-Step Method For Creating Confidence Intervals of Mean are the following:

Step 1 - Calculate the Half-Width of the Confidence Interval (Sometimes Called the Margin of Error)

Step 2 – Create the Confidence Interval By Adding to and Subtracting From the Sample Mean Half the Confidence Interval’s Width

 

The Initial Two Questions That Need To Be Answered Before Creating a Confidence Interval of the Mean or Proportion Are as Follows:

 

Question 1) Type of Confidence Interval?

a) Confidence Interval of Population Mean or Population Proportion?

This is a Confidence Interval of a population mean because each individual observation (each sampled rod’s tensile strength) within the entire sample can have a wide range of values. Most of the sample values are spread out between 200 MPa and 300 MPa.

Sampled data points used to create a Confidence Interval of a population proportion are binary: they can take only one of two possible values.

 

b) t-Based or z-Based Confidence Interval?

A confidence interval can be created that is based on the normal distribution can only if both of the following conditions are met:

Sample size is large (n > 30)

AND

The population standard deviation, σ, is known.

In this case sample size is large (n = 100) and the population standard deviation is known (σ = 30 MPa).

This Confidence Interval can be created using either the t distribution or the normal distribution. In this case, the normal distribution will be used to create this Confidence Interval of a population mean. This Confidence Interval of a population mean will be z-based.

This confidence interval will be a confidence interval of a population mean and will be created using the normal distribution.

 

Question 2) All Required Assumptions Met?

a) Normal Distribution of the Sample Mean

As per the Central Limit Theorem, the large sample size (n = 100) guarantees that the sample mean is normally distributed.

 

b) Population Standard Deviation Is Known (σ = 30 MPa)

 

We now proceed to the two-step method for creating all Confidence intervals of a population mean. These steps are as follows:

Step 1) Calculate the Width of Half of the Confidence Interval

Step 2 – Create the Confidence Interval By Adding and Subtracting the Width of Half of the Confidence Interval from the Sample Mean

Proceeding through the two-step method of creating a confidence interval is done is follows:

 

Step 1) Calculate Width-Half of Confidence Interval

Half the Width of the Confidence Interval is sometimes referred to the Margin of Error. The Margin of Error will always be measured in the same type of units as the sample mean is measured in, which in this case was MPa (megapascals).

Calculating the Half Width of the Confidence Interval using the normal distribution would be done as follows in Excel:

Margin of Error = Half Width of C.I. = Z Value_α/2 * Standard Error

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

Margin of Error = Half Width of C.I. = NORM.S.INV(1 – 0.05/2) * 30/SQRT(100)

Margin of Error = Half Width of C.I. = NORM.S.INV(0.975) * 30/10

Margin of Error = Half Width of C.I. = 1.96 * 3

Margin of Error = Half Width of C.I. = 5.88 MPa

The Half Width of z-based Confidence Interval can also be calculated by the following Excel formula:

Margin of Error = Half Width of C.I. = CONFIDENCE.NORM(α, σ, n)

Margin of Error = Half Width of C.I. = CONFIDENCE.NORM(0.05, 30, 100)

Margin of Error = Half Width of C.I. = 5.88 MPa

 

Step 2 Confidence Interval = Sample Mean ± C.I. Half-Width

Confidence Interval = Sample Mean ± (Half Width of Confidence Interval)

Confidence Interval = x_bar ± 5.88

Confidence Interval = 250 ± 5.88

Confidence Interval = [ 244.12 MPa, 255.88 MPa ]

A graphical representation of this Confidence Interval is shown as follows:

(Click On Image To See a Smaller Version)

 

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!