Wilcoxon Signed Rank Test in Excel

[@davidritz3538]

Nice video. For completeness I would mention how to handle two important cases that are frequently encountered with data, especially ordinal, for which this test is perfect. First, the pairs with zero difference should not be included in the subsequent "positive" vector. Second, the ranking for equal rank values, if more than one diff is of the same value, should be averaged out.

[@MatthiasKullowatz]

Yes, for data that is more discrete, these outcomes you described are very possible. When I made this video, there was no Excel function (2007) for RANK.AVG, so at the time I used a workaround with COUNTIFs. But that never made it into the video. Unfortunately, I don't have time to remake the video, but I hope it's still helpful!

[@vincentiusharyanto1416]

How do you get that the critical value is 14, is it taken from the table, with two tailed 5%, n= 12 and TL= 14?

[@bradpainting1555]

Just a quick tip: you can also use sign() to return a 1 or -1 based on the sign of the cell's value.

[@MatthiasKullowatz]

Brad Painting Great point! That's slick.

[@msdafe4514]

You are so great! I answered my outputs in advanced statistic 2 in just watching this video. Thanks a lot!

[@darian2596]

Thank you so much.....you made this incredibly easy!!!! It has been a looong time since ive used any of this stuff! Thanks again!

[@platymantis27]

Hi, may I inquire if the negative sum is zero (0), what is the test statistics? Thanks

[@xzyeee]

You are an exceptional global teacher! Thank you so much!

[@-SopanPatil]

Please give a practice example for one sample data

[@maindepth8830]

how do i get the critical value table. I have 200 data points.

[@Kelvin_Tso]

I have a question. How can I know the critical value is 14 in this case?

[@MatthiasKullowatz]

You have to use a critical value table, specifically made for the signed-rank test. This is a good question, though, because of some confusion between different tables. That number 14 comes from a special Signed-rank test table. But note that in the table in the first link at the bottom of this response, the 5%, two-tailed critical value (associated with a sample size of 12) is 14. However, the second link is for a table that claims the critical value is 13. Which is right? These critical values are derived from a branch of mathematics called combinatorics, and there should only be one possible critical value. I looked into this further, and to be as clear as possible, your Test Statistic should be strictly less than 14 for this problem. My guess is that some tables suggest you have to be strictly less than 14, and others say you can be less than or equal to 13. Technically those mean the same thing in this context, but it can be confusing when tables don't follow the same rules! 1) d2vlcm61l7u1fs.cloudfront.net/media%2F9c6%2F9c6bd757-2ba1-42df-b352-77984fe4af33%2FphpXMJ7TR.png 2) www.real-statistics.com/statistics-tables/wilcoxon-signed-ranks-table/

[@martin48428]

what if the two or more diff are equal... the rank function gives them the same rank but then skips the next rank.... what would that make of the positive/negative sums? please help

[@samkab677]

Hi can we state here if z statistic is less z critical we reject ho ?

[@mariembensaid1593]

Hi, thank you for the explanation. I would to know how can I organize the data input to perform a test Wilcoxon with Excel? I have data about juveniles and adults individuals of a given tree species and I would compare it along with altitude. Can you help me, please?

[@MatthiasKullowatz]

Can your observations be "matched" in any way. For example, are you taking two measurements per tree? Maybe two measurements per altitude? If your measurements are all from different trees, and you want to measure the effects of altitude and age on some other measurement, then I would suggest a different approach. You're probably looking for a regression model in that case, but it's hard to be sure without me knowing more about your data and objectives.

[@mariembensaid1593]

@@MatthiasKullowatz I calculated the abundance of two size classes (small and adult tree for one speices) and I would assess if thery have similar or dissimilar distribution along elevational gradient. I tried to follow your demonstration in this video

[@MatthiasKullowatz]

@@mariembensaid1593 Okay, so to clarify, it sounds like your data could be segmented into different elevations, with abundance counts in each elevation for both small and adult trees. So like, one example data point might be "at 1000 feet, we see 100 small trees and 1000 adult trees." Now, what is your hypothesis? Do you think that different elevations are better suited for young vs. old trees?

[@mariembensaid1593]

@@MatthiasKullowatz yes, I think that the small trees are regeneratin in higher altitude compared to the adult ones

[@MatthiasKullowatz]

@@mariembensaid1593 In this case, I don't think you want a signed rank test. In my mind this is a regression problem. Here's one way you could test the relationship. Build a linear regression model with altitude as the independent variable and the ratio of small trees to large trees as the dependent variable. In other words, the function will look like this: (smallcount/largecount) = B*(altitude) + intercept If your B coefficient ("slope") is statistically significantly non-zero, and in the direction you would expect, then that's good support for your hypothesis. Because I am not a domain expert here, I guess that the ratio of small/large tree counts makes the most sense. This would help control for effects like "fewer trees overall grow a higher altitudes".

