SPEARMANR

Overview

The SPEARMANR function computes the Spearman rank-order correlation coefficient and its associated p-value between two variables. This nonparametric measure assesses the strength and direction of the monotonic relationship between paired data, without assuming a linear relationship or normal distribution. The Spearman correlation is calculated as the Pearson correlation coefficient between the ranked variables:

r_s = 1 - \frac{6 \sum d_i^2}{n(n^2-1)}

where $d_i$ is the difference between the ranks of each observation and $n$ is the number of observations. For more details, see the scipy.stats.spearmanr documentation .

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

Usage

To use the function in Excel:


=SPEARMANR(x, y)

x (2D list, required): Table (column vector) of numeric values, at least two rows.
y (2D list, required): Table (column vector) of numeric values, same number of rows as x.

The function returns a single-row, two-column array: [correlation, p_value], or an error message (string) if the input is invalid.

Examples

Example 1: Perfect Monotonic Increase

Inputs:

x	y
1	5
2	6
3	7
4	8
5	9

Excel formula:


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

Expected output:

correlation	p_value
1.000	0.000

Example 2: Perfect Monotonic Decrease

Inputs:

x	y
1	9
2	8
3	7
4	6
5	5

Excel formula:


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

Expected output:

correlation	p_value
-1.000	0.000

Example 3: No Monotonic Relationship (Constant x)

Inputs:

x	y
1	1
1	2
1	3
1	4

Excel formula:


=SPEARMANR({1;1;1;1}, {1;2;3;4})

Expected output:

correlation	p_value
0.000	0.000

Example 4: Real Data Example

Inputs:

x	y
1	5
2	3
3	6
4	2
5	1

Excel formula:


=SPEARMANR({1;2;3;4;5}, {5;3;6;2;1})

Expected output:

correlation	p_value
-0.700	0.188

Python Code


from scipy.stats import spearmanr as scipy_spearmanr
 
def spearmanr(x, y):
    """
    Calculate a Spearman correlation coefficient with associated p-value.
 
    Args:
        x: 2D list of values (numeric), must have at least two rows.
        y: 2D list of values (numeric), must have the same number of rows as x.
 
    Returns:
        2D list [[correlation, p_value]] or an error message (str) if input is invalid.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Validate x and y
    if not isinstance(x, list) or not isinstance(y, list):
        return "Invalid input: x and y must be 2D lists."
    # Wrap scalar or 1D list
    if not isinstance(x[0], list):
        x = [[v] for v in x] if isinstance(x, list) else [[x]]
    if not isinstance(y[0], list):
        y = [[v] for v in y] if isinstance(y, list) else [[y]]
    # Validate rows
    if len(x) < 2 or len(y) < 2:
        return "Invalid input: x and y must have at least two rows."
    if len(x) != len(y):
        return "Invalid input: x and y must have the same number of rows."
    # Flatten to 1D
    try:
        x_flat = [float(row[0]) for row in x]
        y_flat = [float(row[0]) for row in y]
    except Exception:
        return "Invalid input: x and y must contain numeric values."
    # Handle constant input: correlation undefined, return zeros
    try:
        if len(set(x_flat)) == 1 or len(set(y_flat)) == 1:
            return [[0.0, 0.0]]
    except Exception:
        pass
    try:
        result = scipy_spearmanr(x_flat, y_flat)
        corr = float(result.statistic)
        pval = float(result.pvalue)
        # Disallow nan or inf
        if any([corr != corr, pval != pval, abs(corr) == float('inf'), abs(pval) == float('inf')]):
            return "Invalid result: correlation or p-value is not finite."
        return [[corr, pval]]
    except Exception as e:
        return f"scipy.stats.spearmanr error: {e}"

Example Workbook

Link to Workbook