THEILSLOPES

Overview

The THEILSLOPES function computes the Theil-Sen estimator for a set of points, providing a robust linear regression line that is less sensitive to outliers than ordinary least squares. It returns the slope, intercept, and a confidence interval for the slope. The Theil-Sen estimator is the median of all slopes between paired values, making it robust for data with outliers or non-normal errors. For more details, see the scipy.stats.theilslopes documentation .

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

Usage

To use the function in Excel:


=THEILSLOPES(y, [x], [alpha], [method])

y (2D list, required): Dependent variable. Each row is an observation.
x (2D list, optional, default: sequence 0, 1, …, n-1): Independent variable. Must have the same number of rows as y.
alpha (float, optional, default: 0.95): Confidence level for the slope interval (between 0 and 1).
method (str, optional, default: “separate”): Method for intercept calculation. Either "separate" or "joint".

The function returns a 2D list with one row and four columns: [slope, intercept, low_slope, high_slope].

Examples

Example 1: Basic usage with default x

Inputs:

y	alpha	method
1	0.95	separate
2
3
4
5

Excel formula:


=THEILSLOPES({1;2;3;4;5})

Expected output:

slope	intercept	low_slope	high_slope
1	1	1	1

Example 2: Custom x and y

Inputs:

y	x	alpha	method
2	1	0.95	separate
4	2
6	3
8	4
10	5

Excel formula:


=THEILSLOPES({2;4;6;8;10}, {1;2;3;4;5})

Expected output:

slope	intercept	low_slope	high_slope
2	0	2	2

Example 3: With outliers

Inputs:

y	x	alpha	method
1	1	0.95	separate
2	2
3	3
100	4
5	5

Excel formula:


=THEILSLOPES({1;2;3;100;5}, {1;2;3;4;5})

Expected output:

slope	intercept	low_slope	high_slope
1	0	-95	97

Example 4: Custom alpha and method

Inputs:

y	x	alpha	method
1	1	0.9	joint
2	2
3	3
4	4
5	5

Excel formula:


=THEILSLOPES({1;2;3;4;5}, {1;2;3;4;5}, 0.9, "joint")

Expected output:

slope	intercept	low_slope	high_slope
1	0	1	1

Excel does not have a direct equivalent for the Theil-Sen estimator. While Excel provides linear regression tools such as LINEST and SLOPE, these are based on ordinary least squares and are not robust to outliers. Theil-Sen regression is more robust and is not natively available in Excel.

Python Code


from scipy.stats import theilslopes as scipy_theilslopes
 
def theilslopes(y, x=None, alpha=0.95, method="separate"):
    """
    Compute the Theil-Sen estimator for a set of points (robust linear regression).
 
    Args:
        y: 2D list of dependent variable values. Each row is an observation.
        x: 2D list of independent variable values (optional). Must have the same number of rows as y. If None, uses 0, 1, ..., n-1.
        alpha: Confidence level for the slope interval (float, default: 0.95).
        method: Method for intercept calculation ("separate" or "joint", default: "separate").
 
    Returns:
        2D list with one row: [slope, intercept, low_slope, high_slope], or an error message (str) if input is invalid.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Validate y
    if not isinstance(y, list) or len(y) < 2:
        return "Invalid input: y must be a 2D list with at least two rows."
    try:
        y_flat = [float(row[0]) if isinstance(row, list) else float(row) for row in y]
    except Exception:
        return "Invalid input: y must contain numeric values."
    n = len(y_flat)
    # Validate x
    if x is None or x == "":
        x_flat = list(range(n))
    else:
        if not isinstance(x, list) or len(x) != n:
            return "Invalid input: x must be a 2D list with the same number of rows as y."
        try:
            x_flat = [float(row[0]) if isinstance(row, list) else float(row) for row in x]
        except Exception:
            return "Invalid input: x must contain numeric values."
    # Validate alpha
    try:
        alpha_val = float(alpha)
    except Exception:
        return "Invalid input: alpha must be a float."
    if not (0 < alpha_val < 1):
        return "Invalid input: alpha must be between 0 and 1."
    # Validate method
    if method not in ("separate", "joint"):
        return "Invalid input: method must be 'separate' or 'joint'."
    try:
        res = scipy_theilslopes(y_flat, x_flat, alpha=alpha_val, method=method)
        # Ensure no NaN/inf in result
        vals = [res.slope, res.intercept, res.low_slope, res.high_slope]
        for v in vals:
            if v is None or (isinstance(v, float) and (v != v or v == float("inf") or v == float("-inf"))):
                return "Computation resulted in invalid value."
        return [vals]
    except Exception as e:
        return f"scipy.stats.theilslopes error: {e}"

Example Workbook

Link to Workbook