CUBIC_HERMITE_SPLINE

Overview

The CUBIC_HERMITE_SPLINE function performs piecewise cubic interpolation of a 1-D function, matching both function values and first derivatives at each data point. This method is useful for smooth curve fitting in engineering, graphics, and scientific computing, where both the value and slope of the function are known at each sample point. The interpolation is $C^1$ continuous (smooth in both value and first derivative) and is based on the Hermite form of the cubic polynomial:

H(x) = h_{00}(t) y_0 + h_{10}(t) (x_1 - x_0) dydx_0 + h_{01}(t) y_1 + h_{11}(t) (x_1 - x_0) dydx_1

where $h_{00}$ , $h_{10}$ , $h_{01}$ , and $h_{11}$ are Hermite basis functions, $y_0$ , $y_1$ are function values, and $dydx_0$ , $dydx_1$ are derivatives at the endpoints of each interval. For more details, see the SciPy CubicHermiteSpline documentation and the Wikipedia article .

This function is a simplified wrapper for the SciPy CubicHermiteSpline class, supporting only 1D arrays and not exposing advanced options like axis or extrapolate.

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

Usage

To use the function in Excel:


=CUBIC_HERMITE_SPLINE(x, y, dydx, x_new)

x (2D list, required): Table of one column with at least two rows, containing strictly increasing x-coordinates of data points.
y (2D list, required): Table of one column with at least two rows, containing function values that correspond to each x.
dydx (2D list, required): Table of one column with at least two rows, containing first derivatives at each x.
x_new (float or 2D list, required): Scalar or table of one column with at least one row, providing the x-coordinates where interpolation is desired.

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

Examples

Example 1: Interpolate a Sine Curve

Inputs:

x	y	dydx	x_new
0	0.0	1.0	0.5
1	0.84147	0.5403	0.75
2	0.909297	-0.4161	1.5
3	0.14112	-0.98999	2.5

Excel formula:


=CUBIC_HERMITE_SPLINE({0;1;2;3}, {0;0.84147;0.909297;0.14112}, {1;0.5403;-0.4161;-0.98999}, {0.5;0.75;1.5;2.5})

Expected output:

Result
0.478
0.681
0.995
0.597

Example 2: Linear Data

Inputs:

x	y	dydx	x_new
0	0	1	0.5
1	1	1	1.5
2	2	1	2.0

Excel formula:


=CUBIC_HERMITE_SPLINE({0;1;2}, {0;1;2}, {1;1;1}, {0.5;1.5;2.0})

Expected output:

Result
0.500
1.500
2.000

Example 3: Quadratic Data

Inputs:

x	y	dydx	x_new
0	0	0	0.5
1	1	2	1.5
2	4	4	2.0

Excel formula:


=CUBIC_HERMITE_SPLINE({0;1;2}, {0;1;4}, {0;2;4}, {0.5;1.5;2.0})

Expected output:

Result
0.250
2.250
4.000

Example 4: Flat Data

Inputs:

x	y	x_new
0	2	0.5
1	2	1.0
2	2	1.5

Excel formula:


=CUBIC_HERMITE_SPLINE({0;1;2}, {2;2;2}, {0;0;0}, {0.5;1.0;1.5})

Expected output:

Result
2.000
2.000
2.000

Python Code


import math
from typing import List, Tuple, Union
 
from scipy.interpolate import CubicHermiteSpline as scipy_cubic_hermite_spline
 
 
ExcelColumnFloat = List[List[Union[float, int, str]]]
 
 
def cubic_hermite_spline(
    x: List[List[Union[int, float]]],
    y: List[List[Union[int, float]]],
    dydx: List[List[Union[int, float]]],
    x_new: Union[List[List[Union[int, float]]], Union[int, float]]
) -> ExcelColumnFloat:
    """
    Piecewise cubic interpolation matching values and first derivatives at each point.
 
    Args:
        x: 2D list with at least two rows, strictly increasing x-coordinates of data points.
        y: 2D list with at least two rows, function values at each x.
        dydx: 2D list with at least two rows, first derivatives at each x.
        x_new: Scalar or 2D list of x-coordinates where interpolation is desired.
 
    Returns:
        2D list (column vector) of interpolated values at each x_new, or a list of one string error message if input is invalid.
 
    This example function is provided as-is without any representation of accuracy.
    """
 
    def _error(message: str) -> ExcelColumnFloat:
        return [[message]]
 
    def _parse_input_column(
        data: List[List[Union[int, float]]],
        name: str
    ) -> Tuple[Union[List[float], None], Union[str, None]]:
        if not isinstance(data, list):
            return None, f"Invalid input: {name} must be a 2D list with at least two rows."
        if len(data) < 2:
            return None, f"Invalid input: {name} must be a 2D list with at least two rows."
        values: List[float] = []
        for row in data:
            if not isinstance(row, list) or not row:
                return None, f"Invalid input: {name} must be a 2D list containing numeric values."
            try:
                value = float(row[0])
            except Exception:
                return None, f"Invalid input: {name} must contain numeric values."
            if not math.isfinite(value):
                return None, f"Invalid input: {name} must contain finite numbers."
            values.append(value)
        return values, None
 
    def _parse_x_new(
        data: Union[List[List[Union[int, float]]], Union[int, float]]
    ) -> Tuple[Union[List[float], None], Union[str, None]]:
        if isinstance(data, (int, float)):
            if not math.isfinite(float(data)):
                return None, "Invalid input: x_new must contain finite numbers."
            return [float(data)], None
        if not isinstance(data, list):
            return None, "Invalid input: x_new must be a scalar or 2D list containing numeric values."
        if len(data) == 0:
            return None, "Invalid input: x_new must contain at least one value."
        values: List[float] = []
        for row in data:
            candidate = row
            if isinstance(row, list):
                if not row:
                    return None, "Invalid input: x_new must contain numeric values."
                candidate = row[0]
            try:
                value = float(candidate)
            except Exception:
                return None, "Invalid input: x_new must contain numeric values."
            if not math.isfinite(value):
                return None, "Invalid input: x_new must contain finite numbers."
            values.append(value)
        return values, None
 
    x_values, error = _parse_input_column(x, "x")
    if error is not None or x_values is None:
        return _error(error)
    y_values, error = _parse_input_column(y, "y")
    if error is not None or y_values is None:
        return _error(error)
    dydx_values, error = _parse_input_column(dydx, "dydx")
    if error is not None or dydx_values is None:
        return _error(error)
    if not (len(x_values) == len(y_values) == len(dydx_values)):
        return _error("Invalid input: x, y, dydx must have the same number of rows.")
    if any(x2 <= x1 for x1, x2 in zip(x_values, x_values[1:])):
        return _error("Invalid input: x must be strictly increasing.")
 
    x_new_values, error = _parse_x_new(x_new)
    if error is not None or x_new_values is None:
        return _error(error)
 
    # Construct the spline and evaluate at the requested points.
    try:
        spline = scipy_cubic_hermite_spline(x_values, y_values, dydx_values)
        y_new = spline(x_new_values)
    except Exception as exc:
        return _error(f"scipy.interpolate.CubicHermiteSpline error: {exc}")
 
    # Convert output to a 2D list while validating the results are finite numbers.
    results: ExcelColumnFloat = []
    for value in y_new:
        try:
            numeric_value = float(value)
        except Exception:
            return _error("Invalid result: interpolation produced a non-numeric value.")
        if not math.isfinite(numeric_value):
            return _error("Invalid result: interpolation produced a non-finite value.")
        results.append([numeric_value])
    return results

Example Workbook

Link to Workbook