**Wilcoxon Signed-Ranks Test for Paired Samples**

When the requirements for the t-test for two paired samples are not satisfied, the Wilcoxon Signed-Rank Test for Paired Samples non-parametric test can often be used.

In particular, we assume *n* subjects from a given population with two observations *x _{i} *and y

*for each subject*

_{i}*i.*This results in two paired samples {

*x*

_{1},…,

*x*} and {y

_{n}_{1},…,y

*} as described in Paired Sample t Test. The requirements for the Wilcoxon Signed-Rank Tests for Paired Samples where*

_{n}*z*= y

_{i}*–*

_{i}*x*for all

_{i}*i*= 1, … ,

*n*, are as follows:

- the
*z*are independent_{i} *x*and y_{i}are interval data (and so a ranking can be applied and differences can be taken)_{i}

For this test we use the following null hypothesis:

H_{0}: the distribution of difference scores in the population is symmetric about zero

I.e. any differences are due to chance. We show how to apply this test via a couple of examples.

**Example 1**: A researcher wanted to determine whether people’s ability to identify objects with their right eye differs from their ability with their left eye. 16 subjects were presented with a series of images and were scored on their abilities to identify objects which each eye. The results tabulated in Figure 1. Based on this data determine use the Wilcoxon Signed-Ranks Test to whether there is a difference between the two eyes.

**Figure 1 – Wilcoxon Signed-Ranks Test for Paired Samples**

We perform a two-tailed Wilcoxon Signed-Ranks Test for Paired Samples with *α* = .05 to test the following null hypothesis:

H_{0}: any differences between the two eyes is due to chance (essentially based on the median of the differences)

The scores for the two eyes are presented in columns B and C. Column D contains the differences between the scores for each subject. Column E contains the absolute value of these differences, eliminating any zero differences from further consideration. Column F contains the adjusted rankings of the non-zero values in column E. Column G reports the values in column F where the difference in column D is positive. Column H reports the values in column F where the difference in column D is negative.

Columns G and H are summed (in cells G19 and H19) to obtain *T+* of 69.5 and *T-* of 35.5. The smaller of these values is the test statistic *T* = 35.5 (in cell K7).

The critical value for the *T* statistic is given in the Wilcoxon Signed-Ranks Table. Here we use *α* = .05 and *n* = 14 (i.e. the 15 subjects less the 1 subject where the difference value in column D is zero). From the table we find that *T _{crit} *= 21 (two-tail test). Since

*T*= 21 < 35.5 =

_{crit}*T*, we can’t reject the null hypothesis (i.e.

*p*≥ .05), and conclude there is no significant difference between the two eyes.

**Observation**: Generally for *n* > 25, an estimate using the normal distribution can be made (as seen in the next example). The actual threshold of 25 is not universally accepted and can be lowered to around 15 or raised to about 50.

**Property 1**: For *n* is sufficiently large, the *T* statistic (or even *T+* or *T-*) has an approximately normal distribution *N*(*μ, σ*) where

Click here for a proof of Property 1.

**Example 2**: A study is made to determine whether there is a difference between husbands and wives attitudes towards politics. A questionnaire measuring this was given to 30 couples with the results summarized in Figure 2.

**Figure 2 – Wilcoxon Signed-Ranks Test for Paired Samples**

The table above is similar to that in Figure 1. Since *T* = 90 < 98 = *T _{crit}* (two-tail test), we conclude there is a significant difference between husbands and their wives regarding politics, Alternatively, we can conduct the analysis using the normal distribution approximation, as we did in Example 2 of Mann-Whitney Test. This time, we calculate a mean of 203 (cell N8), variance of 1928.5 (cell N9) and standard deviation of 43.9 (cell N10). From these we calculate a z-score of 2.57, which yields a p-value of .005 (one-tail test), which is less than α = .05, and so once again we reject the null hypothesis.

**Real Statistics Excel Functions**: The following functions are provided in the Real Statistics Pack:

