Wednesday, May 28, 2014

2-Sample Unpooled Hypothesis Test of Proportion 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 Two-Sample,

Unpooled Hypothesis Test

of Proportion in Excel

This hypothesis test analyzes two independent samples to determine if the populations from which the samples were taken have equal proportions. This test is often used to determine whether two sample proportions are likely different by some specific proportion.

The test is called an unpooled test because the Null Hypothesis states that the two sample proportions are not the same. The formula for Standard Error uses a unpooled proportion that does not combine the proportions of both samples into a single, pooled proportion as a pooled test does. This formula is shown in this following set of formulas.

p_bar₁ = observed sample 1 proportion

p_bar₁ = X₁/n₁

= (Number of successes in sample 1)/(Number of trials in sample 1)

p_bar₂ = observed sample 2 proportion

p_bar₂ = X₂/n₂

= (Number of successes in sample 2)/(Number of trials in sample 2)

Null Hypothesis H₀: p_bar₂ - p_bar₁ = Constant = a non-zero proportion

This Null Hypothesis that states that p_bar₁ ≠ p_bar₂ indicates that this is an unpooled test.

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

(Click On Image To See a Larger Version)

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

1) The observed p_bar₂ - p_bar₁ is beyond the Critical Value.

2) The z Value (the Test Statistic) is farther from zero 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 Two-Sample,

Unpooled, One-Tailed Hypothesis

Test of Proportion in Excel

It is believed that Production Line B produces 5 percent more defects than Production Line A.

Both production lines manufacture the same products and have the same types of machines that are all in similar condition. Both production lines operate approximately the same number of hours. The only difference between the production lines is the experience of the crews. The crews that operate Production Line A have more experience the crews on Production Line B.

Completed units from both production lines were sampled and evaluated over the same period of time as follows.

12 out of 200 randomly sampled units produced on Production Line A were nonconforming. The proportion of sample units from Production Line A that were nonconforming was 0.06 (6 percent).

39 out of 300 randomly sampled units produced on Production Line B were nonconforming. The proportion of sample units from Production Line B that were nonconforming was 0.13 (13 percent).

Determine with 95 percent certainty whether production Line B’s overall proportion nonconforming exceeds that of Production Line A by more than 5 percent. In other words, determine whether difference between Production Line B’s overall percent defective and Production Line A’s overall percent defective is greater than 5 percent.

Note that this will be a unpooled z Test because the proportions of the two populations are assumed to be different. The Null Hypothesis will state that the difference between the proportion of defectives of the two populations from which the samples are taken is equal to 5 percent. The Alternative Hypothesis states that this difference is greater than 5 percent.

Sample results for the two samples cannot be pooled if they are known to be different, as stated by the Null Hypothesis. Sample results can only be pooled if the Null Hypothesis states that the proportions of the two samples are the same.

Two-independent-sample Hypothesis Tests of Proportion remain slightly more intuitive and allow for consistent use of the variable name p_bar₂–p_bar₁ if the larger sample proportion is always designated as p_bar₂.and the smaller sample proportion is designated as p_bar₁.

Summary of Problem Information

First Sample – Production Line A (Experienced Crews)

X₁ = number of positive outcomes (defects) in n₁ trials = 12

n₁ = number of trials (random units inspected) = 200

p_bar₁ = X₁/n₁ = 12/200 = 0.06

Second Sample – Production Line B (Inexperienced Crews)

X₂ = number of positive outcomes (defects) in n₂ trials = 39

n₂ = number of trials (random units inspected) = 300

p_bar₂ = X₂/n₂ = 39/300 = 0.13

p_bar₂ – p_bar₁ = 0.13 – 0.06 = 0.07

Level of Certainty = 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 (the status of each sampled completed production unit) can have only one of two values: conforming (not defective) or nonconforming (defective). 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 whether the difference between the proportion of defective units from Production Line A and Production Line B is greater than the 5 percent. This is a directional inequality making this hypothesis test a one-tailed test. This is a one-tailed test in the right tail if the directional inequality is greater than. If the directional inequality is less than, the one-tailed test will occur in the left tail.

If the problem asks whether the difference between the proportion of defective units from Production Line A and Production Line B is simply not equal to 5 percent, the inequality is non-directional and the test is two-tailed. A two-tailed test is more stringent than a one-tailed test.

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

