Statistical
Mann-Whitney U test (Wilcoxon Rank-Sum) statistic & AUC effect size
Calculate a pooled-rank Mann–Whitney statistic and a descriptive pairwise superiority estimate for two independent groups.
Use pooled average ranks, including tied values
Pool the two independent groups and assign ascending average ranks. RANK alone assigns the lowest tied rank. The helper adds (COUNTIF(range,value)-1)/2 to that rank, giving the midpoint of tied positions.
Subtract nA*(nA+1)/2 from the rank sum of group A to obtain its pairwise-win statistic, counting each tie as half a win. Divide by nA*nB for the empirical probability-of-superiority estimate. A value of 0.5 does not prove identical distributions.
This calculates statistics, not a p-value, confidence interval or significance decision. Rank methods are not automatically preferable whenever data are skewed, and changing an observation can change the result if its rank changes. Interpret a location shift only with appropriate distributional assumptions.
| Group & Values | Combined Ascending Ranks (1..8) | Rank Sum R | Mann-Whitney U & AUC |
|---|---|---|---|
| Group A [12, 18, 25, 95] (n1=4) | 3, 5, 7, 8 | R1 = 23 | U1 = 23 - 10 = 13 wins |
| Group B [8, 10, 15, 20] (n2=4) | 1, 2, 4, 6 | R2 = 13 | U2 = 16 - 13 = 3 (Test U = 3) |
| Pairwise Win Rate (AUC) | =U1 / (n1 * n2) | 13 / 16 | 81.25% (unchanged only if ranks stay unchanged) |
Mann-Whitney U1, U = MIN(U1, U2), and Common-Language Effect Size (AUC)
SUMIF(A2:A9, "A", C2:C9) totals Group A's pooled ascending ranks R1, and COUNTIF(A2:A9, "A") counts n1.
=IF(OR(COUNT(B2:B9)<>8,COUNTIF(A2:A9,"A")=0,COUNTIF(A2:A9,"B")=0,COUNTIF(A2:A9,"A")+COUNTIF(A2:A9,"B")<>8),"Invalid groups",LET(countA, COUNTIF(A2:A9, "A"), countB, COUNTIF(A2:A9, "B"), rankSumA, SUMIF(A2:A9, "A", C2:C9), winsA, rankSumA - countA * (countA + 1) / 2, MIN(winsA, countA * countB - winsA)))Pairwise superiority with half credit for ties
The sample fraction estimates P(A>B)+0.5*P(A=B), not a proof about population distributions.
=IF(OR(COUNT(B2:B9)<>8,COUNTIF(A2:A9,"A")=0,COUNTIF(A2:A9,"B")=0,COUNTIF(A2:A9,"A")+COUNTIF(A2:A9,"B")<>8),"Invalid groups",LET(countA, COUNTIF(A2:A9, "A"), countB, COUNTIF(A2:A9, "B"), rankSumA, SUMIF(A2:A9, "A", C2:C9), winsA, rankSumA - countA * (countA + 1) / 2, ROUND(winsA / (countA * countB), 4)))Try the example
Use the original values or change them to test your understanding.