Mann-Whitney Test for Independent Samples

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.

image950 image951

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.

Mann-Whitney test Excel

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 1:
image958

Property 2: For n1 and n2 large enough the U statistic is approximately normal N(μ, σ) where

image959

Observation: Click here for proofs of Property 1 and 2.

Observation: Where there are a number of ties, the following revised version of the variance gives better results [Ro]:

image960

where the sum is taken over all scores where ties exist and f is the number of ties at that level.

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 in Figure 2. Column W displays the formulas used in column T.

Mann-Whitney U Test

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 samples contained in the first n columns of range R1 and 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 samples contained in the first n columns of range R1 and 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) = 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.

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 a linear interpolation of the values in the table in Mann-Whitney Table, using iter number of iterations (default = 40).

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 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.

The MANN and MTEST functions ignore any empty or non-numeric cells.

Observation: In Example 1, we can use the supplemental function 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 the supplemental function 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): returns the following values in a 7 × 1 column range: U, alpha, tails, z, r, U-crit, p-value. If lab = TRUE then an extra column with labels is included. If ties = TRUE (default) a ties correction factor is applied as described above.

For Example 2, =MANN_TEST(A6:D15,E6:H15,TRUE,1,FALSE) returns the following array:

Mann-Whitney Excel function

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.

Mann-Whitney dialog box

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. Also make sure that Include exact test is checked and Use ties correction is unchecked.

The output is shown in Figure 5.

Mann Whitney output

Figure 5 – Mann-Whitney test data analysis tool output

If we check the Use Ties correction in Figure 4 we would obtain the output shown in Figure 6.

Mann-Whitney ties correction

Figure 6 – Mann-Whitney test data analysis tool with ties correction

As you can see there is very little difference between the outputs shown in Figure 5 and 6.

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

=SQRT(U14*((U6+V6)^3-(U6+V6)-TiesCorrection(A6:A17,B6:B17,2))/(6*((U6+V6)^2-(U6+V6))))

Exact Test

Click here for a description of the exact version of the Mann-Whitney Test using the permutation function.