[@namratabhalerao9837]

Sir can you help me please, My Ho is Celebrity Endorsement has signifucany impact pn consumer decision making process with reference to skin care products H1 is Celebrity Endorsement does not have significant impact on consumer decision making process with reference to Skin-care products. Can you please help me in testing hypothesis using Wilcoxon sign rank test?

[@GaryHutsonVBA]

Hi, I just wondered where to find the signed-rank table on the internet. Please help? Great videos! :)

[@pmike77]

l struggle to understand why you have to reject H0 when t

[@MatthiasKullowatz]

Good question. Assuming the null hypothesis is true for a moment (that there is no difference in the two distributions), then we would expect some cars to get better mileage with additive 1 and others with additive 2. When you add up the ranks of observations where one additive was better than another, you'd expect to get a sum or ranks in the "middle." The middle is like the average of adding up no ranks (0) or adding up all the ranks (1 + 2 + ... + 12 = 78), which is 39. The further away from 39 the sum of ranks is, the further from the null hypothesis the data is. It can be close to 0 or close to 78, but either way it's not agreeing with the null hypothesis. Using other terminology, it is a two-sided critical region, so rejection can happen on both extremes.

[@DavidsGameplayMC]

What is the critical value if n=100? Is there a formula to find this?

[@MatthiasKullowatz]

The short answer is yes, there is a formula. All these non-parametric statistical tests are governed by combinatorics. With a little math, you can derive all the critical values you'd ever want. The cop-out answer is that with n >= 100, practically you probably wouldn't want to use a non-parametric test because it's not needed. A similar t-test is very robust to strange underlying distributions at sample sizes that high. If I find time, I'll swing back with some math on how you'd derive that critical value.

[@krihstylle01]

Where did you get 14?

[@MatthiasKullowatz]

+ashley nicole This is a good question. That number comes from a special Signed-rank test table. However, I've found one source of confusion. In the table in the first link at the bottom of this response, the 5%, two-tailed critical value (associated with a sample size of 12) is 14. However, the second link is for a table that claims the critical value is 13. Which is right? These critical values are derived from a branch of mathematics called combinatorics, and there should only be one possible critical value. I looked into this further, and to be as clear as possible, your Test Statistic should be strictly less than 14 for this problem. My guess is that some tables suggest you have to be strictly less than 14, and others say you can be less than or equal to 13. Technically those mean the same thing in this context, but it can be confusing when tables don't follow the same rules! 1) d2vlcm61l7u1fs.cloudfront.net/media%2F9c6%2F9c6bd757-2ba1-42df-b352-77984fe4af33%2FphpXMJ7TR.png 2) www.real-statistics.com/statistics-tables/wilcoxon-signed-ranks-table/

[@ahsanahmed6412]

Thank you so much

[@mmmj7911]

Hi I don't understand how you found the test statistic of 7, could you explain more? Also is there a way to determine the critical value without looking at the table. Is there an excel function that can be utilized? Thanks

[@MatthiasKullowatz]

In terms of how we get 7 for our test stat, I'm not sure I can explain it any better than in the video. The test stat is the sum of the ranks of only those pairs where the original difference was negative (in this example). As for finding a test stat, there are many tables online. Excel does not have an explicit function for the critical values that I know of, but the critical values come from discrete counting theory, and could be recreated using combinations. Here are some links that may be helpful: www.real-statistics.com/statistics-tables/wilcoxon-signed-ranks-table/ www.real-statistics.com/non-parametric-tests/wilcoxon-signed-ranks-test/

[@petar-boshnakov]

Hi Matthias, thanks a lot for the video. I got a little bit confused on the critical value part. Aren't we supposed to reject the H0 when the test statistic is greater than the critical value? (i.e. in the case of the vid we support the H0) support.minitab.com/en-us/minitab-express/1/help-and-how-to/basic-statistics/inference/supporting-topics/basics/what-is-a-critical-value/

[@roylacaran2020]

@@MatthiasKullowatz the smallest sum will be the test statistics

[@MatthiasKullowatz]

@@roylacaran2020 Is this a question? I think most critical value tables have rejection regions on the low end, so yes the smallest sum will be your test statistic.

[@MatthiasKullowatz]

@@petar-boshnakov Sorry I never replied. I haven't taught this in a number of years, but I believe most critical value tables are set up to reject lower values. When you calculate the sum of ranks of negative differences and the sum of ranks of positive differences, I believe you can just use the smaller of the two numbers and reject for values lower than the critical value. It definitely depends on the critical value table you are using, though.

[@sinanalmottar3207]

hi and thanks it was perfect I have a question if we have independent two samples with negative signs in some readings is the test applicable here or any other one with regards

