*59*

The Wilcoxon Signed-Rank Test is the non-parametric version of the paired samples t-test.

It is used to test whether or not there is a significant difference between two population means when the distribution of the differences between the two samples cannot be assumed to be normal.

This tutorial provides a step-by-step example of how to conduct a Wilcoxon Signed-Rank Test in Excel.

**Step 1: Create the Data**

Suppose an engineer want to know if a new fuel treatment leads to a change in the average miles per gallon of a certain car. To test this, he measures the mpg of 12 cars with and without the fuel treatment.

Weâ€™ll create the following data in Excel to hold the mpg values for each car with the fuel treatment (group1) and without the fuel treatment (group 2):

**Step 2: Calculate the Difference Between the Groups**

Next, weâ€™ll calculate the difference between the groups:

**Step 3: Calculate the Absolute Differences**

Next, weâ€™ll calculate the absolute difference between the groups, returning a blank if the absolute difference is zero:

**Step 4: Calculate the Rank of the Absolute Differences**

Next, weâ€™ll use the **RANK.AVG()** function to calculate the rank of the absolute differences between the groups, returning a blank if the absolute difference is zero:

**Step 5: Calculate the Positive & Negative Ranks**

Next, weâ€™ll calculate the positive ranks:

And weâ€™ll calculate the negative ranks:

**Step 6: Calculate the Test Statistic & Sample Size**

Lastly, weâ€™ll calculate the test statistic which is simply the smaller of the sum of the positive ranks or the sum of the negative ranks:

And weâ€™ll calculate the sample size, which is the total number of ranks that arenâ€™t equal to zero:

The test statistic turns out to beÂ **10.5** and the sample size isÂ **11**.

In this example, the Wilcoxon Signed-Rank Test uses the following null and alternative hypotheses:

**H _{0}:Â **The mpg is equal between the two groups

**H _{A}:Â **The mpg isÂ

*notÂ*equal between the two groups

To determine if we should reject or fail to reject the null hypothesis, we can find the critical value that corresponds to Î± = .05 and a sample size of 11 in the following Wilcoxon Signed Rank Test Critical Values Table:

The critical value that corresponds to Î± = .05 and a sample size of 11 isÂ **10**.

Since the test statistic (10.5) is not less than the critical value of 10, Â we fail to reject the null hypothesis.

We do not have sufficient evidence to say that the mean mpg is not equal between the two groups.

**Bonus:** Feel free to use this Wilcoxon Signed-Rank Test Calculator to automatically calculate the test statistic for a Wilcoxon Signed-Rank Test.