This tutorial will demonstrate how to use the Wilcoxon Rank Sum Test (Mann Whitney U Test) in Excel and Google Sheets.

 

How to Perform the Wilcoxon Rank Sum Test in Excel

Wilcoxon Rank Sum Test Table of Critical Values

In this method, we compare the Wilcoxon Rank Sum test statistic with the critical value. Listed below is the step-by-step guide on how to perform the Wilcoxon Rank Sum test using the method of critical values.

Example

Background: A new drug has been produced for the treatment of headaches and pains. The producer claims that the drug relieves headaches fastest than the current drugs in the market. A researcher wants to test this claim, so, he randomly placed patients with severe headaches in two groups. He administered the new drug to one of the groups, and one of the popular older drugs to the other group and records the time, in minutes, it took for each person to feel relief from the headache. The result is presented in the table below. Test at a 5% significant level whether there is a significant difference in the time it takes for the new drug and the old drug to relieve headache.

First, assign the rank to each data point (Time) from the smallest to the largest using the RANK.AVG Function:

Note that we used absolute referencing for the range in the RANK.AVG Function so that we can easily paste the formula to other cells without changing the range.

Next, we obtain the sum of the ranks for each group (the old drug group and the new drug group).

Next, use the COUNT Function to obtain the sample sizes of the two groups as shown below:

The test statistics for the Wilcoxon Rank Sum Test is denoted by  U and is the smaller of U₁ and U₂ defined by:

Where n₁ and n₂ are the sample sizes of group 1 (old drug group) and group 2 (new drug group) respectively, and  R₁ and  R₂ are the sums of the ranks of group 1 and group 2 respectively.

Next, we calculate  U₁ and U₂ as follows:

Similarly, for U₂:

 

The test statistic , U, is the smaller of  U₁ and U₂ and is calculated as follows:

Now, obtain the critical value for the sample size of n₁= 15,  n₂ =15 and a significance level of 5% from the Wilcoxon Rank Sum table of critical values.

Note that we are doing a two-tailed test here because we are testing for the difference in values (and not increase or decrease in values) which is both ways, both increase and decrease in values.

An excerpt of the Wilcoxon Rank Sum (Mann-Whitney U) table of critical values for a two-tailed test is shown below:

So, the critical value is 64.

For the Wilcoxon Rank Sum test, we conclude that there is a significant difference between the groups when the Test Statistics is less than or equal to the Critical Value.

Here, the test statistic is 20 and the critical value is 64, since the test statistic is less than the critical value, we conclude that there is statistically significant evidence that the new drug relieves headaches faster than the old drug.

 

p-Value Method

Another method of performing the Wilcoxon Rank Sum test is by approximating the distribution to a normal distribution and then using the p-value of the approximated z-score to make a conclusion about the population of the groups.

In the following steps, we illustrate this method using the same example used in the previous method.

Example

Background: A new drug has been produced for the treatment of headaches and pains. The producer claims that the drug relieves headaches fastest than the current drugs in the market. A researcher wants to test this claim, so, he randomly placed patients with severe headaches in two groups. He administered the new drug to one of the groups, and one of the popular older drugs to the other group and records the time, in minutes, it took for each person to feel relief from the headache. The result is presented in the table below. Test at a 5% significant level whether there is a significant difference in the time it takes for the new drug and the old drug to relieve headache.

We are going to follow all the steps described in the previous method to obtain the sum of the ranks of the two groups and the sample sizes.

For this method, the test statistic, T, is the smaller of the sum of the ranks of the two groups, which is obtained as follows:

Now, we will obtain the mean and the standard error of the distribution. The mean (μ) of the distribution is given by:

where n_s and n_L are the sample sizes of the smaller rank-sum and the larger rank-sum groups respectively, and the standard error is given by:

which is equivalent to:

For our case, n_s = n_L= 15. So, we calculate the mean as follows:

and the standard error as follows:

Next, calculate the z-score for the observations using the formula:

Finally, calculate the p-value for the observations using the NORM.DIST Function or NORM.S.DIST Function. Since we are performing a two-tailed test, we multiply the result of the NORM.DIST or NORM.S.DIST Functions by 2.

The NORM.DIST Function has the syntax: NORM.DIST(x, mean, standard_dev, cumulative), where  is the test statistic, mean is as calculated from the data, standard_dev is the standard error, and select ‘TRUE’ for the cumulative distribution function.

The NORM.S.DIST Function has the syntax: NORM.S.DIST(z, cumulative), where  is the z-score, and select ‘TRUE’ for the cumulative distribution function.

The p-value here is  which is less than our significant level of 5% or 0.05. Therefore, ‘reject the null hypothesis’ which means that we conclude that there is statistically significant evidence that the new drug relieves headache faster than the old drug.

 

The Wilcoxon Signed-Rank Test in Google Sheets

The Wilcoxon Signed-Rank test can be calculated in similar ways in Google Sheets as in Excel as shown below.

For the critical value method, we have: