1-Sample Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013

This is one of the following four articles on Hypothesis Tests of Proportion in Excel

Hypothesis Tests of Proportion Overview (Hypothesis Testing On Binomial Data)

1-Sample Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013

2-Sample Pooled Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013

How To Build a Much More Useful Split-Tester in Excel Than Google's Website Optimizer

 

Overview of the One-

Sample Hypothesis test of

Proportion

This hypothesis test analyzes a single sample to determine if the population from which the sample was taken has a proportion that is equal to a constant, p. In many cases, a one-sample Hypothesis test of Proportion is used to determine if one population has the same proportion as the known proportion of another population, p.

p_bar = observed sample proportion

p_bar = X/n = (Number of successes in the sample)/(Number of trials in the sample)

q_bar = 1 – p_bar

p = Constant (is the hypothesized population proportion, which is often a known population proportion)

q = 1 – p

Null Hypothesis H₀: p_bar = Constant = p

The z Value for this test, which is the Test Statistic, is calculated as follows:

(Click On Image to See a Larger Version)

(Click On Image to See a Larger Version)

SE is calculated using population parameters p and q, not sample statistics p_bar and q_bar.

The Null Hypothesis is rejected if any of the following equivalent conditions are shown to exist:

1) The observed p_bar is beyond the Critical Value.

2) The z Value (the Test Statistic) is farther from zero than the Critical z Value.

3) The p value is smaller than α for a one-tailed test or α/2 for a two-tailed test.

 

Example of a One-Sample, Two-

Tailed Hypothesis Test of

Proportion in Excel

Following is result of a one-sample hypothesis test of proportion that is two-tailed:

Over the course of one entire year, 30 percent of all units produced by one production line had at least one defect. During the next year the first 21 out of 50 units produced by the production line had a defect. Determined with 95 percent certainty whether production line’s performance has changed.

Note that the question asks only whether there has been any change, not whether there has been a specific change such as whether there has been a worsening of performance. This means that the hypothesis test will be a two-tailed test and not a one-tailed test.

 

Summary of Problem Information

p = Population proportion defective for last year = 0.30

The example evaluates whether the population proportion for this year equals p (last year’s proportion defectives) based on a sample taken from this year’s production.

q = Population proportion not defective for last year

q = 1 – p = 0.70

X = number of defects detected in the sample taken from this year’s production = 21

n = Sample size = 50

p_bar = Observed sample proportion = X/n = 21/50 = 0.42

Required Level of Certainty = 95% = 0.95

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

As with all Hypothesis Tests of Proportion, we must satisfactorily answer these two questions and then proceed to the four-step method of solving the hypothesis test that follows.

 

The Initial Two Questions That Must be Answered Satisfactorily

1) What Type of Test Should Be Done?

2) Have All of the Required Assumptions For This Test Been Met?

 

The Four-Step Method For Solving All Hypothesis Tests of Mean

Step 1) Create the Null Hypothesis and the Alternate Hypothesis

Step 2) – Map the Normal Curve Based on the Null Hypothesis

Step 3) – Map the Regions of Acceptance and Rejection

Step 4) – Perform the Critical Value Test, the p Value Test, or the Critical z Value Test

 

The Initial Two Questions That Must Be Answered Before Performing the Four-Step Hypothesis Test of Proportion are as follows:

 

Question1) Type of Test?

a) Hypothesis Test of Mean or Proportion?

This is a Hypothesis Test of Proportion because each individual observation (each sample unit from the production line) can have only one of two values: it is defective or it is not. Samples for Hypothesis Test of Mean can have more than two values and often can assume any value within a possible range of values.

 

b) One-Tailed or Two-Tailed Hypothesis?

The problem asks to determine whether the production line’s proportion defective during the current year is simply different than during the previous year. This is a non-directional inequality making this hypothesis test a two-tailed test. If the problem asked whether the proportion defective this year was greater than or less than last year, the inequality would be directional and the resulting hypothesis test would be a one-tailed test. A two-tailed test is more stringent than a one-tailed test.

 

b) One-Sample or a Two-Sample Test?

This is a one-sample hypothesis test because only one production sample of 50 units has had its proportion defective calculated. This sample proportion will be compared with the overall proportion defective from the previous year.

 

d) t-Test or z-Test?

A Hypothesis Test of Proportion uses the normal distribution to approximate the underlying binomial distribution that the sampled objects follow. A Hypothesis Test of Proportion will therefore always be a z Test and not a t Test.

A hypothesis test of proportion will always be a z test because a hypothesis test of proportion always uses the normal distribution to model the distributed variable. A t Test uses the t distribution to model the distributed variable.

