Friday, May 30, 2014

Mann-Whitney U Test in 12 Steps in Excel as 2-Sample Pooled t-Test Nonparametric Alternative in Excel 2010 and Excel 2013

This is one of the following eight articles on 2-Independent-Sample Pooled t-Tests in Excel

2-Independent-Sample Pooled t-Test in 4 Steps in Excel 2010 and Excel 2013

Excel Variance Tests: Levene’s, Brown-Forsythe, and F Test For 2-Sample Pooled t-Test in Excel 2010 and Excel 2013

Excel Normality Tests Kolmogorov-Smirnov, Anderson-Darling, and Shapiro Wilk Tests For Two-Sample Pooled t-Test

Two-Independent-Sample Pooled t-Test - All Excel Calculations

2-Sample Pooled t-Test – Effect Size in Excel 2010 and Excel 2013

2-Sample Pooled t-Test Power With G*Power Utility

Mann-Whitney U Test in 12 Steps in Excel as 2-Sample Pooled t-Test Nonparametric Alternative in Excel 2010 and Excel 2013

2- Sample Pooled t-Test = Single-Factor ANOVA With 2 Sample Groups

 

Mann-Whitney U Test in

12 Steps in Excel As a

Nonparametric Alternative

For the 2- Sample Pooled

t-Test

The Mann-Whitney U Test is a nonparametric test that can be substituted for the two-sample t-Test (both pooled or unpooled) when the following circumstances occur:

1) Normality of at least one sample or one population cannot be verified and sample size is small.

2) The data is ordinal. A t-Test requires that the data be either ratio or interval but not ordinal. The Mann-Whitney U Test requires only the data be at least ordinal so that all of the data can be ranked. The specific difference between data values does not have to be measurable.

3) Either one of the sample groups has significant outliers. The Mann-Whitney U Test is based upon the rankings of data values and is therefore much affected by outliers than a t-Test, which is based on sample means.

The two-independent-sample t-Test compares the means of the two samples to determine if the means of the two populations are significantly different. The two populations are those from the two samples were taken.

 

Overview of the Mann-Whitney U

Test

The Mann-Whitney U Test performs a similar evaluation by comparing the ranks of one sample group to the average ranks of both sample groups to determine if the ranks of each of the two populations are significantly different. The two populations are those from the two samples were taken.

The Mann-Whitney U Test is based upon the adjusted sum of rankings of values in each of the two samples. All of the data from both sample groups is combined into one group and ranked. The data, along with their rankings are then returned to their original groups. The rankings of each group are summed up separately and then adjusted. The smaller of the two adjusted sums is designated as the Test Statistic U.

If the required assumptions of the Mann-Whitney U Test are met, the Test Statistic is approximately normally distributed. A z Score is calculated based upon the Test Statistic and then compared to a Critical z Value based upon the specified alpha and number of tails in the test.

The Null Hypothesis is rejected or not rejected based upon the z Score is further from the standardized normal mean of zero than the Critical z Value.

The Null Hypothesis of the Mann-Whitney U Test is similar to the Null Hypothesis of a two-independent sample t-Test. The t-test would have the following Null Hypothesis:

H0: x_bar1 – x_bar2 = Constant

The Mann-Whitney U Test would have the following Null Hypothesis:

H0: Sum_of_Ranks1 – Sum_of_Ranks2 = 0

If the Null Hypothesis cannot be rejected, the sample groups are not considered to be different at the specified level of significance. If this Null Hypothesis can be rejected, the sample groups are considered to be different.

The Mann-Whitney U Test can be performed as a one or two-tailed test. As with most hypothesis tests, the operator in the Alternative Hypothesis determines whether the test is one or two-tailed.

A non-directional operator (a “not equal” sign) in the Alternative Hypothesis indicates a two-tailed test. If the Mann-Whitney U Test is to be performed as a one-tailed test, it will always be performed in the left tail regardless of which sample is expected to have the largest rank sum. The Test Statistic U for the Mann-Whitney U Test is always based upon the sample with the lowest rank sum. This will be discussed further in this section.

 

Required Assumptions of the Mann-

Whitney U Test

1) The data are at least ordinal so that the data can be ranked. Differences between sample data points do not have to be measurable.

2) All data observations are independent of each other.

3) The sum of sample sizes, n1 + n2, equals at least 20.

