Math & Statistical
Wilcoxon Signed-Rank Paired Nonparametric Test (W, Mean & Z-Score)
Calculate the paired Wilcoxon Signed-Rank statistic W = MIN(W_plus, W_minus), null expectation mu_W = n*(n+1)/4, standard deviation sigma_W = SQRT(n*(n+1)*(2*n+1)/24), and normal-approximation Z-score.
Test paired before-vs-after medians without assuming normal differences!
When paired before-and-after measurements (pain visual-analog scores, skewed response latencies, small clinical cohorts) violate the paired t-test's normality assumption, Frank Wilcoxon's Signed-Rank Test ranks the absolute non-zero differences |d_i| from 1 to n, attaches the original + or - sign to each rank, and sums positive ranks (W^+) vs negative ranks (W^-)!
Notice the built-in checksum: W^+ + W^- MUST equal the triangular sum n * (n + 1) / 2! Under the null hypothesis of zero median shift, expected rank sum is mu_W = n * (n + 1) / 4 with standard deviation sigma_W = SQRT((n * (n + 1) * (2 * n + 1)) / 24), yielding the standardized test statistic |Z| = ABS(MIN(W^+, W^-) - mu_W) / sigma_W (significant at two-sided alpha = 0.05 when |Z| >= 1.96)!
| Wilcoxon Metric (n = 10 Non-Zero Pairs) | Formula | Treatment Shift (W+ = 49, W- = 6) | Null-Like Split (W+ = 30, W- = 25) |
|---|---|---|---|
| Checksum W+ + W- | n * (n + 1) / 2 | 49 + 6 = 55 (Valid!) | 30 + 25 = 55 (Valid!) |
| Test Statistic W & Null Mean mu_W | MIN(W+, W-) vs n*(n+1)/4 | W = 6 vs mu_W = 27.50 | W = 25 vs mu_W = 27.50 |
| Std Dev sigma_W & |Z| Statistic | ABS(W - mu_W) / sigma_W | sigma = 9.8107 -> |Z| = 2.191 (> 1.96!) | |Z| = 0.255 (Not Significant) |
Wilcoxon Signed-Rank Z-Statistic (with Rank-Sum Checksum Guard)
Verifies W^+ + W^- = n * (n + 1) / 2 so mis-ranked pairs are caught immediately, then computes |Z| = ABS(MIN(W^+, W^-) - mu_W) / sigma_W.
=IF(B2 < 5, "Use Exact Table for n < 5", IF(ROUND(B3 + B4, 2) <> (B2 * (B2 + 1)) / 2, "Rank Sum Checksum Mismatch", ROUND(ABS(MIN(B3, B4) - ((B2 * (B2 + 1)) / 4)) / SQRT((B2 * (B2 + 1) * (2 * B2 + 1)) / 24), 3)))Two-Sided alpha = 0.05 Nonparametric Decision (|Z| >= 1.96)
Compares |Z| against the two-tailed 5% critical value 1.96.
=IF((ABS(MIN(B3, B4) - ((B2 * (B2 + 1)) / 4)) / SQRT((B2 * (B2 + 1) * (2 * B2 + 1)) / 24)) >= 1.96, "Significant Paired Shift (p < 0.05)", "No Significant Shift")Try the example
Use the original values or change them to test your understanding.