A Mann-Whitney U test (sometimes called the Wilcoxon rank-sum test) is used to compare the differences between two samples when the sample distributions are not normally distributed and the sample sizes are small (n <30).
It is considered to be the nonparametric equivalent to the two sample t-test.
This tutorial explains how to perform a Mann-Whitney U test in Excel.
Example: Mann-Whitney U Test in Excel
Researchers want to know if a fuel treatment leads to a change in the average mpg of a car. To test this, they conduct an experiment in which they measure the mpg of 12 cars with the fuel treatment and 12 cars without it.
Because the sample sizes are small and they suspect that the sample distributions are not normally distributed, they decided to perform a Mann-Whitney U test to determine if there is a statistically significant difference in mpg between the two groups.
Perform the following steps to conduct a Mann-Whitney U test in Excel.
Step 1: Enter the data.
Enter the data as follows:
Step 2: Calculate the ranks for both groups.
Next, we’ll calculate the ranks for each group. The following image shows the formula to use to calculate the rank of the first value in the Treated group:
Although this formula is fairly complicated, you only have to enter it one time. Then, you can simply drag the formula to all of the other cells to fill in the ranks:
Step 3: Calculate the necessary values for the test statistic.
Next, we’ll use the following formulas to calculate the sum of the ranks for each group, the sample size for each group, the U test statistic for each group, and the overall U test statistic:
Step 4: Calculate the z test statistic and the corresponding p-value.
Lastly, we’ll use the following formulas to calculate the z test statistic and the corresponding p-value to determine if we should reject or fail to reject the null hypothesis:
The null hypothesis of the test states that the two groups have the same mean mpg. Since the p-value of the test is (0.20402387) is not smaller than our significance level of 0.05, we fail to reject the null hypothesis.
We do not have sufficient evidence to say that the true mean mpg is different between the two groups.
Great method. Used it and worked well. I compared results with SPSS. I believe the above method is giving me the EXACT significance. However, we often use the asymptotic significance. SPSS issues both. Is there was a way to extrapolate this?
Hey, I’m wondering for the z value you have above, at the end of the formula it shows /12. Is this always 12, or is it 12 in your case because you have n=12 samples in both groups?
In my case, I have one group with A = 18 samples and B = 25 samples. Would I still but /12 at the end of the z calculation or do I use a different value?
Thanks
for the z score formula, why is it /12 at the very end? What is that 12 coming from?
Thank you!!!!!
What formular did you use for the rank
Zach, you have not provided the rank in your first step. RANK would be a simple 2-13 ordering of largest to smallest or vice versa. Your formula, which did not work in Excel (too many arguments) is doing something else… finding an average and then ranking each cell according to that?? I’m not sure. I wish you would explain this further. What you call “RANK” is not what the Excel formula expects at all. And I don’t think a normal RANK would work to give us the values in the Mann-Whitney U test. Please tell us what you are doing in the first step.
Th formula for z has a “/12”, this would be “/E18”, right?
Step 4 : End of formula you divide by 12. Where did this number come from? Is it based on the n? And if so what do you do if you have different counts in each group?
Hi,
In cell F26 you divide by 12. It’s strange. I would look better to divide by MAX(E18,E19)?
I don’t know what is the math formula for z, but this constant “12” bothers me.
Cheers,
Christian
Hi Zach, thanks for this. Can you explain why we have to divide by 12 to calculate the z-value? Shouldn’t that be a value related to sample size? Thank you very much, Jitta
The formula in text would have been more helpful than an image so that I could just copy and paste it.
Can you write the formula in text so I can copy and paste?
Can you post the equations for this test? I’m not sure if the order of operations you have posted in the formulas is correct.
Thanks for the clear instructions!
One-tailed tests would also be interesting.
And if the number of ties is considerable, the z-value would need a correction.
Hey Zach,
I have a question about the z formula in step 4…you set everything over 12 which is the same value as your N1 and N2. However, I am using the mann whitney u with unequal sample sizes. Should I use the smallest N, the largest, or the average in between? Thanks!
Awesome… Simple yet profound way of teaching Statistics to the core.
Thanks for this…..how can you do this for two unequal sample sizes?
for the z-score, did you divide by 12 in this equation =(E24-E18*E19/2)/SQRT(E18*E19*(E18+19+1)/12) because you had 12 #? I have different amounts #s for my two groups so what # should I use for the z score? thank you for your help!
Dude, you couldn’t copy the formula out into a line of text? We have to try to decipher it from a tiny tiny image??
For the Z-test, where does the “12” come from in the equation? Is it the sample size or just or something intrinsic to the equation itself.
Thanks
When you calculate the Z test statistic, why do you use 12 at the very end of the calculation? Is that some constant for the calculation or is it related to the groups you’re working with in this example? Would that 12 be the same in every situation of Z test statistic calculations?
Thank you for the wonderful example.
Can I ask whether in the z-test the 12 corresponds to the sample size?
what if the sample size is 6?
The “12” in the formula is a required part of the formula for the standard deviation, which is then used to get the z-score. As for the ranking formula, use RANK.AVG. It’s simple and less prone to typos. Finally, regarding the “tiny tiny image”, Most browsers allow you to zoom in and increase the size. Take a screen shot of the “tiny” formula, enlarge it, and paste it on your worksheet (I know, it will be fuzzy) then type your formula into your work. When finished and tested, kill the mini screen shot.
why do we multiply by 2 the p value
Hi saranya…The Mann-Whitney U Test is a non-parametric test used to determine whether there is a significant difference between the distributions of two independent groups. When performing this test, the p-value helps to determine whether the observed data is significantly different from what you would expect under the null hypothesis.
### Why Multiply the p-value by 2?
The multiplication by 2 is related to the nature of the test being two-tailed or one-tailed:
– **One-Tailed Test**: If you are only interested in whether one group tends to have higher (or lower) values than the other, you would use a one-tailed test. In this case, the p-value you calculate directly from the U statistic reflects this single direction.
– **Two-Tailed Test**: Often, you’re interested in whether the distributions are different in either direction—whether one group is greater or less than the other. This is called a two-tailed test. In a two-tailed test, you need to account for the possibility that the difference could be in either direction, so the p-value is doubled.
Thus, when performing a two-tailed Mann-Whitney U Test in Excel, if you calculate a p-value for one direction (say, whether Group A is greater than Group B), you would multiply it by 2 to account for the possibility of the opposite direction (Group B being greater than Group A).
### Example:
– Suppose you calculate a p-value of 0.03 for the one-tailed test.
– For a two-tailed test, you multiply this by 2, resulting in a p-value of 0.06.
If your significance level (α) is 0.05, the two-tailed test would not be significant, while the one-tailed test might be. This adjustment is crucial for accurate hypothesis testing based on the test’s context.
Is there a way to do this with unequal sample sizes?
Hi Erin…Yes, you can run a Mann-Whitney U test even when your two groups have different numbers of observations. The test was designed for exactly that situation. Excel itself doesn’t include a direct Mann-Whitney button, but there are a few practical ways to do it.
One option is to work directly in Excel using ranks. Place all the data in a single column, assign ranks with the RANK.AVG function, then add up the ranks for one group. With that rank sum you can calculate the U statistic using the formula
U = n1*n2 + n1*(n1 + 1)/2 – R1,
where n1 and n2 are the sample sizes and R1 is the sum of ranks for the first group. From there you can compare U to critical values or convert it to a z-score for a p-value.
Another option is to install a free add-in like the Real Statistics Resource Pack. It includes a simple function MANN\_WHITNEY(R1, R2, TRUE/FALSE) that accepts ranges of any size and does the calculations for you.
If you prefer, you can also copy the Excel data into a tool such as R or Python and run the test with wilcox.test in R or scipy.stats.mannwhitneyu in Python.
The main point is that unequal group sizes are not a problem for this test as long as the samples are independent and you are working with at least ordinal-level data.