[@MatthiasKullowatz]

Yes, the test is fine if some of the observed values are negative.

[@maindepth8830]

What is the point of using R when excel makes life so much easier

[@mervatsalama8065]

for paired sample which is 3 items i can use willcoxon signed ranks or not

[@MatthiasKullowatz]

Do you mean that you have 3 pairs of data points? Technically you can do the Signed-rank test, but you won't get an interesting result. Assume each pair has observation A and observation B. Even if all three values are larger for observation A, the p-value would be 0.125 or 0.25, depending on whether you did a one- or two-tailed test. Is that what you meant when you said "3 items"?

[@ingridcastrolund7655]

hello :) i wanted to ask how do you know the percentage significance to find the critical value in the table? thanks

[@MatthiasKullowatz]

This is typically given to you in a contrived problem in class. Generally, 5% is common. If you're testing for "a difference" you have to split 5% in half and go to the 2.5% column.

[@ingridcastrolund7655]

@@MatthiasKullowatz Thank you!

[@purple492]

Great tutorial! But if my N=6 then my critical value=0. So I can never reach significance...is that correct?

[@MatthiasKullowatz]

Correct. I think in the video I said something like "because 7 is less than 14, we reject the null hypothesis," but even had the test stat been = 14, I would have rejected the null hypothesis based on the table I was using. So with N = 6, it is possible that all observations will have have positive differences, or all observations will have negative differences, in which case the test stat would be 0, and you would reject the null hypothesis. To understand why there aren't very many options for rejection with N = 6, think about how probable it is for all to be positive or all to be negative by random chance alone. It would be like flipping a coin 6 straight times, and get either all heads or all tails. The probability of all heads is (0.5)^6 ~ 1.6%, which is the same as the probability of all tails, so there is a 3.2% chance you'd randomly get a test stat of 0. That's less than 5%, so if you see a test stat of 0, you reject at the 5% level of significance.

[@purple492]

Matthias Kullowatz Thank you for such a thorough explanation! You’ve really helped me with my analysis!

[@joshchen9972]

Does this work if there are records where there is no change (i.e. Sign = 0)?

[@MatthiasKullowatz]

If you have many instances where the pair of observations are equal, i.e. their difference is 0, then there is likely no difference between the groups. If you have just a few observations that are equal, then it probably won't make a difference (because they will have the smallest ranks). If you still want to go through with it... All of your zero-change observations will get the same average rank value. Just take half of these observations, and add their average rank values to the positives, and take the other half and add them with the negatives. If there's an odd number of zero-change observations, do like you did above, but put the odd one out with the team that has the least points. That is, whichever sum is closer to zero, add the final zero-change rank value to that group. This is the conservative way to treat the odd one out, because it will slightly increase your p-value.

[@joshchen9972]

@@MatthiasKullowatz Thanks for taking the time to reply. This makes sense :)

[@cynthiajacobson9702]

What is done when the difference is zero?

[@MatthiasKullowatz]

This is a good question. In theory, the difference will never be 0 if you're measuring "continuous" things. However, in the real world many measurements are not particularly granular (like whole numbered ages, for example). The good news is, it probably won't matter. I'm going to cite a prior response here: " If you have many instances where the pair of observations are equal, i.e. their difference is 0, then there is likely no difference between the groups. If you have just a few observations that are equal, then it probably won't make a difference (because they will have the smallest ranks). If you still want to go through with it... All of your zero-change observations will get the same average rank value. Just take half of these observations, and add their average rank values to the positives, and take the other half and add them with the negatives. If there's an odd number of zero-change observations, do like you did above, but put the odd one out with the team that has the least points. That is, whichever sum is closer to zero, add the final zero-change rank value to that group. This is the conservative way to treat the odd one out, because it will slightly increase your p-value. "

[@igorribeiro1623]

Thank you for that very usefull lecture, but still some questions to ask. How can i show the percentage difference between two samples? For instance: Sample 1 = 10; sample 2 = 5; diference = - 50% . Thanks in advance!

[@MatthiasKullowatz]

You could perform the entire test in the exact same way except change how you calculate the "Difference" column. Instead of taking a simple difference (subtraction), use the percent difference in this column. In cell D2, type the following: =(B2-C2)/B2 Then copy that formula down to get the percent differences for all pairs. Then run the rest of the test in the exact same way as shown in the video. In this particular example, you'll get a similar result as before. Most of the raw differences will be ranked similarly to the percent differences. That will not be the case with every sample you'll ever see, though, so if you're truly more interested in percent differences, then you should use percent differences in the test.

[@petar-boshnakov]

Such a useful video! Thanks a lot for it!

[@ahmed_shawa]

Many thanks! This was very helpful really

[@gawthami5956]

thank you so much for such a great explanation