22 Responses to Mann-Whitney Test for Independent Samples

  1. Robert Rooney says:

    I downloaded and installed the Resources Pack as per your website instructions and it shows as an addin under excel options (RealStats). But after I check it and click OK, no additional tools show up under Data Analysis. I have Excel 2007. Any ideas why the tools are not available?

    • Charles says:

      Robert,

      Once you install the Real Analysis Resource Pack, the additional tools are available by simply pressing Ctrl-m. This will bring up a menu with all the Real Statistics data analysis tools. I thought that this would be the easiest approach since this can be done no matter which ribbon is active.

      The other recommended approach is to add the Real Statistics tools to the Quick Access Toolbar (QAT), especially since the QAT is also available no matter which ribbon is active. The instructions for doing this are included in webpage http://www.real-statistics.com/excel-capabilities/supplemental-data-analysis-tools/accessing-supplemental-data-analysis-tools/, although not everyone has been successful at getting this to work.

      Excel doesn’t let you customize the ribbon by adding an addin to an existing group (such as Data>Data Analysis). Instead you can add the addin as a custom group on any of the ribbons (e.g. right next to Data Analysis on the Data ribbon). Instructions for doing this are now available on the same web page as the one referenced above.

      Charles

  2. Aaron says:

    Does MTEST always report a p-value for a 2-tailed test? Can you do a 1-tailed test with the function?

    • Charles says:

      Aaron,
      The MTEST (as well as the T Tests and Non-parametric Equivalents data analysis tool) reports the p-value of the one-tail test. To get the two-tail test you simply double the answer.
      I had intended to report the two-tail test, in which case you would have had to half the p-value to get the one-tail test. I will fix this in the next release, but for now MTEST reports the one-tail test.
      Charles

      Update: In the latest release (R2.1) the T Tests and Non-parametric Equivalents data analysis tool reports both the one tail and two tail tests.

  3. J. Basu says:

    Charles:
    Can you please tell me:
    1)How I can use the “rank” function in Excel to rank continuous variables, that is numbers with decimal points, e.g., 1.38, 3.6. 40.9 etc. When I’m trying to rank them, the number like 1.38 that is enlisted in the array twice is not being properly ranked although I have used the following formula to correct for repeat numbers:
    =Rank(number, range, order)+(count(range)+1-rank(number, range, 1)-rank(number, range, 0))/2. However, the number like 70.4 which is also listed twice is being correctly ranked.

    My second question is if the sample size of the two groups are more than 20, e.g., 30, 40 etc., I cannot use the Mann Whitney table. I have to calculate the z score. But which table do I then use to look up if the computed U value is above or below the critical value?

    The third question is do I have to pay to download the “Real Statistics tool pack”?

    Will look forward to your response.

    With sincerest thanks for your comments,
    JB

    • Charles says:

      Hi JB,

      Q1: I am not sure why you are using such a complicated formula. I use =RANK(x,R1) + (COUNTIF(R1,x)-1)/2 and it works fine. I tried it with repeated values of 1.38, 3.6 and 70.4 and it works as you would expect. You can also use RANK.AVG in Excel 2010/2013. With earlier versions of Excel you can use the function RANK_AVG found in the Real Statistics tool pack.

      Q2: For larger sample sizes, you don’t need a table. The idea is that the z value is normally distributed and so you can use the NORMSDIST function. This is easier. Easier still is to use the MTEST function or T-Test and Non-parametric Equivalents data analysis function found in the Real Statistics tool pack.

      Q3: You can download the Real Statistics tool pack for free.

      Charles

  4. J. Basu says:

    Thank you.

  5. Amber Davidson says:

    Any chance you can get the mean and standard deviation for two tailed Mann-Whitney U Test? I assume when it says Wilcoxon Signed-Rank Test for Paired Samples after I do the test it is actually the Mann-Whitney U Test, correct?

    And what if I’m dealing with time? Do I still leave the Mean/Median at 0? Just want to make sure it doesn’t mess up my results.

    And if this works, you are a LIFESAVER!

    • Charles says:

      Amber,

      The mean and standard deviation provided work for both the one-tail and two-tail tests. I just didn’t write the information twice (e.g. in Figure 4) since it is the same.

      The Wilcoxon Signed-Rank Test for Paired Samples is not the same as the Mann-Whitney U Test, although they have many characteristics in common. If you have paired samples you should use the test described on the webpage http://www.real-statistics.com/non-parametric-tests/wilcoxon-signed-ranks-test/.

      The Hypothetical Mean/Median field is not used with the current implementation of the Mann-Whitney Test or Wilcoxon Signed-Rank Test for Paired Samples, and so you may assume that the value is 0.

      Charles

  6. Saad says:

    Hi Charles

    I noticed that calculating p-value using
    MTEST(R1, R2, t) or
    Ctrl-m and choose the T Test and Non-parametric Equivalents

    gives different results (~ 10% different)!
    Any insight pls?
    Thanks

    • Charles says:

      Saad,
      I have never seen this before. It sounds lie an error. Can you send me an example where this is the case?
      Charles

    • Charles says:

      Hi Saad,
      In al the examples that I have seen, the function and data analysis tool give the same results. Can you send me the example where the two results are different?
      Charles

  7. Felix says:

    Hi and thank you for all your work! Your website is an amazing resource for me.

    As Fig. 4 shows there are two different output values for significance, rows 20 and 23. Where’s the difference? I got different results for some of my analyses and don’t know how to deal with it…

    Felix

    • Charles says:

      Hi Felix,

      Row 20 is based on the normal approximation (when the sample size is large), while row 23 is based on the exact value using the table of critical values. If the sample is large (sample size > 20) then no row 23 is generated. If row 23 is generated then you should use the results from row 23; otherwise you should use the results from row 20 (the only choice).

      Charles

  8. student says:

    sir, what stat tool i’m going to use if i have two groups with unequal number of respondents? I want to determine if there is a significant difference in their performance in terms of the knowing, applying and reasoning skills of the students between the control and experimental groups?

  9. Simao C Rodeia says:

    Hi,

    first of all, congratulation on the website. It’s really well built, as least for me, a null at stats. And it’s surely an enormous amount of work that you’ve put at our disposition.
    Thank you!

    Unfortunately, i’m running an older version of excel, who does not support your package.
    So, trying do go trough this test with samples n1=31 and n2=15, i’m having difficulty finding the critical value. furthermore, my results do not comply with the U1+U2 = n1n2 property… actually, U1 = U2 = n1n2…
    i’ve gone through every formula and i did not find any mistake…

    Could you help me?
    Thanks in advance. Cheers,
    Simao

    • Charles says:

      Simao,
      1. It is very strange that your results don’t comply with U1+U2 = n1*n2, since mathematically this should always hold. If you send me a spreadsheet with your calculations, I will try to figure out where the problem is.
      2. The critical value for alpha = .05, two-tailed, n1 = 31 and n3 = 15 is 148.
      3. The Real Statistics Resource Pack works with all Windows versions of Excel back to Excel 2002. Do you have an older version than this or are you using a version for the Mac prior to Excel 2011?
      Charles

  10. Nina says:

    Hi Charles,
    my compliments on the blog!
    I was wondering if you could perhaps explain how the formulas in Example 2 change if you would like to calculate the values for a 2-tailed test.
    Best
    Nina

  11. Nina says:

    Never mind, it just occurred to me the p-value for a 2-tailed test is (probably) twice the p-value of the one-tailed test :)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>