Samples taken for a Hypothesis Test of Proportion are binary: they can only assume one of two values. Binary objects are distributed according to the binomial distribution. The binomial distribution can be approximated by the normal distribution. A Hypothesis Test of Proportion uses the normal distribution to approximate the underlying binomial distribution that the sampled objects follow. A Hypothesis Test of Proportion will therefore always be a z Test and not a t Test.

This hypothesis test is a z Test that is one-sample, two-tailed hypothesis test of proportion.

 

Question 2) Test Requirements Met?

Can Binomial Distribution Be Approximated By Normal Distribution?

The samples for a Hypothesis Test of Proportion follow the binomial distribution because each sample has only two possible outcomes and the probability of the positive outcome is always the same for each sample taken.

A Hypothesis Test of Proportion approximates the binomial distribution with the normal distribution so that normal-distribution-based statistical analysis tools such as z Scores can be used.

The most important requirement of a Hypothesis Test of Proportion is the validity of approximating the binomial distribution with the normal distribution. The binomial distribution can be approximated by the normal distribution if sample size, n, is large enough and p is not too close to 0 or 1. This can be summed up with the following rule:

The binomial distribution can be approximated by the normal distribution if np > 5 and nq >5. In this case, the calculation of np and qp is the following:

n = 50

p = 0.30

q = 0.70

np = 15 and nq = 35

np > 5 and nq >5 so it is valid to approximate the binomial distribution with the normal distribution. Because the binomial distribution can be modeled by the normal distribution, a z Test can be used to perform a Hypothesis Test of Proportion.

The binomial distribution has the following parameters:

Mean = np

Variance = npq

Each unique normal distribution can be completely described by two parameters: its mean and its standard deviation. As long as np > 5 and nq > 5, the following substitution can be made:

Normal (mean, standard deviation) approximates Binomial (n,p)

If np is substituted for the normal distribution’s mean and npq is substituted for the normal distribution’s standard deviation as follows:

Normal (mean, standard deviation)

becomes

Normal (np, npq)

which approximates Binomial (n,p)

This can be demonstrated with Excel using data from this problem.

n = 50 = the number of trials in one sample

p = 0.3 = expected probability of a positive result in all trials

q = 1 – p = 0.7 = expected probability of a negative result in all trials

If the number of positive outcomes is randomly picked to be X = 21, the normal approximation of the binomial distribution’s PDF at the point X = 21 is computed as follows:

BINOM.DIST(X, n, p, FALSE)

= BINOM.DIST(21, 50, 0.3, FALSE)

= 0.023

The normal distribution’s PDF will equal approximately the same value of binomial distribution’s PDF if the following substitutions are made:

NORM.DIST(X, Mean, Stan. Dev, FALSE)

= NORM.DIST(X, np, npq, FALSE)

This is the basis for the normal approximation of the binomial distribution as follows:

BINOM.DIST(X, n, p, FALSE) ≈ NORM.DIST(X, np, npq, FALSE)

NORM.DIST(X, np, npq, FALSE)

= NORM.DIST(21,15,10.5,FALSE) = 0.032

The difference between BINOM.DIST(21, 50, 0.3, FALSE) and NORM.DIST(21,15,10.5,FALSE) is less than 0.01. That is reasonably close.

Note that the normal approximation of the binomial distribution only works for the PDF (Probability Density Function) and not the CDF (Cumulative Distribution Function). Replacing FALSE with TRUE in the above BINOM.DIST() and NORM.DIST() formulas would calculate their CDFs instead of their PDFs.

 

We now proceed to complete the four-step method for solving all Hypothesis Tests of Proportion. These four steps are as follows:

Step 1) Create the Null Hypothesis and the Alternate Hypothesis

Step 2 – Map the Normal or t Distribution Curve Based on the Null Hypothesis

Step 3 – Map the Regions of Acceptance and Rejection

Step 4 – Determine Whether to Accept or Reject theNull Hypothesis By Performing the Critical Value Test, the p Value Test, or the Critical t Value Test

Proceeding through the four steps is done is follows:

 

Step 1 – Create the Null and Alternative Hypotheses

The Null Hypothesis is always an equality and states that the items being compared are the same. In this case, the Null Hypothesis would state that the proportion defective of the entire current year’s production is not different than the proportion defective from the entire last year’s production, p, which was p = 30 percent or 0.30. This Null Hypothesis would be written as follows:

H₀: p_bar = Constant = p

H₀: p_bar = p = 0.3

The Alternate Hypothesis is always in inequality and states that the two items being compared are different. In this case, the Alternative Hypothesis would state that the average defective percentage for the current year is different than the average defective percentage for last year. This Alternate Hypothesis is as follows:

