**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}- The distribution of the
*z*is symmetric (or at least not very skewed)_{i}

If the second or third assumption is violated, then you should consider using the Sign Test, which doesn’t require symmetry.

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 are 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 values for the *T* statistic are 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 so 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.

**Property 2**: If there are a large number of ties, a better estimate of the variance is given by

where *t* varies over the set of tied ranks and *f _{t}* is the number of times (i.e. frequency) the rank

*t*appears.

**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 range A3:C33 of Figure 2. Determine whether there is a significant difference between their attitudes towards politics.

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

The figure 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 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. *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, h*) = 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). If *h* = TRUE (default) harmonic interpolation is used; otherwise linear interpolation is used.

**SRankPROB**(*x, n, tails, iter*) = an approximate p-value for the 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 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 Real Statistics functions to arrive at the same value for *T*, namely SRankPair(B4:C33) = 90 and the one-tailed p-value (assuming the 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, cont*): returns the following values in a 7 × 1 column range: *T, alpha, tails, z, r, T*-crit, p-value. If *ties* = TRUE (default), the ties correction described in Property 2 is applied. If *cont* = TRUE (default) then the continuity correction is applied. If *lab* = TRUE then an extra column with labels is included.

If the size of sample R1 is 26 or less, i.e. COUNT(R1) ≤ 26, then an exact test will be performed. In this case, the output is a 9 × 1 column range (or a 9 × 2 range if* lab* = TRUE), including *T*-crit (exact) and p-value (exact).