[@FuKeiii]

How about if the difference is 0?

[@MatthiasKullowatz]

This is an interesting question. For truly continuous measurements, that shouldn't happen. But in the real world, that kind of precision is not always possible. Or you might be measuring things with values like 1, 2, 3, 4, etc. where zero differences are very common. So here's a workaround that I thought up... For a one-tailed hypothesis, like Additive 1 > Additive 2, then I would suggest only marking the difference positive if it's truly positive (that is, not equal to 0). Set all zeros to negative (or whatever direction is NOT in support of the researcher's hypothesis). For a two-tailed hypothesis, like Additive 1 is not equal to Additive 2, then it becomes tricky because evidence against the null hypothesis is accumulated on both sides. Here's what I might do: Give half the rank points to the positive sum and half to the negative sum. Because differences of zero are clearly in support of the null hypothesis, you don't want to remove them because it would bias the results toward rejection of the null hypothesis. By dividing the rank points up, you're unbiasedly giving the least amount of rank points to either extreme.

[@mariembensaid1593]

In the end of the video I do not understand why you wrote "14" as critical value !!??

[@MatthiasKullowatz]

Here I have just copied an answer I've given to a similar question: This is a good question, though, because of some confusion between different tables. That number 14 comes from a special Signed-rank test table. But note that in the table in the first link at the bottom of this response, the 5%, two-tailed critical value (associated with a sample size of 12) is 14. However, the second link is for a table that claims the critical value is 13. Which is right? These critical values are derived from a branch of mathematics called combinatorics, and there should only be one possible critical value. I looked into this further, and to be as clear as possible, your Test Statistic should be strictly less than 14 for this problem. My guess is that some tables suggest you have to be strictly less than 14, and others say you can be less than or equal to 13. Technically those mean the same thing in this context, but it can be confusing when tables don't follow the same rules! 1) d2vlcm61l7u1fs.cloudfront.net/media%2F9c6%2F9c6bd757-2ba1-42df-b352-77984fe4af33%2FphpXMJ7TR.png 2) www.real-statistics.com/statistics-tables/wilcoxon-signed-ranks-table/

[@mariembensaid1593]

@@MatthiasKullowatz thank you very much Sir for your explanation..

[@dianakurniawati3721]

how get critical is 14

[@MatthiasKullowatz]

These values come from a field of mathematics called "combinatorics." You don't have to derive these critical values every time, though. Just use a table like the one linked below :-) d2vlcm61l7u1fs.cloudfront.net/media%2F9c6%2F9c6bd757-2ba1-42df-b352-77984fe4af33%2FphpXMJ7TR.png

[@WisdomValleser]

Could you please explain when to use which critical value? I seem to find 2 versions of the table for Critical Values of the Wilcoxon Signed Ranks test: this one facultyweb.berry.edu/vbissonnette/tables/wilcox_t.pdf and this one users.sussex.ac.uk/~grahamh/RM1web/WilcoxonTable2005.pdf Thanks! Sorry if that might sound like dumb question. :)

[@MatthiasKullowatz]

WisdomValleser Not dumb at all! These tables are not intuitive. The two tables are saying basically the same things, but the second link has a slightly different set of critical values than the first, so it seems different. I would stick to the first link, and read it like this: 1) Choose your sample size from the left column. 2) Decide whether your alternative hypothesis is one-tailed or two-tailed. 3) Choose 5% or 1% level of significance, and scroll down to the appropriate cell in the table. 4) Add up the ranks of the positive and negative differences separately, and take the smallest absolute value (like in the video). 5) If the number you got in step 4 is smaller than the number in the table from step 3, then you reject the null hypothesis. Video example: n = 12, Test stat = 7. Two-tailed, 5% level of significance critical value: 13. 7 < 13, so we reject the null hypothesis.

[@WisdomValleser]

Matthias Kullowatz thanks a lot!

[@นางสาววิราญสุขจันดี]

Thank you so much.

[@muhammadsabhannurdiansyah1277]

this is very helpfull

[@rosamrc923]

thanks A LOT!

[@GaryHutsonVBA]

Found it. Sorry! :)

[@MatthiasKullowatz]

I was going to say, not all tables are the same. Some give upper tailed critical values and others lower tailed. Note that every test actually has two test statistics, depending on your table. If, for example, you found positive differences ranked 1, 4, 7 and 9 (of nine), and negative differences at 2, 3, 6, and 8. Then your test stat could be either 21 or 19. This table includes the lower rank values: facultyweb.berry.edu/vbissonnette/tables/wilcox_t.pdf

[@GaryHutsonVBA]

Yep! Chi Square you normally cut off the right tail to find significance. But, equally you can divide the tails to see if the probability is lower or higher than the chosen confidence limit. Thanks