4) Both samples have similar distribution shapes. A histogram of each sample will display the shape of the data’s distribution.

If these assumptions are met, the Test Statistic U will have an approximately normal distribution. The Mann-Whitney U test is based upon the Test Statistic U being approximately normally distributed. Test Statistic U is the sum of the ranks of the data in one of the two samples.

 

Mann-Whitney U Test in Excel With

This Example’s Data

Following are the data from the two data samples that will be compared in this Mann-Whitney U Test:

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test
(Click On Image To See a Larger Version)

 

Step 1 – Evaluate Whether the Required Assumptions Are Met

The required assumptions for the Mann-Whitney U Test are as follows:

1) The data are at least ordinal so that the data can be ranked. Differences between sample data points do not have to be measurable.

2) All data observations are independent of each other.

3) The sum of sample sizes, n1 + n2, equals at least 20.

4) Both samples have similar distribution shapes. A histogram of each sample will display the shape of the data’s distribution.

The first three assumptions have clearly been met. Histograms of each sample group have to be created to determine if both samples have similar distribution shapes. The following Excel histograms show that the sample groups have reasonably similar distribution shapes:

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test (Click On Image To See a Larger Version)

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test (Click On Image To See a Larger Version)

 

Step 2 – Create the Null and Alternative Hypotheses

The purpose of the original t-Test was to determine with 95 percent certainty whether Brand A batteries have a longer average lifetime than Brand B batteries. The Null and Alternative Hypotheses for this t-Test are the following:

H0: x_bar1-x_bar2 = 0

H1: x_bar1-x_bar2 > 0

The “greater than” operator in the Alternative Hypothesis indicates that this is a one-tailed test in the right tail.

The two-independent-sample t-Test compares the means of the two samples to determine if the means of the two populations are significantly different. The two populations are those from the two samples were taken.

The Mann-Whitney U Test performs a similar evaluation by comparing the ranks of one sample group to the average ranks of both sample groups to determine if the ranks of each of the two populations are significantly different. The two populations are those from the two samples were taken.

Just as with a two-independent-sample t-Test, the Mann-Whitney U Test can be performed as a two-tailed test or as a one-tailed test. The Alternative Hypothesis specifies which tail(s) the test will be focused on.

The Null Hypothesis for this Mann-Whitney U Test is as follows:

H0: U = Uaverage

There is one notable difference between a one-tailed t-Test and a one-tailed Mann-Whitney U Test. A one-tailed t-Test either the left of the right tail. A one-tailed Mann-Whitney U Test will always be performed in the left tail regardless of which sample is expected to have the larger rank sum.

The Alternative Hypothesis for this one-tailed test in the left tail is the following:

H1: U < Uaverage

The reason that a one-tailed Mann-Whitney U Test is always performed in the left tail is the Test Statistic U is always less than Uaverage (which is the average of U1 and U2) because Statistic U is set to equal the smaller of the two adjusted sums of ranks, U1 and U2, for the two groups.

Uaverage = (U1 and U2)/2

It should be noted that the one-tailed t-Test was performed in the right tail. This one-tailed Mann-Whitney U Test is performed in the left tail.

Test Statistic U is calculated in the following steps.

 

Step 3 – Combine All of the Data Into a Single Column

Make sure that each data point has its group name in an adjacent cell. This will be necessary to return the data back to the original groups.

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test
(Click On Image To See a Larger Version)

 

Step 4 – Sort All of the Data

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test
(Click On Image To See a Larger Version)

 

Step 5 – Rank All of the Data

Ties (data that have the same values) are assigned the rank that is the average rank for all of the tied values. For example, the two tied data values of 26 would have been assigned the ranks of 9 and 10 if they were not tied. Since they are tied, they are both assigned the average rank of 9.5.

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test
(Click On Image To See a Larger Version)

 

Step 6 – Return the Data to the Original Two Groups

Sort all three columns simultaneously according the column that contains the name of the original group that each data value belongs.

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test
(Click On Image To See a Larger Version)

 

Step 7 – Calculate R and n For Each Sample Group

R equals the sum of the ranks for each group and n is the sample size of each group.

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test (Click On Image To See a Larger Version)

clip_image009 (Click On Image To See a Larger Version)

 

Step 8 – Calculate U1 and U2

U1 and U2 are adjusted rank sums for the two groups.

