The Mann-Whitney U test is essentially an alternative form of the Wilcoxon Rank-Sum test for independent samples and is completely equivalent.
Define the following test statistics for samples 1 and 2 where n1 is the size of sample 1 and n2 is the size of sample 2, and R1 is the adjusted rank sum for sample 1 and R2 is the adjusted rank sum of sample 2. It doesn’t matter which sample is bigger.
As for the Wilcoxon version of the test, if the observed value of U is < Ucrit then the test is significant (at the α level), i.e. we reject the null hypothesis. The values of Ucrit for α = .05 (two-tailed) are given in the Mann-Whitney Tables.
Example 1: Repeat Example 1 of the Wilcoxon Rank Sum Test using the Mann-Whitney U test.
Figure 1 – Mann-Whitney U Test
Since R1 = 117.5 and R2 = 158.5, we can calculate U1 and U2 to get U = 39.5. Next we look up in the Mann-Whitney Tables for n1 = 12 and n2 = 11 to get Ucrit = 33. Since 33 < 39.5, we cannot reject the null hypothesis at α = .05 level of significance.
Property 2: For n1 and n2 large enough the U statistic is approximately normal N(μ, σ) where
Observation: Click here for proofs of Property 1 and 2.
Property 3: Where there are a number of ties, the following revised version of the variance gives better results:
where n = n1 + n2, t varies over the set of tied ranks and ft is the number of times (i.e. frequency) the rank t appears. An equivalent formula is
Observation: A further complication is that it is often desirable to account for the fact that we are approximating a discrete distribution via a continuous one by applying a continuity correction. This is done by using a z-score of
instead of the same formula without the .5 continuity correction factor.
Example 2: Repeat Example 2 of the Wilcoxon Rank Sum Test using the Mann-Whitney U test.
We show the results of the one-tailed test (without using a ties correction) is shown in Figure 2. Column W displays the formulas used in column T.
Figure 2 – Mann-Whitney U test using normal approximation
As can be seen in cell T19, the p-value for the one-tail test is the same as that found in Wilcoxon Example 2 using the Wilcoxon rank-sum test. Once again we reject the null hypothesis and conclude that non-smokers live longer.
Real Statistics Excel Functions: The following functions are provided in the Real Statistics Pack:
MANN(R1, R2) = U for the samples contained in ranges R1 and R2
MANN(R1, n) = U for the sample contained in the first n columns of range R1 and the sample consisting of the remaining columns of range R1. If the second argument is omitted it defaults to 1.
MTEST(R1, R2, tails) = p-value of the Mann-Whitney U test for the samples contained in ranges R1 and R2. tails = # of tails: t = 1 (default) or 2.
MTEST(R1, n, tails) = p-value of the Mann-Whitney U test for the sample contained in the first n columns of range R1 and the sample consisting of the remaining columns of range R1. If the second argument is omitted it defaults to 1. tails = # of tails: t = 1 (default) or 2.
MCRIT(n1, n2, α, tails, h) = critical value of the Mann-Whitney U test for samples of size n1 and n2, for the given value of alpha and tails = 1 (one tail) or 2 (two tails) based on the Mann-Whitney Table. If h = TRUE (default) harmonic interpolation is used; otherwise linear interpolation is used.
MPROB(x, n1, n2, tails, iter) = an approximate p-value for the Mann-Whitney test for the U value equal to x for samples of size n1 and n2 and tails = 1 (one tail) or 2 (two tails, default) based on an interpolation of the values in the table in Mann-Whitney Table, using iter number of iterations (default = 40) to calculate the approximation.
Note that the values for α in the table in Mann-Whitney Table range from .01 to .1 for tails = 2 and .005 to .05 for tails = 1. If the p-value produced by the MPROB function is less than .01 (tails = 2) or .005 (tails = 1) then the p-value in MPROB is given as 0 and if the p-value is greater than .1 (tails = 2) or .05 (tails = 1) then the p-value in MPROB is given as 1.
Any empty or non-numeric cells in R1 or R2 are ignored.
Observation: In Example 1, we can use Real Statistics functions to arrive at the same value for U, namely MANN(A6:B17) = 39.5. Also MCRIT(H5,I5,H9,H10) = MCRIT(12, 11, .05, 2) = 33 (the value in cell H12 of Figure 1). Finally note that the p-value = MPROB(H5,I5,H9,H10) = MPROB(39.5, 12, 11, 2) = 1 (meaning that p-value > .1), and so once again we can’t reject the null hypothesis.
If U had been 32, then p-value = MPROB(32, 12,11, 2) = 0.044 < .05 = α, and so we would reject the null hypothesis. This is consistent with the fact that U = 32 < 33 = Ucrit..
Similarly in Example 2, we can use Real Statistics functions to arrive at the same value for U, namely MANN(A6:H15,4) = MANN(A6:D15,E6:H15) = 486, as well as the same p-value (assuming a normal approximation described above), namely MTEST(A6:H15,4) = MTEST(A6:D15,E6:H15) = 0.003081.
Also note that the supplemental functions RANK_COMBINED and RANK_SUM, as defined in Wilcoxon Rank-Sum Test, can be used in conjunction with the Mann-Whitney test.
Observation: The effect size for the data using the Mann-Whitney test can be calculated in the same manner as for the Wilcoxon test, and the result will be the same.
The effect size of .31 for the data in Example 2 is calculated as in Figure 2. Namely, the z-score (cell T17) is calculated using the formula =(T13-T14)/T16 and the effect size (cell 20) is calculated by the formula =ABS(T17)/SQRT(T6+U6).
Also note that the z-score and the effect size r can be calculated using the supplemental function MTEST as follows:
z-score = NORMSINV(MTEST(R1, R2))
r = NORMSINV(MTEST(R1, R2))/SQRT(COUNT(R1)+COUNT(R2))
Observation: The results of analysis for Example 2 can be summarized as follows: The life expectancy of non-smokers (Mdn = 76.5) was significantly higher than that of smokers (Mdn = 70.5), U = 486, z = -2.74, p = .0038 < .05, r = .31.
Real Statistics Function: The Real Statistics Pack also provides the following array function for the samples in ranges R1 and R2 where alpha is the α value (default .05) and tails is the number of tails (1 or 2 = default).
MANN_TEST(R1, R2, lab, tails, alpha, ties, cont): returns the following values in a 7 × 1 column range: U, alpha, tails, z, r, U-crit, p-value. If ties = TRUE (default) the ties correction factor of Property 3 is applied. If cont = TRUE (default) then the continuity correction is applied. If lab = TRUE then an extra column with labels is included.
If the size of the two samples is 26 or less, i.e. COUNT(R1) + COUNT(R2) ≤ 26, then an exact test will be performed. In this case, the output is a 9 × 1 column range (or a 9 × 2 range if lab = TRUE), including U-crit (exact) and p-value (exact).
For Example 2, the array formula =SRANK_TEST(B4:B33,C4:C33,TRUE,1,.05,FALSE) returns the following array for the one-tailed test with continuity correction but no correction for ties:
Figure 3 – Output from MANN_TEST
Real Statistics Data Analysis Tool: The Real Statistics Resource Pack also provides a data analysis tool which performs the Mann-Whitney test for independent samples, automatically calculating the medians, rank sums, U test statistic, z-score, p-value and effect size r.
For example, to perform the analysis in Example 1, enter Ctrl-m and choose the T Test and Non-parametric Equivalents. The dialog box shown in Figure 4 appears.
Figure 4 – Dialog box for Real Statistics Mann-Whitney Test
Enter A5:B17 as the Input Range 1 (although we could insert A5:A17 in Input Range 1 and B5:B17 in Input Range 2) click on Column headings included with data and choose the Two independent samples and Non-parametric options and click on the OK button. Keep the default of 0 for Hypothetical Mean/Median (this value is not used anyway) and .05 for Alpha. For this version of the test, we check Use continuity correction, Include exact test and Include table lookup but we leave the Use ties correction option unchecked.
The output is shown in Figure 5.
Figure 5 – Mann-Whitney test data analysis tool output
Both the one-tail and two-tail tests are shown. Also, three versions of the test are shown: the test using the normal approximation (range O18:P20), the test using the critical values (range O22:P23) from the Mann-Whitney Table and the exact test (range O25:P26) as described later on this webpage.
If we check the Use Ties correction in Figure 4 we would obtain the output shown in Figure 6.
Figure 6 – Mann-Whitney test data analysis tool with ties correction
In this case the ties correction of Property 3 is applied to the normal approximation (range U18:V20). As you can see there is very little difference between the outputs shown in Figure 5 and 6.
Note too that the ties correction (as well as the continuity correction) only applies to the normal approximation. The table and exact versions of the test do not apply the ties or continuity correction.
Real Statistics Function: The Real Statistics Pack also provides the following function to calculate the ties correction used in the data analysis tool.
TiesCorrection(R1, R2, type) = ties correction value for the data in range R1 and optionally range R2, where type = 0: one sample, type = 1: paired sample, type = 2: independent samples
For the Mann-Whitney test type = 2. The ties correction is used in the calculation of the standard deviation (cell U15 of Figure 6) as follows
Click here for a description of the exact version of the Mann-Whitney Test using the permutation function.
Confidence Interval of the Median
Click here for a description of how to calculate confidence interval of the median based on the Mann-Whitney Test.