For Example 2, the array formula =SRANK_TEST(B4:B33,C4:C33,TRUE,,,FALSE,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** from 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.

For this version of the test, we check **Use continuity correction**, **Use ties correction** and **Include table lookup. **Since there are more than 26 elements in either of the two samples, the exact test won’t be used (whether or not the **Include exact test **option is 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 defined by Property 2 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

where the TiesCorrection function is as described in Mann-Whitney Test.

Finally note that the test ignores any data pairs where one or both of the values is non-numeric. The only exception to this is that the median values (U7 and V7 in Figure 4) are calculated separately, and so may include data that is not included in the test (since one of the elements in the pair is non-numeric).

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

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

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

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

*v*. The assumptions for this test are similar to those of the paired test, namely

- The
*x*are independent_{i} - The
*x*are interval data (so that ranking can be applied and differences taken)_{i} - The distribution of the
*x*is symmetric (or at least not very skewed)_{i}

If the second or third assumption is violated, you should consider using the Sign Test.

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

**SRank**(R1, *med*) = *T* for a single sample contained in range R1 less *med. *If the second argument is omitted it defaults to zero.

**SRTEST**(R1, *med, tails*) = p-value for the Signed-Ranks test using the normal distribution approximation for the sample contained in range R1 less *n.* 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, cont*): returns the following values in a 7 × 1 column range: *T, alpha, tails, z, r, T*-crit, p-value. If *ties* = TRUE (default), the ties correction described in Property 2 is applied. If *cont* = TRUE (default) then the continuity correction is applied. If *lab* = TRUE then an extra column with labels is included.

If the size of sample R1 is 26 or less, i.e. COUNT(R1) ≤ 26, then an exact test will be performed. In this case, the output is a 9 × 1 column range (or a 9 × 2 range if* lab* = TRUE), including *T*-crit (exact) and p-value (exact).

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

**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 observed 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 select the **Non-parametric** and **Single**** sample** options.

For this version of the test, check the **Use continuity correction**, **Use ties correction**, **Include table lookup **and **Include exact test **options and 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 from Property 3 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 Charles

First of all, many thanks for creating this very useful tool.

I’m trying to perform a Wilcoxon Signed-Ranks for Paired Samples test using Real Statistics. The problem I keep having is that after inputting my data ranges (1 & 2) and otherwise completing the dialog box, I get the error “invalid input range 1 selected”. I’m quite certain I’m selecting only the data range I want. I get this error whether I choose the range with the column headings (and check the column heading box in the RealStats dialog) or not (and leave the corresponding box unchecked).

Any suggestions to get around this issue?

Again, thanks.

Eric

Eric,

I have just used the Wilcoxon Signed-Ranks test for Paired Samples and did not see this problem. Perhaps it has something to do with your data. If you send me an Excel file with your data, I will truy to figure out why you are having this problem. You can find my email address at Contact Us.

Charles

Sir Charles,

Thanks for this site, really helped me a lot as I’m really not a statistician! I hope you would consider my question. I have a paired sample data (n=229), and their differences are actually not symmetric (i.e. I even validated this using the D’Agostino-Pearson Test from your site as well). Considering the asymmetry, I should consider using the sign test but I am quite hesitant and am still trying to look for some way of still using the Wilcoxon-signed-rank test.

I read this dissertation of Mr. Jutharath Voraprateep about “Robustness of Wilcoxon Signed-Rank Test Against the Assumption of Symmetry”. If I was able to understand it correctly, he said that doing the Wilcoxon-signed-rank test after the Inverse transformation method from an arbitrary distribution of the dataset (in our case, the paired difference dataset) to comply the symmetry condition of the test can be done and may be considered.

However in my dataset, some paired difference are negative, and in that, I cannot use log/ln transformation. I still want to use Wilcoxon signed rank test instead of Sign test. Of this whole idea, I hope you could help me with my problems:

1. Is the inverse transformation method that sir Voraprateep said same with the simple transformation method (e.g log10(x), ln(-ln(x)), cube root(x) etc.)? If yes, I’ve been looking for a transformation method that can handle negative data and at the same time improve the symmetry of the data but up to now, I still can’t find one (I objectively test for symmetry of the transformed data using the same D’Agostino-Pearson Test for symmetry). I hope you have some suggestion on what specific transformation formula (dealing with negative values and asymmetric data) I could use/consider.

2. If it would be successful to transform the paired difference data and be able to follow symmetry, Can I use the result of the Wilcoxon Rank signed test for the transformed data to explain the actual paired difference data (i.e whether the changes/difference between the paired sample dataset is significant or not based from the result of the test from the transformed paired difference data).

3. If the whole idea is not appropriate, could you please suggest me of other tests similar to Wilcoxon or even sign test? Even if it’s hard, I’m going to study those tests.

I hope I was able to raise my questions/concern clearly and these questions were likewise able to provide further idea for other people who visits and really learn from your lectures and discussions. Thank you very much!

Larry,

While log(x) is not defined for negative values of x, log(a+x) is defined provided the constant a is chosen large enough so that it is bigger than the absolute value of the most negative value of x that you have in your sample. This approach might work for you.

Charles

What if you have all positive ranks, and no negative ranks? Do I use zero for my T since it’s the smaller of the sums (sum of the negative ranks), which makes it smaller than my T-crit (and statistically significant)? Thanks!

Cassie,

I think that is correct. Since all the signs go in one direction, you would expect that the result is significant.

Charles

How can We proof that Wilcox rank test is consistent

Please see the paper Mann & Whitney (1947) referenced in the Bibliography for the proof of the Mann-Whitney test. The proof is probably similar to that for the Signed Ranks test. If not, you could look at Wilcoxon’s original paper.

Charles

Hi Charles:

1. Can we work a Wilcoxon signed rank with unequal samples? For example:

n1=10 and n2=8

n1=20 and n2=5

n1=1 and n2=10

n1=1000 and n2=50

I saw in the example of husband and wife that the couple number 8 and 30 have a cero and a think we can but I have a doubt…

Felix,

The Wilcoxon signed rank test is for paired samples, and so there is essentially one sample consisting of pairs. Thus n1 must equal n2. If both members of the pair have the same value the value used by the test for that pair is zero.

Since n1 is not equal to n2 perhaps you are looking to use some other test — maybe the Mann-Whitney test (or even the t test).

Charles

Hi Charles,

Thank you for explaining the stats so nicely. Your website and RealStat package have been tremendously helpful.

I have one question, though. I ran the Wilcoxon signed rank test using the same dataset, and found SPSS puts out a negative Z value (-1.682), whereas RealStat in Excel puts out a positive Z value (1.631). Not only that, but the p values were slightly different: 0.093 in SPSS, and 0.1029 in RealStat. Would you please explain what can account for these differences in Z and P?

I would be very grateful to hear your opinion on this.

Thank you.

Sue,

The z value is probably negative, but I have used the absolute value of the z-value in Real Statistics since it is easier to relate to this value. In any case, the absolute values of z from SPSS should be equal to the z value from Real Statistics.

Without seeing the data, I can’t explain why the z values from Excel and SPSS should differ (except for the sign). The likely reason is how ties are handled (e.g. if SPSS automatically corrects for ties and you have not chosen the ties correction in Real Statistics). If you send me an Excel file with your data I will try to figure out why there is a difference.

The fact that the p-values are different is simply a consequence of the z values being different.

Charles

I was happy when I’ve found this great site but when I want to start Wilcoxon Signed-Ranks Test for Paired Samples (for 2007) a message pops up “alfa most be a number between 0 and .5”. Ofcourse the default value 0.05 is set. I don’t know if I’m doing something wrong or something with Excel is not right. I would be realy gratefull for answer about that error.

Cristopher,

Even though the default value is .05, you will need to re-enter the value (probably as ,05). You are receiving this message because the software is confused between .05 and ,05 (comma vs. period as the decimal indicator). I have tried to avoid this problem, but it seems to occur with Excel for certain languages.

Charles

I have two paired data on the perception of villagers regarding forest management before and after intervention of PFM. I see Wilcoxon is suitable to analyse the data to see if there is significant difference before and after intervention. Please could you provide me with reference of books that I can support my decision?

Thanks for your understanding.

Amani

Almost any book of statistics that includes nonparametric tests can be used as a reference. Two such references are:

Howell, D. C. (2010). Statistical methods for psychology (7th ed.). Wadsworth, Cengage Learning.

Moore, D., McCabe G. and Craig, B. (2006) Chapter 15 (Non-parametric tests) of Introduction to the practice of statistics, 6th Ed. WH Freeman

http://bcs.whfreeman.com/ips6e/content/cat_040/pdf/ips6e_chapter15.pdf

Charles

Dear Charles, I am testing for equality of means between large paired samples ranging in size from n=600-1400, with large and unequal variances. Is the Wilcoxon Signed-Ranks Test for Paired Samples suitable? [When I use the test the null hypothesis is consistently rejected, even when the sample means are very close]

Christo,

If your data is normally distributed (or at least not too skewed), you could even use the paired t test. Otherwise the Wilcoxon Signed-Ranks test could be used. Recall that in the paired test, the differences between each of the pairs is tests, and so the fact that the individual samples have different variances is not relevant.

With large samples, it is not too surprising that the null hypothesis is rejected, since even a small difference in the means will be statistically significant. In these cases (and even for small samples), you should calculate the effect size. If this statistic is small, you could conclude that there is a significant difference between the means, but that this difference is small.

Charles

Hi Charles

Thanks much for this well explained piece of information. I found it very useful as am preparing for my Non-Parametrics and Categorical Statistics.. but how do we compute the p value for the Wilcoxon?

Monica,

When the normal approximation is used then the p-value is simply NORMDIST(T,mean,std,TRUE) where T is the calculated T value and mean and std are the approximate mean and standard deviation values (as described on the referenced webpage).

Charles

Dear Charles,

I’m using the Data Analysis Tool for Wilcoxon signed-ranks test. However, when I select ties correction the standard deviation is negative and then impossible to calculate the others values.

I’m comparing the judgments of a same group of listeners, using a 5-point scale, concerning two different musical stimuli.

Thanks,

Juan

Juan,

Yes, I am seeing the same problem. There is a misplaced parenthesis in the program. I will fix this bug and put it in the next release, which should be available in the next couple of days.

Thanks very much for catching this error.

Charles

Hi Charles,

Just a question. Will there be a new release for Mac users soon ?

Thanks,

Juan

Hi Charles,

I performed Wilcoxon Signed Ranks test on two data sets using SPSS. In test statistics, it mentions the basis of the Z-value as either negative ranking or positive ranking. Can I use this logic to obtain an inequality between the two data set? Just like in a paired T-test, if the value of T is negative than we can say that the latter one in the data set is greater than the former one.

Thanks

Sorry, but I don’t understand your question. The Wilcoxon Signed-Ranks test is used as a substitute for the paired t test.

Charles

Hi Charles,

In SPSS, the test statistics is given in the form of Z and P value. With every Z-value it is indicated if it was based on positive ranking or negative ranking. While in the rank table it shows that Positive ranking indicates Sample1<Sample2 and vice versa. Can I use this to indication to infer the an inequality relation between the two samples?

Or simply, can this test be used to establish an inequality relation between the two samples?

Umer,

Sorry, but I don’t completely understand your question. In any case, the Wilcoxon Signed-Ranks test is used to establish an inequality relation between the two samples (assuming they are paired samples).

Charles

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

Hi Charles,

sorry…..but where can I find references on the estimates that I have listed?

My problem is:

I have a distribution(X) on which I perform the Wilcoxon Signed-Rank Test for a Single Sample.

H0: X=0 ; H1: X != 0

The value of “effect r” is : 0,874474.

how can I interpret this value based on my hypothesis?

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). Your value of r represents a very large effect. explaining over 76% of the variance.

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.

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

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

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

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

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

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

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

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.