Wilcoxon Rank Sum Test

[RLL]

Hi,

Is there a method to calculate a Wilcoxon Rank Sum Test in Excel?

 

[hgrove]

[RLL wrote...

Is there a method to calculate a Wilcoxon Rank Sum Test in
Excel?

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)

 

[Jerry W. Lewis]

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?

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))

 

[hgrove]

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.

...

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.