This guide will explain how to perform a Wilcoxon signed rank test in Excel.
We can use a Wilcoxon test as an alternative to a paired t-test.
The Wilcoxon Signed rank test, also known as the Wilcoxon matched pairs test, is a type of non-parametric statistical test you can use to compare data.
Non-parametric tests are often used when you cannot assume that your data follow a normal distribution.
The Wilcoxon signed rank test involves calculating the absolute differences between pairs of observations from both groups being compared.
After finding the absolute differences, we’ll rank the absolute differences in ascending order. We can obtain the test statistic by finding the smaller sum between the positive ranks and the negative ranks.
Let’s look at a simple use case where we can perform a Wilcoxon signed rank test in Excel.
Now that we know when to use a Wilcoxon signed-rank test, let’s learn how to use it on an actual sample spreadsheet.
A Real Example of Performing a Wilcoxon Signed Rank Test in Excel
The following section provides an example of how to perform a Wilcoxon signed-rank test. We will also explain the formulas and tools used in these examples.
First, let’s take a look at our sample data. We have two sample groups with 22 observations. We want to identify whether the mean values of both groups are statistically significant from each other.
The spreadsheet below shows an example of a Wilcoxon signed rank test in Excel. We were able to arrive at a test statistic of 45.5
To get the value of the test statistic, we just need to use the following formula:
=MIN(SUM(F2:F23),SUM(G2:G23)
We can compare our test statistic to the appropriate critical value. The critical value of our dataset can be found using the Wilcoxon signed-rank table below.
We can reject the null hypothesis if the test statistic is lower than the critical value.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try conducting a Wilcoxon signed-rank test in Excel, head over to the next section to read our step-by-step breakdown on how to do it!
How to Perform a Wilcoxon Signed Rank Test in Excel
This section will guide you through each step needed to perform the Wilcoxon signed-rank test in Excel.
Follow these steps to start using the Wilcoxon signed-rank test:
- First, we’ll get the difference between pairs coming from both sample groups.
- We’ll use the ABS function to find the absolute difference between pairs. If the difference is 0, we must leave a blank or empty string. We can use the IF function to return an empty string instead of 0.
- Next, we must rank the absolute differences for each pair. Type ‘=RANK.AVG(‘ to start the
RANK.AVGfunction.
- Input the value we want to rank as the first argument of
RANK.AVG.
- Next, input the entire range to compare the first argument with. You must also set the third argument to 1 to indicate we want an ascending ranking.
- Use the Fill Handle tool to return the rank of the rest of the matched pairs.
- We’ll use the formula
=IF(C2>0,E2,””)to create a new column that only shows ranks with a positive difference.
- We’ll use the formula ‘=IF(C2<0,E2,””)’ to create another column displaying all negative ranks.
- We’ll now compute the test statistic of the Wilcoxon signed rank test. We’ll find the sum of the positive ranks and negative ranks and use the MIN function to return the smaller sum.
- Hit the Enter key to evaluate the formula. In this example, we’ve calculated a test statistic of 45.5.
- We’ll use the
COUNTfunction to return the sample size. We can use the Wilcoxon critical value table seen earlier to determine the critical value for the test.
Assuming an alpha of 0.05 and a sample size of 22, our critical value is designated as 65. Since our test statistic is less than 65, we can reject the null hypothesis.
This indicates a significant difference between the medians of both samples.
These are all the steps required to perform a Wilcoxon signed rank test in Excel.
This step-by-step guide should provide you with all the information you need to start performing a Wilcoxon signed rank test in Excel.
We can use the Wilcoxon test to compare data that are not normally distributed
The Wilcoxon signed rank test is just one example of the many statistical tests you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
With so many other Excel functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!