This is a two-sample hypothesis test because two independent samples are being compared. One independent sample included completed units from Production Line A and the other independent sample included completed units from Production Line B.

d) Pooled Test or an Unpooled Test?

Once a hypothesis test has been determined to be a two-independent-sample test, the test should be designated as a pooled or unpooled test because each uses different formulas. A pooled Hypothesis Test of Proportion makes a basic assumption that the proportions of both populations are the same. An unpooled Hypothesis Test of Proportion makes a basic assumption that the proportions of both populations are not the same. This is an unpooled test because the difference between the proportion defective for each of the two production lines is assumed to be at least 5 percent.

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 two-independent-sample, unpooled, one-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:

Sample 1

X₁ = 12

n₁ = 200

p₁ = 0.06

q₁ = 0.94

n₁p₁ = 12 and n₁q₁ = 188

Sample 2

X₂ = 39

n₂ = 300

p₂ = 0.13

q₂ = 0.87

n₂p₂ = 39 and n₂q₂ = 261

np > 5 and nq >5 for both samples 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)

When np is substituted for the normal distribution’s mean and npq is substituted for the normal distribution’s standard deviation, then the following is true:

Normal (mean, standard deviation)

becomes

Normal (np, npq)

This approximates Binomial (n,p).

The approximation can be demonstrated with Excel using data from the second sample of this problem.

X = 90 = the number of positive outcomes in n trials

n = 200 = the number of trials in one sample

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

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

The normal approximation of the binomial distribution as follows:

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

Analyzing the data from Sample 2 would produce the following comparison:

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

= BINOM.DIST(39, 300, 0.13, FALSE) = 0.067

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

= NORM.DIST(39, 39, 33.93, FALSE) = 0.012

The difference between BINOM.DIST(39, 300, 0.13 FALSE) and NORM.DIST(39, 39, 33.93, FALSE) is less than 0.06. 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 difference in proportions defective between Production Line A and Production Line B equals 5 percent. This Null Hypothesis would be written as follows:

H₀: p_bar₂–p_bar₁ = Constant = 0.05

This test is an unpooled test because the Null Hypothesis states that the two sample proportions do not equal each other. The Constant in the Null Hypothesis for an unpooled test is therefore a nonzero number.

The formula for Standard Error in an unpooled, two-independent-sample Hypothesis Test of Proportion does not use a pooled proportion that combines the proportions of both samples as a pooled test would.

The Alternate Hypothesis is always in inequality and states that the two items being compared are different. In this case, the Alternative Hypothesis states that the difference between proportions defective of Production Line A and Production Line B is greater than 5 percent. This Alternate Hypothesis is as follows:

H₁: p_bar₂–p_bar₁ > Constant, which equals 0.05

Therefore:

H₁: p_bar₂–p_bar₁ > 0.05

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

The Alternative Hypothesis is directional (“greater than” or “less than” instead of “not equal to”) and the hypothesis test is therefore a one-tailed test. the “greater than” operation in the Alternative Hypothesis indicates that this one-tailed test will occur in the right tail. A “less than” operator would indicate that this one-tailed test would occur in the left tail.

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 samples were taken. A two-independent-sample Hypothesis Test of Proportion determines whether to reject or fail to reject the Null Hypothesis which states that the populations from which the two independent samples came from have equal proportions.

In this case the Hypothesis test analyzes whether total proportion defective of Production Line B is at least 5 percent greater than the total proportion defective of Production Line A based upon much smaller samples taken from both production lines.

Step 2 – Map the Distributed Variable to Normal Distribution

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

The sample proportion, p_bar₂–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₂–p_bar₁ = Constant = 0.05

The distributed variable p_bar₂–p_bar₁ will be mapped to a normal distribution curve with a mean = 0.05, which is the Constant.

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_Diff) for an unpooled, two-independent-sample Hypothesis Test of Proportion is calculated as follows:

(Click On Image To See a Larger Version)

SE_Diff = SQRT[ (p_bar₁*q_bar₁/n₁) + (p_bar₂*q_bar₂/n₂) ]

SE_Diff = SQRT[ (0.13*0.87/300) + (0.06*0.94/200) ]

SE_Diff =0.03

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

Mean = 0.05

Standard Error = 0.03

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₂–p_bar₁ (0.07) is from the hypothesized p_bar₂–p_bar₁ (0.05).

