Wilcoxon Rank Sum Test

  • Thread starter Thread starter Guest
  • Start date Start date
[RLL wrote...
Is there a method to calculate a Wilcoxon Rank Sum Test in
Excel?
Click to expand...

No, but you can use an array formula to calculate the test statistic
If you have your two samples in ranged named SampleA and SampleB wit
SampleA the smaller of the two, try the array formula

=SUM(LOOKUP(SampleA,SMALL((SampleA,SampleB),
ROW(INDIRECT("1:"&COUNT((SampleA,SampleB))))),
(RANK(SMALL((SampleA,SampleB),ROW(INDIRECT("1:"
&COUNT((SampleA,SampleB))))),(SampleA,SampleB),1)
+COUNT((SampleA,SampleB),1)-RANK(SMALL((SampleA,SampleB),
ROW(INDIRECT("1:"&COUNT((SampleA,SampleB))))),
(SampleA,SampleB),0))/2)
 
Assumes that there are no ties in the data. Excel's RANK() function
assigns the lowest of the tied ranks to all tied values, whereas
nonparametric statistical procedures use the median of the tied ranks.

Jerry
[RLL wrote...
Is there a method to calculate a Wilcoxon Rank Sum Test in
Excel?
Click to expand...

No, but you can use an array formula to calculate the test statistic.
If you have your two samples in ranged named SampleA and SampleB with
SampleA the smaller of the two, try the array formula

=SUM(LOOKUP(SampleA,SMALL((SampleA,SampleB),
ROW(INDIRECT("1:"&COUNT((SampleA,SampleB))))),
(RANK(SMALL((SampleA,SampleB),ROW(INDIRECT("1:"
&COUNT((SampleA,SampleB))))),(SampleA,SampleB),1)
+COUNT((SampleA,SampleB),1)-RANK(SMALL((SampleA,SampleB),
ROW(INDIRECT("1:"&COUNT((SampleA,SampleB))))),
(SampleA,SampleB),0))/2))
Click to expand...
 
Jerry W. Lewis write...
Assumes that there are no ties in the data. Excel's RANK()
function assigns the lowest of the tied ranks to all tied values,
whereas nonparametric statistical procedures use the median of
the tied ranks.
Click to expand...
...

REREAD the term (reformatted for your benefit)

(
_RANK(
__SMALL(
___(SampleA,SampleB),
___ROW(INDIRECT("1:"&COUNT((SampleA,SampleB))))
__),
__(SampleA,SampleB),
__1
_)
_+COUNT(
__(SampleA,SampleB),
__1
_)
_-RANK(
__SMALL(
___(SampleA,SampleB),
___ROW(INDIRECT("1:"&COUNT((SampleA,SampleB))))
__),
__(SampleA,SampleB),
__0
_)
)/2

which handles ties in the prescribed manner. In other words, if Sample
were {3.3;1.5} and SampleB were {10.2;4.5;3.3}, then the term abov
*DOES* return {1;2.5;2.5;4;5} rather than the {1;2;2;4;5} which i
seems you believe it does.

At least try my formulas next time.
 
Back
Top