RANKSUMS

Overview

The RANKSUMS function performs the Wilcoxon rank-sum test (also known as the Mann–Whitney U test) to determine whether two independent samples are drawn from the same distribution. This nonparametric test is particularly useful when the assumptions of the parametric t-test (such as normality) cannot be met, or when working with ordinal data.

The test works by combining both samples, ranking all observations from smallest to largest, and then comparing the sum of ranks between the two groups. Under the null hypothesis that both samples come from identical distributions, the ranks should be randomly distributed between the groups. The test statistic measures how much the observed rank sums deviate from what would be expected by chance.

This implementation uses the scipy.stats.ranksums function from SciPy, which computes a large-sample approximation where the rank sum statistic is assumed to be normally distributed. For large samples (typically n > 20), this approximation is quite accurate.

The z-statistic is calculated using the normal approximation:

z = \frac{U - \mu_U}{\sigma_U}

where \mu_U = \frac{n_1 n_2}{2} is the expected value of the U statistic and \sigma_U = \sqrt{\frac{n_1 n_2 (n_1 + n_2 + 1)}{12}} is the standard deviation under the null hypothesis.

The function supports three alternative hypotheses: two-sided tests whether the distributions differ in either direction, less tests whether the first sample is stochastically smaller, and greater tests whether the first sample is stochastically larger. Note that this implementation does not handle ties between measurements; for tie-handling and an optional continuity correction, consider using the Mann–Whitney U test variant via scipy.stats.mannwhitneyu.

This example function is provided as-is without any representation of accuracy.

Excel Usage

=RANKSUMS(x, y, rs_alternative)

• x (list[list], required): First sample data as a 2D array of numeric values.
• y (list[list], required): Second sample data as a 2D array of numeric values.
• rs_alternative (str, optional, default: “two-sided”): Defines the alternative hypothesis for the test.

Returns (list[list]): 2D list [[statistic, p_value]], or error message string.

Examples

Example 1: Basic two-sided test

Inputs:

x		y
1	2	5	6
3	4	7	8

Excel formula:

=RANKSUMS({1,2;3,4}, {5,6;7,8})

Expected output:

Result
-2.3094	0.0209

Example 2: One-sided test (less)

Inputs:

x		y		rs_alternative
1	2	5	6	less
3	4	7	8

Excel formula:

=RANKSUMS({1,2;3,4}, {5,6;7,8}, "less")

Expected output:

Result
-2.3094	0.0105

Example 3: One-sided test (greater)

Inputs:

x		y		rs_alternative
1	2	5	6	greater
3	4	7	8

Excel formula:

=RANKSUMS({1,2;3,4}, {5,6;7,8}, "greater")

Expected output:

Result
-2.3094	0.9895

Example 4: Larger sample sizes

Inputs:

x		y
1	2	7	8
3	4	9	10
5	6	11	12

Excel formula:

=RANKSUMS({1,2;3,4;5,6}, {7,8;9,10;11,12})

Expected output:

Result
-2.8823	0.0039

Python Code

from scipy.stats import ranksums as scipy_ranksums
import math

def ranksums(x, y, rs_alternative='two-sided'):
    """
    Computes the Wilcoxon rank-sum statistic and p-value for two independent samples.

    See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.ranksums.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        x (list[list]): First sample data as a 2D array of numeric values.
        y (list[list]): Second sample data as a 2D array of numeric values.
        rs_alternative (str, optional): Defines the alternative hypothesis for the test. Valid options: Two-sided, Less, Greater. Default is 'two-sided'.

    Returns:
        list[list]: 2D list [[statistic, p_value]], or error message string.
    """
    def to2d(val):
        return [[val]] if not isinstance(val, list) else val

    x = to2d(x)
    y = to2d(y)

    if not isinstance(x, list) or not all(isinstance(row, list) for row in x):
        return "Invalid input: x must be a 2D list."
    if not isinstance(y, list) or not all(isinstance(row, list) for row in y):
        return "Invalid input: y must be a 2D list."

    try:
        x_flat = [float(item) for row in x for item in row]
        y_flat = [float(item) for row in y for item in row]
    except (TypeError, ValueError):
        return "Invalid input: x and y must contain only numeric values."

    if len(x_flat) < 2 or len(y_flat) < 2:
        return "Invalid input: x and y must each contain at least two values."

    if rs_alternative not in ('two-sided', 'less', 'greater'):
        return "Invalid input: rs_alternative must be 'two-sided', 'less', or 'greater'."

    try:
        result = scipy_ranksums(x_flat, y_flat, alternative=rs_alternative)
        stat, pvalue = float(result.statistic), float(result.pvalue)

        if math.isnan(stat) or math.isinf(stat) or math.isnan(pvalue) or math.isinf(pvalue):
            return "Invalid result: statistic or pvalue is nan or inf."

        return [[stat, pvalue]]
    except Exception as e:
        return f"scipy.stats.ranksums error: {e}"

Online Calculator