**SRankPair**(R1) = *T* for a pair of samples contained in range R1, where R1 consists of two columns, one for each paired sample. R1 must contain only numeric values.

**SRTESTPair**(R1,,*tails*) = p-value for Signed-Ranks Test using the normal distribution approximation for the pair of samples contained in range R1, where R1 consists of two columns, one for each paired sample. R1 must contain only numeric values. *tails* = # of tails: 1 (default) or 2.

There are also versions of SRankPair and SRTEST which take two ranges, namely:

**SRankPair**(R1, R2) = *T* for a pair of samples contained in ranges R1 and R2, where both R1 and R2 have only one column, one for each paired sample. R1 and R2 must contain only numeric values and they must have the same number of elements.

**SRTESTPair**(R1, R2, *tails*) = p-value for Signed-Ranks test using the normal distribution approximation for the pair of samples contained in range R1 and R2, where both R1 and R2 have only one column, one for each paired sample. R1 and R2 must contain only numeric values and they must have the same number of elements. *tails* = # of tails: 1 (default) or 2.

These functions ignore any empty or non-numeric cells.

There are also the following two functions which relate to the Wilcoxon Signed Ranks Table.

**SRankCRIT**(*n, α, tails*) = critical value of the Signed-Ranks Test for samples of size *n* for the given value of alpha and *tails* = 1 (one tail) or 2 (two tails, default).

**SRankPROB**(*x, n, tails, iter*) = an approximate p-value for signed ranks test *x* (= *T*) for a sample of size *n* and *tails* = 1 (one tail) or 2 (two tails, default) based on a linear interpolation of the values in the Wilcoxon Signed Ranks Table, using *iter* number of iterations (default = 40).

Note that the values for α in the table in Wilcoxon Signed Ranks Table range from .01 to .1 for tails = 2 and .005 to .05 for tails = 1. If the p-value is less than .01 (tails = 2) or .005 (tails = 1) then the p-value is given as 0 and if the p-value is greater than .1 (tails = 2) or .05 (tails = 1) then the p-value is given as 1.

**Observation**: In Example 1, we can use the supplemental function SRankPair(B4:C18) = 35.5, which is the same value we calculated in cell K7 of Figure 1. Also SRankCRIT(K6,K4,K5) = SRankCRIT(14,.05, 2) = 21 (the value in cell K8 of Figure 1). Finally note that the p-value = WPROB(K7,K6,K5) = WPROB(35.5, 14, 2) = 1 > .05 = α, and so once again we can’t reject the null hypothesis. That WPROB(35.5, 14, 2) = 1 simply means that the p-value > .1. If *T* had been equal to 22 instead of 35.5, we would obtain a p-value = WPROB(22, 14, 2) = .0625.

Similarly in Example 2 we can use the supplemental function to arrive at the same value for *T*, namely SRankPair(B4:C33) = 90, as well as the same p-value (assuming a normal approximation), namely SRTESTPair(B4:C33) = 0.005039.

We can also use the two argument versions of these functions to get the same results, namely SRankPair(B4:B33, C4:C33) = 90 and SRTESTPair(B4:B33, C4:C33) = 0.005039.

**Observation**: As for the Wilcoxon test for independent samples, we can use the correlation coefficient *r* as a measure of effect size where

Here 2*n* = the number of observations, including the cases where the difference is 0. For Example 2

which represents a medium-sized effect.

**Real Statistics Function**: The Real Statistics Pack also provides the following array function for the samples in ranges R1 and R2 where alpha is the *α* value (default .05) and *tails* is the number of tails (1 or 2 = default).

**SRANK_TEST**(R1, R2, *lab, tails, alpha, ties*): returns the following values in a 7 × 1 column range: *T, alpha, tails, z, r, T*-crit, p-value. If *lab* = TRUE then an extra column with labels is included. If *ties* = TRUE (default), the ties correction described above is applied.

