CUBIC_SPLINE

Overview

The CUBIC_SPLINE function performs cubic spline interpolation for a set of data points, returning interpolated values at specified query points. Cubic spline interpolation fits a piecewise cubic polynomial that is twice continuously differentiable, ensuring smooth transitions between data points. This method is widely used for smooth curve fitting in engineering, graphics, and data analysis. The underlying implementation uses SciPy’s CubicSpline class, which constructs the spline based on the provided data and boundary conditions.

The cubic spline $S(x)$ is defined such that:

where the coefficients are determined to ensure $S(x)$ and its first two derivatives are continuous at each $x_i$.

For more details, see the SciPy CubicSpline documentation .

This function simplifies the interface by only supporting 1D data, a subset of boundary conditions (not-a-knot, clamped, natural, periodic), and does not expose the axis or extrapolate options. This example function is provided as-is without any representation of accuracy.

Usage

To use the function in Excel:

=CUBIC_SPLINE(x, y, x_new, [bc_type])

• x (2D list, required): Table with one column, the independent variable (must be strictly increasing).
• y (2D list, required): Table with one column, the dependent variable (same number of rows as x).
• x_new (2D list, required): Table with one column, the points at which to evaluate the interpolated values.
• bc_type (str, optional, default=“not-a-knot”): Boundary condition type. One of "not-a-knot", "clamped", "natural", or "periodic".

The function returns a 2D list with one column, containing the interpolated values at each x_new point, or an error message (2D list of str) if the input is invalid.

Examples

Example 1: Interpolate a Sine Curve

Inputs:

Excel formula:

=CUBIC_SPLINE({0;1;2;3;4}, {0;0.8415;0.9093;0.1411;-0.7568}, {0.5;1.5;2.5;3.5;4.5})

Expected output:

Example 2: Natural Boundary Condition

Inputs:

Excel formula:

=CUBIC_SPLINE({0;1;2;3;4}, {0;0.8415;0.9093;0.1411;-0.7568}, {0.5;1.5;2.5;3.5;4.5}, "natural")

Expected output:

Example 3: Clamped Boundary Condition

Inputs:

Excel formula:

=CUBIC_SPLINE({0;1;2;3;4}, {0;0.8415;0.9093;0.1411;-0.7568}, {0.5;1.5;2.5;3.5;4.5}, "clamped")

Expected output:

Example 4: Periodic Boundary Condition

Inputs:

Excel formula:

=CUBIC_SPLINE({0;1;2;3;4}, {0;0.8415;0.9093;0.1411;-0.7568}, {0.5;1.5;2.5;3.5;4.5}, "periodic")

Expected output:

Python Code

from scipy.interpolate import CubicSpline as scipy_cubicspline
 
def cubic_spline(x, y, x_new, bc_type="not-a-knot"):
    """
    Perform cubic spline interpolation for 1D data.
 
    Args:
        x: 2D list, one column, independent variable (strictly increasing).
        y: 2D list, one column, dependent variable (same length as x).
        x_new: 2D list, one column, points to evaluate the spline at.
        bc_type: Boundary condition type (default: "not-a-knot").
 
    Returns:
        2D list with one column of interpolated values, or 2D list of str if error.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Validate input shapes
    try:
        x_flat = [float(row[0]) for row in x]
        y_flat = [float(row[0]) for row in y]
        x_new_flat = [float(row[0]) for row in x_new]
    except Exception:
        return [["Invalid input: x, y, and x_new must be 2D lists of numbers."]]
    if len(x_flat) != len(y_flat) or len(x_flat) < 2:
        return [["Invalid input: x and y must have the same length >= 2."]]
    if any(x2 <= x1 for x1, x2 in zip(x_flat, x_flat[1:])):
        return [["Invalid input: x must be strictly increasing."]]
    if bc_type not in ["not-a-knot", "clamped", "natural", "periodic"]:
        return [["Invalid input: unsupported bc_type."]]
    try:
        cs = scipy_cubicspline(x_flat, y_flat, bc_type=bc_type)
        y_new = cs(x_new_flat)
        return [[round(float(val), 4)] for val in y_new]
    except Exception as e:
        return [[f"scipy.CubicSpline error: {e}"]]

Live Notebook

Edit this function in a live notebook .

Live Demo