U1 = R1 – n1(n1 + 1)/2

U1 = 314 – 16(16 – 1)/2 = 178

U2 = R2 – n2(n2 + 1)/2

U2 = 247 – 17(17 – 1)/2 = 94

 

Step 9 – Set-Test Statistic U to the Smaller of U1 or U2

U1 = 178

U2 = 94

Test Statistic U = 94

 

Step 10 – Calculate the Mean and Standard Deviation of U

U_bar = (U1 + U2) / 2 = n1*n2 / 2 = 136

sU = SQRT( n1 * n2 * (n1 + n2+ 1) / 12) = 27.76

 

Step 11 – Calculate the z Score and Critical z Value

It should be noted that the one-tailed t-Test was performed in the right tail. This one-tailed Mann-Whitney U Test is performed in the left tail because Test Statistic U is always set to equal the smaller of U1 and U2.

z Score = (U – U_bar)/ sU

z Score = (94 – 136)/27.76 = -1.5130

Critical z Value α=0.05,one-tailed,left tail = NORM.S.INV(α)

Critical z Value α=0.05,one-tailed,left tail = NORM.S.INV(0.05) = -1.6448

If this were a two-tailed test, the Critical z Values would be calculated as follows:

Critical z Values α=0.05,two-tailedl = ±NORM.S.INV(1 – α/2) = ±1.9599

 

Step 12 – Determine Whether or Not to Reject the Null Hypothesis by Comparing the z Score to the Critical z Value

The Null Hypothesis is rejected if the z Score is farther from the standardized normal distribution’s mean of zero than the Critical z Value. This is not the case here because the z Score (-1.5130) is closer to the standardized mean of zero than the Critical z Value (-1.6448). There is not enough evidence to reject the Null Hypothesis at an alpha level of 0.05.

This one-tailed, left tail, Mann-Whitney U Test was not sensitive enough to detect a difference at α = 0.05. The Null Hypothesis, which states that the adjusted rank sum of one of the groups is not different than the average adjusted rank sum of both groups, is not rejected. The rankings of the data in each group are not found to be significantly different at an alpha level of 0.05. The two populations from which the samples were taken are not assumed to have different rankings. This one-tailed Mann-Whitney U Test did not detect a difference in the two populations based on the two samples taken from the populations. This information is shown in the following Excel-generated graph:

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test (Click On Image To See a Larger Version)

The equivalent one-tailed, right tail, two-independent-sample t-Test was sensitive enough to detect a difference at α = 0.05. The t value (1.79) was further from the standardized mean of zero than the Critical t Value (1.69). The Null Hypothesis of this t-Test, which states that the means of both populations are not different, is rejected. This one-tailed t-Test did detect a difference in the two populations based on the two samples taken from the populations. This information is shown in the following Excel-generated graph:

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test (Click On Image To See a Larger Version)

You may have noticed that the p Value (red region in the chart) appears in the left tail in the Mann-Whitney U Test but appears in the right tail in the t-Test graph directly above.

It should be noted that the Mann-Whitney U Test is always has its p value (red region in the graph) in its left tail. This is due to the negative z Score. The z Score calculated in the Mann-Whitney U Test will always negative because Test Statistic U is always set to equal the smaller of U1 and U2.

The formula for this z Score is the following:

z Score = (U – U_bar)/ sU

This z Score is negative because U is always less than U_bar.

The p Value for a t-test can appear in the right or left tail because the t Value of a t-Test can be positive or negative. The t Value in this t-Test is positive because the formula for the t Value is the following:

mann-whitney,nonparametric testing, nonparametric testing,parametric,normality,t-test,t test,excel,excel 2010,excel 2013,normality test
(Click On Image To See a Larger Version)

x_bar1 = 43.56

x_bar2 = 33.53

Constant = 0

This t Value is positive because x_bar1 - x_bar2 - Constant is positive.

Nonparametric tests generally have less power (ability to detect a difference) than their parametric equivalents. One way to increase the likelihood that a nonparametric test will detect a difference is to increase alpha. Increasing alpha decreases the required level of certainty because of the following relationship:

Alpha – 1 – Level of Required Certainty

If alpha were doubled from a value of α = 0.05 to α = 0.10, the Critical z Value is changed from a value of -1.6448 to -1.2816. The Mann-Whitney U Test would have detected a difference in this case.

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)