For Example 2, =SRANK_TEST(B4:B33,C4:C33,TRUE,,FALSE) returns the array shown in Figure 3:

**Figure 3 – Output from SRANK_TEST for paired samples**

**Real Statistics Data Analysis Tool**: The Real Statistics Resource Pack also provides a data analysis tool which performs the Wilcoxon signed-ranks test for paired samples, automatically calculating the medians, *T* test statistic, z-score, p-value and effect size *r*.

For example, to use this data analysis tool for Example 2, enter **Ctrl-m** and choose **T Tests and Non-parametric Equivalents** for the menu. A dialog box will appear as in Figure 3 in Mann-Whitney Test for Independent Sample. Enter B3:C33 in the **Input Range**, check **Column headings included with data**,** **choose the **Paired samples** and **Non-parametric** options and make sure that **Include exact test **and **Use ties correction** are checked.

When you click on the **OK **button the output shown in Figure 4 is displayed.

**Figure 4 – Wilcoxon signed-ranks data analysis for paired samples**

Note that rows 16 through 23 of the figure show the results of the Wilcoxon signed-ranks test using the normal approximation, while the bottom two rows show the results of the test using the critical value from the Wilcoxon Signed-Ranks Table. These last two rows are only displayed when the values of *n* and *α* are in the table and the **Include exact test** option is checked.

Also note that since **Use ties correction** is checked, the ties correction described above is applied in the calculation of the standard deviation (cell U18 of Figure 4) as follows.

=SQRT(U17*(2*U10+1)/6)-TiesCorrection(B4:B33,C4:C33,1)/48

**Wilcoxon Signed-Ranks Test for a Single Sample**

We can also use the Wilcoxon Signed-Ranks Test to test the following null hypothesis:

H_{0}: the median of the population is some given value ν

The approach we use is to apply the Wilcoxon Signed-Ranks test for paired samples, as described above, on a single sample {*x*_{1}, …, *x _{n}*} where we assume the second sample consists of

*n*values all of which are ν. We demonstrate approach this in Example 3 using the following supplemental functions.

**Real Statistics Excel Functions**: The following functions are provided in the Real Statistics Pack:

**SRank**(R1, *n*) = *T* for a single sample contained in range R1 less *n*, where R1 can be of any size and shape but must contain only numeric values. If the second argument is omitted it defaults to zero.

**SRTEST**(R1, *n, tails*) = p-value for the Signed-Ranks test using the normal distribution approximation for the sample contained in range R1 less *n*, where R1 can be of any size and shape but must contain only numeric values. If the second argument is omitted it defaults to zero. *tails* = # of tails: 1 (default) or 2.

These functions ignore any empty or non-numeric cells. The **SRankCRIT** and **SRankPROB **functions, as described above, can also be used for single sample signed-ranks tests.

**Example 3**: Determine whether the memory loss program described in Example 1 of the Sign Test is effective using the Wilcoxon Signed-Ranks Test.

We repeat the data from this example in Figure 5.

**Figure 5 – Wilcoxon Signed-Ranks Test for a Single Sample**

This time we compare the data from the single sample with the hypothetical median of 20%. We calculate *T* to be 40.5 using the formula SRank(D4:D18). We find the critical value for *T* when *n* = 14 to be 21 (two-tail test) from the Wilcoxon Signed-Ranks Table. Since 40.5 > 21, we cannot reject the null hypothesis and conclude again that there is no significant difference between the median of the data and 20%.

Using the normal distribution approximation, we see that SRTEST(B4:B18,20) = SRTEST(D4:D18) = .225629 > .05 = *α*, and so again conclude there is no significant difference between the median of the data and 20% (one-tail test).

Note that one way to calculate that *n* = 14 is to use the formula:

=COUNTIF(B4:B18,”<>”&20).

**Real Statistics Function**: The Real Statistics Pack also provides the following array function for the sample in range R1 where *hyp *is the hypothesized median (default = 0), alpha is the *α* value (default .05) and *tails* is the number of tails: 1 or 2 (default).

