Interpolation

Overview

Interpolation is the mathematical process of constructing new data points within a discrete set of known data points. At its core, interpolation assumes that the true underlying relationship between variables can be approximated by connecting existing observations with some smooth or structured function. This makes it fundamental to data analysis, scientific computing, computer graphics, and engineering applications.

The basic problem is straightforward: given a set of points (x_1, y_1), (x_2, y_2), \ldots, (x_n, y_n), find a function f(x) such that f(x_i) = y_i for all i. The function f can then be used to estimate values at positions not in the original dataset. The choice of interpolation method depends on the data’s characteristics, the required smoothness, computational efficiency, and whether the data has measurement noise.

Interpolation differs from curve fitting in that it passes exactly through the data points, whereas fitting methods (like least squares) allow deviations to reduce the impact of noise. It also differs from extrapolation, which estimates values outside the range of known data points—a much riskier endeavor prone to larger errors.

Univariate Interpolation

Univariate interpolation operates on one-dimensional data, where both x and y are scalar values. This is the most common form of interpolation.

• Linear interpolation: Connects consecutive points with straight line segments. Simple and fast, but produces non-smooth results (discontinuous first derivatives).
• Cubic splines: Piecewise cubic polynomials that ensure continuity of the function, its first derivative, and second derivative. The CUBIC_SPLINE function provides this classic smooth interpolation.
• Hermite interpolation: Matches both function values and derivatives at data points. Implemented via HERMITE_SPLINE.
• Akima interpolation: A modified spline method that reduces overshoot near sharp changes in data, available through AKIMA_INTERP.
• PCHIP (Piecewise Cubic Hermite Interpolating Polynomial): Preserves monotonicity and avoids oscillations, particularly useful for financial or economic data where trends must be maintained. See PCHIP_INTERPOLATE.
• Polynomial interpolation: Fits a single polynomial through all points. While exact, high-degree polynomials can exhibit Runge’s phenomenon (wild oscillations between data points). Methods like Barycentric (BARYCENTRIC_INTERP) and Krogh (KROGH_INTERPOLATE) provide numerically stable implementations.

The general-purpose INTERP1D function offers multiple methods (linear, nearest, cubic, etc.) with a unified interface.

Multivariate Interpolation

Multivariate interpolation extends to two or more dimensions, where data points have the form (x_1, x_2, \ldots, x_n, y). This is essential for spatial data analysis, image processing, and scientific simulations.

• Structured grids: When data lies on a regular grid (e.g., pixels in an image, latitude/longitude grids), specialized methods are efficient. INTERPN handles N-dimensional regular grids, while GRID_INTERP focuses on 2D cases.
• Unstructured data: For scattered, irregular data points, GRIDDATA uses methods like Delaunay triangulation to interpolate values anywhere in the domain.
• Radial Basis Functions (RBF): A powerful approach for scattered data in arbitrary dimensions. RBF_INTERPOLATOR constructs a weighted sum of radially symmetric functions centered at each data point.
• Simple methods: LINEAR_ND_INTERP performs piecewise linear interpolation in N dimensions, while NEAREST_ND_INTERP assigns the value of the closest known data point.

Splines

Splines are piecewise polynomial functions that provide smooth interpolation with guaranteed continuity properties. They are the workhorse of modern interpolation.

• B-splines: Basis splines represent curves as linear combinations of basis functions. MAKE_INTERP_SPLINE constructs interpolating B-splines of arbitrary degree.
• Smoothing splines: When data contains noise, exact interpolation is undesirable. Smoothing splines (UNIVARIATE_SPLINE, SMOOTH_SPLINE) balance fidelity to data with smoothness by minimizing a penalized residual sum of squares.
• Least-squares splines: MAKE_LSQ_SPLINE fits a spline in a least-squares sense with user-defined knot positions, useful when knot placement is informed by domain knowledge.
• Univariate splines: INTERP_UV_SPLINE provides a simple interface for standard univariate spline interpolation.

Splines are computationally efficient and scale well to large datasets. They’re used in CAD software, font rendering, and path planning for robotics.

Approximation Methods

Beyond interpolation, approximation theory provides tools for representing functions efficiently.

• Lagrange interpolation: Constructs a polynomial explicitly passing through all points using Lagrange basis polynomials. LAGRANGE_INTERP implements this classical method.
• Padé approximation: Approximates a function using the ratio of two polynomials rather than a single polynomial. This often provides better accuracy with fewer terms, especially for functions with poles or asymptotic behavior. See PADE.

Native Excel capabilities

Excel provides limited built-in interpolation:

• FORECAST and FORECAST.LINEAR: Perform simple linear interpolation/extrapolation based on a linear trend.
• Chart trendlines: Can add polynomial, exponential, or logarithmic trendlines to scatter plots, but these are for visualization and curve fitting, not pure interpolation.
• Manual linear interpolation: Using simple formulas like =Y1+(Y2-Y1)*(X-X1)/(X2-X1) works for individual points but doesn’t scale.

Excel has no built-in support for cubic splines, multivariate interpolation, or advanced polynomial methods. Python functions provide access to industry-standard algorithms from SciPy, dramatically expanding capabilities.

Third-party Excel add-ins

• XLfit by IDBS: A commercial add-in focused on curve fitting and regression, with some interpolation features for scientific data analysis.
• XLSTAT: Offers spline interpolation and smoothing as part of its statistical analysis suite.
• NumXL: A time-series focused add-in with linear and spline interpolation capabilities for financial and econometric modeling.

These tools are limited compared to Python’s SciPy library, which provides dozens of interpolation methods with extensive customization options and superior performance for large datasets.

Approximation

Tool	Description
LAGRANGE_INTERP	Compute the Lagrange interpolating polynomial through a set of points.
PADE	Compute Pade rational approximation to a polynomial.

Multivariate

Tool	Description
GRID_INTERP	Interpolator on a regular grid in 2D.
GRIDDATA	Interpolate unstructured D-D data.
INTERPN	Multidimensional interpolation on regular grids (2D).
LINEAR_ND_INTERP	Piecewise linear interpolator in N > 1 dimensions.
NEAREST_ND_INTERP	Nearest neighbor interpolation in N > 1 dimensions.
RBF_INTERPOLATOR	Radial basis function interpolation in N dimensions.

Splines

Tool	Description
INTERP_UV_SPLINE	1-D interpolating spline for data.
MAKE_INTERP_SPLINE	Compute interpolating B-spline and evaluate at new points.
MAKE_LSQ_SPLINE	Compute LSQ-based fitting B-spline.
SMOOTH_SPLINE	Smoothing cubic spline.
UNIVARIATE_SPLINE	1-D smoothing spline fit to data.

Univariate

Tool	Description
AKIMA_INTERP	Akima 1D interpolation.
BARYCENTRIC_INTERP	Interpolating polynomial for a set of points using barycentric interpolation.
CUBIC_SPLINE	Cubic spline data interpolator.
HERMITE_SPLINE	Piecewise-cubic interpolator matching values and first derivatives.
INTERP1D	Interpolate a 1-D function.
KROGH_INTERPOLATE	Krogh polynomial interpolation.
PCHIP_INTERPOLATE	PCHIP 1-D monotonic cubic interpolation.