TRAPEZOID

Overview

The TRAPEZOID function numerically integrates sampled data using the composite trapezoidal rule. This method estimates the area under a curve by approximating the region between each pair of points as a trapezoid and summing their areas. It is commonly used for integrating discrete data points or functions sampled at regular or irregular intervals. The y-axis locations of points will be taken from the y array, and the x-axis sample points must be provided with the x array. The return value will be equal to the combined area under the red lines in the illustration below.

The composite trapezoidal rule is given by:

\int_a^b f(x) dx \approx \sum_{i=1}^{n-1} \frac{y_{i} + y_{i+1}}{2} (x_{i+1} - x_{i})

where $y_i$ are the function values at points $x_i$ .

This implementation only supports a single column for both y and x. This is more restrictive than the underlying scipy.integrate.trapezoid function, which supports higher-dimensional arrays and more advanced use cases. For more details, see the scipy.integrate.trapezoid documentation and the Wikipedia page .

Image source: Wikipedia - Composite trapezoidal rule illustration

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

Usage

To use the function in Excel:


=TRAPEZOID(y, x)

y (2D list, required): Single column table of values to integrate. Must have at least two rows.
x (2D list, required): Single column table of sample points corresponding to y. Must have the same number of rows as y and at least two rows.

The function returns a float. Returns an error message (string) if the input is invalid.

Examples

Example 1: Integrate a non-linear vector

This example integrates the vector [1, 4, 9] (squares) with sample points [0, 1, 2].

Inputs:

y	x
1	0
4	1
9	2

Excel formula:


=TRAPEZOID({1;4;9},{0;1;2})

Expected output:

Result
9.000

This means the area under the curve defined by [1,4,9] is 9.0.

Example 2: Integrate with custom x values

This example integrates [1, 2, 3] with sample points [4, 6, 8].

Inputs:

y	x
1	4
2	6
3	8

Excel formula:


=TRAPEZOID({1;2;3},{4;6;8})

Expected output:

Result
8.000

This means the area under the curve with custom sample points is 8.000.

Example 3: Integrate constant samples

This example integrates the vector [1, 1, 1] with evenly spaced sample points.

Inputs:

y	x
1	0
1	1
1	2

Excel formula:


=TRAPEZOID({1;1;1},{0;1;2})

Expected output:

Result
2.000

This confirms the integral equals the area of a rectangle with height 1.000 and width 2.000.

Example 4: Integrate symmetric negative and positive values

This example integrates [-1, 0, 1] over [0, 1, 2], which cancels out symmetrically.

Inputs:

y	x
-1	0
0	1
1	2

Excel formula:


=TRAPEZOID({-1;0;1},{0;1;2})

Expected output:

Result
0.000

This demonstrates that symmetric negative and positive contributions can sum to zero.

Python Code


import math
from scipy.integrate import trapezoid as scipy_trapezoid
 
 
def trapezoid(y, x):
    """
    Integrate sampled data using the composite trapezoidal rule.
 
    Args:
        y: 2D list with a single column containing the values to integrate. Must have at least two rows.
        x: 2D list with a single column of sample points corresponding to ``y``. Must match the number of rows in ``y``.
 
    Returns:
        The computed integral (float) or an error message (str) when validation fails.
 
    This example function is provided as-is without any representation of accuracy.
    """
 
    def _normalize_column(data, name):
        # Normalize scalar to single-element 2D list and validate a single-column 2D list
        if isinstance(data, (int, float, bool)):
            data = [[float(data)]]
        if not isinstance(data, list):
            return f"Invalid input: {name} must be a 2D list with at least two rows."
        if len(data) < 2:
            return f"Invalid input: {name} must be a 2D list with at least two rows."
        column = []
        for row in data:
            if not isinstance(row, list) or len(row) != 1:
                return f"Invalid input: {name} must be a single column 2D list."
            try:
                numeric = float(row[0])
            except Exception:
                return f"Invalid input: {name} must contain numeric values."
            if math.isnan(numeric) or math.isinf(numeric):
                return f"Invalid input: {name} must contain finite numbers."
            column.append(numeric)
        return column
 
    y_values = _normalize_column(y, "y")
    if isinstance(y_values, str):
        return y_values
    x_values = _normalize_column(x, "x")
    if isinstance(x_values, str):
        return x_values
    if len(x_values) != len(y_values):
        return "Invalid input: x must have the same number of rows as y."
 
    # Compute trapezoidal integration using SciPy.
    try:
        result = scipy_trapezoid(y_values, x=x_values)
    except Exception as exc:  # noqa: BLE001 - propagate SciPy errors as messages
        return f"scipy.integrate.trapezoid error: {exc}"
 
    if not isinstance(result, (int, float)):
        return "scipy.integrate.trapezoid error: non-numeric result."
    numeric_result = float(result)
    if math.isnan(numeric_result) or math.isinf(numeric_result):
        return "scipy.integrate.trapezoid error: result is not finite."
    return numeric_result

Example Workbook

Link to Workbook