H₁: p_bar ≠ Constant

H₁: p_bar ≠ p

H₁: p_bar ≠ 0.3

The “not-equals” sign indicates that this is a two-tailed hypothesis test and not a one-tailed test.

The Alternative Hypothesis is non-directional (“not equal” instead of “greater than” or “less than”) and the hypothesis test is therefore a two-tailed test. It should be noted that a two-tailed test is more rigorous (requires a greater differences between the two entities being compared before the test shows that there is a difference) than a one-tailed test.

It is important to note that the Null and Alternative Hypotheses refer to the proportion of the populations from which the sample was taken. A one-sample Hypothesis Test of Proportion determines whether to reject or fail to reject the Null Hypothesis which states that the population has a proportion equal to the Constant. In this case the population from which the sample was taken is the entire current year’s production. The Constant in this case is equal to the proportion defective for the entire last year’s production.

 

Step 2 – Map the Distributed Variable to Normal Distribution

A z Test can be performed if the sample proportion is distributed according to the normal distribution. The sample proportion, p_bar, is distributed according to the binomial distribution. The normal distribution can be used to approximate this binomial distribution because the requirements that np and qp are greater than 5. The distribution of the sample proportion, p_bar, can therefore be approximated by the normal distribution.

The sample proportion, p_bar, will be mapped to a normal distribution. Each unique normal distribution can be fully described by two parameters: its mean and standard deviation.

The mean of the normal distribution curve that maps the distributed variable p_bar is equal to the Constant in the Null Hypothesis. The Null Hypothesis is as follows:

H₀: p_bar = Constant = p = 0.3

The distributed variable p_bar will be mapped to a normal distribution curve with a mean = 0.3.

Population parameters such as population standard deviation have to be estimated if only sample data is available. In this case the population standard deviation will be estimated by the Standard Error which is based on the sample size.

Standard Error (SE) for a one-sample Hypothesis Test of Proportion is calculated as follows:

SE = Standard Error

(Click On Image to See a Larger Version)

SE = SQRT[ (0.3*0.7)/50 ]

SE = 0.0648

Note that SE is calculated using p (0.3) from the Null Hypothesis and not p_bar (0.42). q is derived from p and q_bar is derived from p_bar.

The normal distribution curve that maps the distribution of variable p_bar now has the following parameters:

Mean = 0.3

Standard Error = 0.0648

This Excel-generated normal distribution curve is shown as follows:

 (Click On Image to See a Larger Version)

 

Step 3 – Map the Regions of Acceptance and Rejection

The goal of a hypothesis test is to determine whether to accept or reject the Null Hypothesis at a given level of certainty. If the two things being compared are far enough apart from each other, the Null Hypothesis (which states that the two things are not different) can be rejected.

In this case we are trying to show graphically how different the observed p_bar (0.42) is from the known p (p = 0.3 = the previous annual proportion of defects). p_bar is the defect rate of a sample taken from this year’s production and p is the proportion defective from the entire last year’s production.

A Hypothesis Test of Proportion calculates the probability of a sample having a proportion defective equal to the observed p_bar (0.42) if the true defect rate of this year’s production is the same as last year’s production (0.3) and the sample proportion is normally distributed.

 

The Regions of Acceptance and Rejection

The normal distribution curve that maps the distribution of variable p_bar can be divided up into two types of regions: the Region of Acceptance and the Region of Rejection.

If p_bar’s observed value of 0.42 falls in the Region of Acceptance, we fail to reject the Null Hypothesis. If p_bar falls in the Region of Rejection, we reject the Null Hypothesis.

This is a two-tailed test because the Region of Rejection is split between both outer tails. The Alternative Hypothesis indicates this. A hypothesis test is non-directional if the Alternative Hypothesis has the non-directional operator “not equal to.”

A hypothesis test is a one-tailed test if the Alternative Hypothesis has a directional operator, i.e., “greater than” or “less than.” A one-tailed test has the entire Region of Rejection contained in one outer tail. A “greater than” operator indicates a right-tailed test. A “less than” operator indicates a left-tailed test.

In this case the Alternative Hypothesis is the following:

H₁: p_bar ≠ 0.3

This Alternate Hypothesis indicates that the hypothesis test has the Region of Rejection split between both outer tails and is therefore two-tailed.

The total size of the Region of Rejection is equal to Alpha. In this case Alpha, α, is equal to 0.05. This means that the Region of Rejection will take up 5 percent of the total area under this normal distribution curve.

Because this test is a two-tailed test, the 5 percent Region of Rejection is divided up between the two outer tails. Each outer tail contains 2.5 percent of the total 5 percent is the Region of Rejection.

 

Calculate Critical Values