This Hypothesis Test of Proportion calculates the probability of a difference sample having a difference proportion equal to the observed p_bar₂–p_bar₁ (0.07) if the true difference equals 0.05 and the difference sample proportion is normally distributed.

The Regions of Acceptance and Rejection

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

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

This is a one-tailed test in the right tail as indicated by the Alternative Hypothesis. A one-tailed test in the right tail means that the entire Region of Rejection is contained in the outer right tails. The Alternative Hypothesis indicates this.

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.

A hypothesis test is non-directional if the Alternative Hypothesis has the non-directional operator “not equal to.”

In this case the Alternative Hypothesis is the following:

H₁: p_bar₂–p_bar₁ > 0.05

This Alternate Hypothesis indicates that the hypothesis test has the Region of Rejection entirely contained in the outer right tail. The total size of the Region of Rejection is equal to Alpha. In this case Alpha, α, is equal to 0.05 (not to be mistaken with the Constant of the Null Hypothesis which coincidentally happens to be 0.05 as well). This means that the Region of Rejection will take up 5 percent of the total area under this normal distribution curve.

Calculate Critical Values

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

Critical Value = Mean + (Number of Standard Errors from Mean to Region of Rejection) * SE

Critical Value = Mean + NORM.S.INV(1-α) * SE_Diff

Critical Value = 0.05 + NORM.S.INV(1- 0.05 ) * 0.03

Critical Value = 0.05 + NORM.S.INV(0.95) * 0.03

Critical Value = 0.05 + 0.049

Critical Value = 0.099

The Region of Rejection is therefore everything that is to the right of 0.099.

This normal distribution curve with the blue Region of Acceptance in the center and the yellow Region of Rejection in the outer tails is shown is as follows in this Excel-generated graph:

(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_bar2–p_bar1 With Critical Value

If the observed value of p_bar₂–p_bar₁ (0.07) 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₂–p_bar₁ falls into the Regions of Rejection (either of the two yellow outer regions), the Null Hypothesis is rejected.

The observed p_bar₂–p_bar₁ (0.07) is closer to the curve’s mean (0.05) than the Critical Value on the right side (0.099) and falls in the blue Region of Acceptance. We therefore reject the Null Hypothesis. We cannot state with 95 percent certainty that the difference between the proportions defective of Production Line A and Production Line B is greater 0.05.

2) Compare z Value With Critical z Value

The Test Statistic for this Hypothesis Test is called the z Value. The z Value is the number of Standard Errors that the observed p_bar₂–p_bar₁ is from the mean of 0.05. The Critical z Value is the number of Standard Errors that the Critical z 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. The test Statistic is calculated as follows:

z Value (the Test Statistic) = (p_bar₂–p_bar₁ - Constant) / SE_Diff

z Value (the Test Statistic) = (0.13 – 0.06 – 0.05)/0.03

z Value (the Test Statistic) = 0.67

This means that the observed p_bar₂–p_bar₁ (0.07) is 0.67 Standard Errors from the curve’s mean (0.05).

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

[ Note that the one-tailed Critical z Value in the left tail = NORM.S.INV(α) ]

Critical z Value_{α=0.05,one-tailed,right tail} = NORM.S.INV(0.95) = 1.65

This means that the boundary of the Region of Rejection is 1.65 Standard Errors to the right of the curve’s mean (0.05).

The z Value (0.67) is closer to the curve’s standardized mean of zero than the Critical z Value (1.65) 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₂–p_bar₁ (0.07) . If the p Value is smaller than Alpha (since the test is one-tailed), the Null Hypothesis is rejected. If the p Value is larger than Alpha, 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(0.67,TRUE),1-NORM.S.DIST(0.67,TRUE))

p Value = 0.2523

The p Value (0.2523) is larger than Alpha (0.05) and we therefore cannot reject the Null Hypothesis.

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

(Click On Image To See a Larger Version)

The Null Hypothesis would also not be rejected if the test were two-tailed because a two-tailed test is more stringent that a one-tailed test. A hypothesis test is more stringent if the Null Hypothesis is harder to reject.

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Technorati Tags: hypothesis test,excel,excel 2010,excel 2013,statistics,hypothesis test of proportion

Wednesday, May 28, 2014

2-Sample Unpooled Hypothesis Test of Proportion in Excel 2010 and Excel 2013

Overview of Two-Sample,

Unpooled Hypothesis Test

of Proportion in Excel