**SRANK_TEST**(R1, *hyp, lab, tails, alpha, ties*): returns the following values in a 7 × 1 column range: *T, alpha, tails, z, r, T*-crit, p-value. If *lab* = TRUE then an extra column with labels is included. If *ties* = TRUE (default) then the ties correction described above is applied.

For Example 3 (two-tailed) =SRANK_TEST(B4:B33,C4:C33,TRUE,.05,FALSE) returns the array displayed in Figure 6:

**Figure 6 – Output from SRANK_TEST for single sample**

**Real Statistics Data Analysis Tool**: The Real Statistics Resource Pack also provides a data analysis tool which performs the Wilcoxon signed-ranks test for one sample, automatically calculating the median, *T* test statistic, z-score, p-value and effect size *r*.

For example, to use this data analysis tool for Example 3, press **Ctrl-m** and choose **T Tests and Non-parametric Equivalents** from the menu that is displayed. A dialog box will appear as in Figure 3 in Mann-Whitney Test for Independent Sample. Enter B3:B18 in the **Input Range**, check **Column headings included with data** and enter 20 for the **Hypothetical Mean/Mean**. Next choose the **One sample** and **Non-parametric** options and make sure that **Include exact test **and **Use ties correction** are checked.

When you click on the **OK **button the output shown in Figure 7 is displayed.

**Figure 7 – Wilcoxon Signed-Ranks data analysis for a Single Sample**

Also note that since **Use ties correction** is checked, the ties correction described above is applied in the calculation of the standard deviation (cell AB16 of Figure 7).

**Exact Test**

Click here for a description of the exact version of the Signed Ranks Test using the permutation function.

Hi,

I have downloaded your functions which are very helpul and thanks a lot for this.

The problem lies in SRankPair()which does not calculate.

Pls let me know whether you plan to fix the bug.

Much appreciating your assistance.

Hi Dinos,

I am pleased that you found the functions useful. I am sorry that the SRankPair function didn’t work. I will test the function tonight and see what the problem is. Sorry for any delay, but I am moving house and so things are a bit delayed.

Charles

Dinos,

I just tested the SRankPair() function and I found that it works just fine on my computer. Please make sure that you give the function one argument, consisting of a range with two columns. For example to calculate the value of the function for two samples in ranges E1:E10 and F1:F10 use the function SRankPair(E1:F10) and not SRankPair(E1:E10,F1:F10). The two samples must be contained in two contiguous columns of the same size starting in the same rows. I will introduce a two argument version of the function in a future release.

If this doesn’t address your problem, please send me the data that you used and range where the data was entered and I will try to duplicate the problem.

Charles

Update(20 July 2013): The new release of the Real Statistics Resource Pack, R1.4, now removes the restriction that the two ranges be contiguous. You can use the function SRankPai(R1, R2) where R1 and R2 are not contiguous.Hi Charles,

Thank you very much for putting all this information up for free – its all really useful. I have data very similar to that in Example 1. Would you be able to walk me through how to get the results as shown in columns F and K? I am assuming I use the SRankPair function for column F (inputting the data from columns A and B), but how do I get column K?

Thanks in advance,

Robin

Hi Robin,

Column F is calculated using the RANK.AVG function in Excel 2010/2013 (or the RANK_SUM supplemental function with earlier versions of Excel).

Cell K6 uses the formula =COUNT(E4:E18), cell K7 uses the formula =MIN(G19:H19), cell K8 uses the supplemental formula =SRankCRIT(K6,K4,K5) (or you can look up the value in the Wilcoxon Signed Rank Table).

You can check all of this out by downloading the Real Statistics Examples Workbook, which contains the worksheets for all the examples in the website.

Charles

Charles,

Can you double check your formula for calculating the effect size for the Wilcoxon Signs Rank Test?

This source uses N, whereas yours uses 2N:

