Table of Contents
The Wilcoxon Signed Rank Test is a statistical test used to compare two dependent samples to determine if there is a significant difference between them. This test is commonly used in research and data analysis to analyze paired data that may not follow a normal distribution. To perform a Wilcoxon Signed Rank Test in Excel, you will need to first organize your data into two columns, one for each sample. Then, you will need to calculate the differences between the two samples and rank them from smallest to largest. Finally, you can use the built-in function in Excel to calculate the test statistic and p-value, which will determine if there is a significant difference between the two samples. This test can be a useful tool in analyzing data and making informed decisions based on the results.
Perform a Wilcoxon Signed Rank Test in Excel (Step-by-Step)
The is the non-parametric version of the .
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 .
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
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:
H0: The mpg is equal between the two groups
HA: 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 to automatically calculate the test statistic for a Wilcoxon Signed-Rank Test.