The Critical Value is the boundary of the Region of Rejection. The Critical Value is the boundary on either side of the curve beyond which 2.5 percent of the total area under the curve exists. In this case both Critical Values can be found by the following:

Critical Values = Mean ± (Number of Standard Errors from Mean to Region of Rejection) * SE

Critical Values = Mean ± NORM.S.INV(1-α/2) * SE

Critical Values = 0.3 ± NORM.S.INV(1- 0.05/2 ) * 0.06428

Critical Values = 0.3 ± NORM.S.INV(0.975) * 0.06428

Critical Values = 0.30 ± 0.13

Critical Values = 0.43 and 0.17

The Region of Rejection is therefore everything that is to the right of 0.43 and everything to the left of 0.17.

This normal distribution curve with the blue Region of Acceptance in the center and the yellow Regions of Rejection in the outer tails is shown is as follows:

 (Click On Image to See a Larger Version)

 

Step 4 – Determine Whether to Reject Null Hypothesis

The object of a hypothesis test is to determine whether to accept of reject the Null Hypothesis. There are three equivalent tests that determine whether to accept or reject the Null Hypothesis. Only one of these tests needs to be performed because all three provide equivalent information. The three tests are as follows:

 

1) Compare p-bar With Critical Value

If the observed value of p_bar (0.43) falls into the Region of Acceptance (the blue region under the curve), the Null Hypothesis is not rejected. If the observed value of p_bar falls into the Regions of Rejection (either of the two yellow outer regions), the Null Hypothesis is rejected.

The observed p_bar (0.42) is closer to the curve’s mean (0.3) than the Critical Value (0.43) and falls in the blue Region of Acceptance. We therefore do not reject the Null Hypothesis. We cannot state with 95 percent certainty that there is a real difference between the overall defect rates of this year and last year based upon the defect rate of the sample taken from this year’s production.

 

2) Compare z Value With Critical z Value

The z Value is the number of Standard Errors that the observed p_bar is from the standardized mean of zero. The Critical z Value is the number of Standard Errors that the Critical Value is from the mean.

If the z Value is closer to the standardized mean of zero than the Critical z Value, the Null Hypothesis is not rejected. If the z Value is farther from the standardized mean of zero than the Critical z Value, the Null Hypothesis is rejected.

z Value = Test Statistic

(Click On Image to See a Larger Version)

z Value = (0.42 – 0.3)/0.0648

z Value = 1.85

This means that the observed p_bar (0.42) is 1.85 Standard Errors from the curve’s mean.

Critical z Values_{α=0.05,two-tailed} = ±NORM.S.INV(1-α/2)

Critical z Values_{α=0.05,two-tailed} = ±NORM.S.INV(0.975) = 1.96

This means that the left and right boundaries of the Regions of Rejection are 1.96 Standard Errors from the curve’s mean.

The z Value (1.85) is closer to the curve’s standardized mean of zero than the Critical z Value (1.96) so the Null Hypothesis is not rejected.

 

3) Compare p Value With Alpha

The p Value is the percent of the curve that is beyond the observed p_bar (0.42). If the p Value is smaller than Alpha/2 (if the test is two-tailed), the Null Hypothesis is rejected. If the p Value is larger than Alpha/2, the Null Hypothesis is not rejected.

The p Value is calculated by the following Excel formula:

p Value = MIN(NORM.S.DIST(z Value,TRUE),1-NORM.S.DIST(z Value,TRUE))

p Value = MIN(NORM.S.DIST(1.85,TRUE),1-NORM.S.DIST(1.85,TRUE))

p Value = 0.032

The p Value (0.032) is larger than Alpha/2 (0.025) and we therefore do not reject the Null Hypothesis.

The following Excel-generated graph shows that the red p Value (the curve area beyond p_bar) is larger than the yellow Alpha/2 Region of rejection in the outer right tail.

 (Click On Image to See a Larger Version)

It should be noted that if this z Test were a one-tailed test, which is less stringent than a two-tailed test, the Null Hypothesis would now have been reject because of the following three equivalent conditions:

1) The p Value (0.034) is smaller than Alpha (0.05). A one-tailed test would contain the entire 5-percent Region of Rejection in one outer tail.

2) p_bar (0.42) would now be the Region of Rejection, which would now have its outer right boundary at 0.41, the Critical value for a one-tailed test.

Critical Value_{one-tailed,right tail} = Mean + NORM.S.INV(1-α) * SE = 0.41

3) The z Value (1.85) would now be farther the standardized mean of zero than the critical z Value which would now be 1.6444

Critical z Value_{one-tailed,right tail} = NORM.S.INV(1-α) = 1.6448

 

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!