http://www.let.rug.nl/~heeringa/statistics/stat03_2013/lect09.pdf

Thanks,

Gary

Gary,

In the reference N is the total number of observations on which z is based. I believe N = 2n where n = total number of paired samples since each pair has two observations.

Charles

which parametric test matches with wilcoxon test

The Wilcoxon signed ranks test for a single sample matches with the one-sample t test.

The Wilcoxon signed ranks test for paired samples with the paired samples t test.

Charles

Is there a critical value table or calculator for large sample sizes? The two sample sizes I have is n1=75 and n2=171

Deon,

For large samples you should use the normal approximation described in Examples 2 and 3 of the referenced webpage. These examples use Property 1 described on that webpage.

Charles

Thank you for putting together this functions for excel.

I am trying to use the SRANK_TEST but it doesn’t returns the whole table that is supposed to return. What do you think I am missing? Once I place the function it returns T-Test on one single cell.

Fernando,

Based on your comment, I don’t understand the problem that you are having. If you send me the data set that you are testing, I will try to figure out what the problem is.

Charles

Dear Charles,

I would like to do a Wilcoxon test to compare to sets of nonparametric paired data. I have downloaded the realstats package and activated it in the add-ins. When I open the data analysis window, the SRank options do not show. Am I missing something?

Many thanks for your help,

Sue

Sue,

SRank is a function and so you should enter it into a cell as you would the SUM, AVERAGE or other Excel functions

You can access the Wilcoxon Signed-Ranks Test, as described towards the end of the referenced webpage, as follows:

Press Ctrl-m and choose the

T Tests and Non-parametric Equivalentsdata analysis tool from the menu that is displayed. On the dialog box that appears choose theOne sampleandNon-parametricoptions, and fill in the appropriate fields.Charles

Hi Charles,

Thanks for your help.

Sue

I am wondering why I am getting different p values when I do this test in excel versus doing it in SAS. The p values are pretty different when using less than 20 observations, and SAS says “If n<20 , the significance of is computed from the exact distribution of S, where the distribution is a convolution of scaled binomial distributions."

The T criticial values are the same but p values of 2 tail are different.

When I use this excel add in, running the exact test and unchecking the exact test produce the same results. is that normal?

SAS: http://support.sas.com/documentation/cdl/en/procstat/63104/HTML/default/viewer.htm#procstat_univariate_sect029.htm

Ada,

When using the Real Statistics

Wilcoxon Signed-Ranksdata analysis tool both the one-tail and two-tail tests are shown using thenormal approximation. This test outputs the T-crit, p-value and whether the test is significant or not. The result is the same whether theInclude exact testoption is checked or not.If the

Include exact testoption is checked thenin additionan exact test is displayed. This test shows the critical value based on the Wilcoxon Signed-Ranks Table and whether the T value is significant or not (no p-value is displayed).In addition, you can conduct another version of the exact test using the PERMDIST(T, n, TRUE) formula (but not included in the data analysis tool). This version outputs a p-value, and may be equivalent to the output from SAS with n < 20 (I don’t have a copy of SAS and so I can’t check).

I plan to update the website to make this clearer in the next day or two.

Charles

In the PERMDIST(T, n, TRUE) formula, is T the value outputed above the sig(exact) in the table produced? So for figure 4, would it be PERMDIST(116, 30, TRUE)

Adam,

The value for T is 90, as shown in cell U13. Thus the calculation is PERMDIST(90, 30, TRUE).

Caution: since n = 30, this formula will take a while to calculate.

Charles

Great, thanks for your help.

Hi Charles,

in Wilcoxon Single Sample Test which is the scale of effect r that i have to see? Cohen’s scale?

Best regards,

gp

No you shouldn’t use the Cohen’s scale.

A rough estimate of effect size is that r=.5 represents a large effect size (explains 25% of the variance), r=.3 represents a medium effect size (explains 9% of the variance), and r=.1 represents a small effect size (explains 1% of the variance).

Charles