Math
Overview
Mathematical computation forms the backbone of quantitative analysis in science, engineering, finance, and data science. This comprehensive library of mathematical functions extends Excel’s capabilities far beyond native formulas, providing access to sophisticated numerical methods implemented in SciPy, CasADi, and specialized Python libraries.
Modern mathematical computing requires more than arithmetic—it demands robust algorithms for solving differential equations, fitting complex models to data, optimizing multivariate functions, and performing matrix operations with numerical stability. These functions bridge the gap between Excel’s spreadsheet interface and the power of production-grade scientific computing libraries.
Calculus
Calculus provides the mathematical framework for understanding rates of change (differentiation) and accumulation (integration). In practical applications, calculus enables sensitivity analysis, area/volume calculations, and the modeling of dynamic systems through differential equations.
Differentiation computes derivatives that describe how functions change. The JACOBIAN function calculates first-order partial derivatives for multivariable systems, while HESSIAN computes second derivatives essential for optimization and stability analysis. The SENSITIVITY function quantifies how model outputs respond to parameter variations—critical for uncertainty quantification and robust design.
Integration accumulates quantities over intervals or regions. QUAD performs adaptive quadrature for smooth one-dimensional integrals, DBLQUAD extends this to two-dimensional regions, and TRAPEZOID efficiently integrates tabulated data using the trapezoidal rule.
Ordinary Differential Equations (ODEs) model systems that evolve over time. The general-purpose SOLVE_IVP function solves initial value problems, while SOLVE_BVP handles boundary value problems. Pre-built models include classic systems: LOTKA_VOLTERRA for predator-prey dynamics, SIR and SEIR for epidemiology, LORENZ for chaos theory, MICHAELIS_MENTEN for enzyme kinetics, and HODGKIN_HUXLEY for neuroscience.
Curve Fitting
Curve fitting finds mathematical functions that best describe observed data. Unlike simple regression, these methods handle nonlinear models, multiple parameters, and domain-specific functional forms.
Least-Squares Fitting minimizes the sum of squared residuals between model predictions and data. CURVE_FIT uses scipy.optimize.curve_fit for general nonlinear models. CA_CURVE_FIT leverages CasADi for automatic differentiation—particularly powerful for complex symbolic expressions. LM_FIT uses the lmfit library for advanced features like parameter bounds and model composition. MINUIT_FIT employs iMinuit, trusted in particle physics for robust uncertainty estimation.
Domain-Specific Models provide ready-to-use functional forms for common scientific and engineering applications: - Biological Sciences: DOSE_RESPONSE, ENZYME_BASIC, ENZYME_INHIBIT, BINDING_MODEL - Chemical Engineering: ADSORPTION, CHROMA_PEAKS, SPECTRO_PEAKS - Growth Models: EXP_GROWTH, EXP_DECAY, GROWTH_SIGMOID, GROWTH_POWER - Advanced Distributions: STAT_DISTRIB, STAT_PARETO - Peak Analysis: PEAK_ASYM, WAVEFORM
Interpolation
Interpolation estimates values between known data points, enabling smooth function reconstruction from discrete measurements.
Univariate Interpolation operates on single-variable data. INTERP1D provides linear, cubic, and other interpolation schemes. PCHIP_INTERPOLATE offers monotonic cubic interpolation that preserves data trends without overshooting. AKIMA_INTERP minimizes oscillations in non-uniform data. Polynomial methods include KROGH_INTERPOLATE, BARYCENTRIC_INTERP, and LAGRANGE_INTERP.
Splines use piecewise polynomials for smooth, flexible curve fitting. CUBIC_SPLINE provides standard cubic interpolation, while UNIVARIATE_SPLINE and SMOOTH_SPLINE add smoothing for noisy data. MAKE_INTERP_SPLINE and MAKE_LSQ_SPLINE offer B-spline construction. HERMITE_SPLINE matches both values and derivatives.
Multivariate Interpolation extends to higher dimensions. GRIDDATA and RBF_INTERPOLATOR handle scattered data in any dimension. For regularly-spaced grids, use INTERPN, GRID_INTERP, LINEAR_ND_INTERP, or NEAREST_ND_INTERP.
Linear Algebra
Linear algebra operations on matrices and vectors are fundamental to scientific computing, machine learning, and engineering analysis.
Matrix Decompositions factor matrices into simpler forms for numerical stability and insight. SVD computes the Singular Value Decomposition, revealing matrix rank and principal directions. QR produces QR decomposition for solving linear systems and orthogonalization. CHOLESKY efficiently factors positive-definite matrices common in covariance matrices and optimization.
Matrix Functions and Solvers provide specialized operations. EXPM computes the matrix exponential used in solving linear differential equations. PINV calculates the Moore-Penrose pseudoinverse for handling singular or rectangular matrices.
Least-Squares Systems solve overdetermined linear problems. LSTSQ handles unconstrained problems, while LSQ_LINEAR adds bound constraints—essential for non-negative least squares and other constrained regression tasks.
Optimization
Mathematical optimization finds the best solution from a set of feasible alternatives. Every optimization problem has an objective function to minimize or maximize, decision variables to adjust, and constraints that define feasibility.
Local Optimization seeks nearby minima using gradient information. MINIMIZE provides access to powerful methods like BFGS, L-BFGS-B, and Newton-CG. MINIMIZE_SCALAR efficiently handles single-variable problems. CA_MINIMIZE uses CasADi’s automatic differentiation for complex symbolic objectives.
Global Optimization finds the absolute minimum across the entire search space. DIFF_EVOLUTION uses a population-based stochastic algorithm robust to local minima. DUAL_ANNEALING combines simulated annealing with local search. BASIN_HOPPING escapes local minima through random perturbations. SHGO provides theoretical guarantees for Lipschitz-continuous functions. BRUTE performs exhaustive grid search.
Linear and Quadratic Programming handle problems with linear objectives and constraints. LINEAR_PROG solves linear programming using the Simplex or Interior-Point method. MILP extends to mixed-integer linear programming for discrete decision problems. CA_QUAD_PROG solves quadratic programs using CasADi.
Root Finding solves equations where f(x) = 0. ROOT_SCALAR handles single-variable equations using methods like Brent’s algorithm and Newton-Raphson. ROOT extends to systems of nonlinear equations. CA_ROOT leverages automatic Jacobian calculation. FIXED_POINT finds points where f(x) = x.
Assignment Problems optimize one-to-one mappings. LINEAR_ASSIGNMENT solves the classic assignment problem using the Hungarian algorithm. QUAD_ASSIGN handles the more complex quadratic assignment problem where costs depend on pairwise interactions.
Native Excel capabilities
Excel provides several built-in mathematical capabilities, though with significant limitations:
Basic Functions: Excel’s formula library includes elementary math (
SUM,PRODUCT), trigonometry (SIN,COS), logarithms (LOG,LN), and matrix operations (MMULT,MINVERSE). These work well for simple calculations but lack advanced numerical methods.Analysis ToolPak: Provides basic statistical tools including regression (linear least-squares only), moving averages, and exponential smoothing. It cannot handle nonlinear curve fitting or custom models.
Solver Add-in: Excel’s optimization tool supports linear programming, nonlinear optimization, and integer programming. However, it’s limited to approximately 200 decision variables, lacks access to modern algorithms (like differential evolution), and provides no automatic differentiation. Performance degrades significantly with problem size.
Goal Seek: A simplified single-variable root finder. Useful for quick “what-if” analysis but limited to one equation in one unknown.
Interpolation: Excel offers
FORECAST.LINEARandTRENDfor linear interpolation/extrapolation, but lacks splines, multivariate interpolation, and sophisticated methods like PCHIP or RBF.Numerical Integration/Differentiation: Not natively supported. Users must manually implement trapezoidal or Simpson’s rule using formulas, which is error-prone and inefficient.
Python functions provide industrial-grade numerical algorithms (from SciPy, CasADi, NumPy), automatic differentiation, handle thousands of variables, support complex constraints, and deliver superior numerical stability and performance.
Third-party Excel add-ins
Frontline Solvers (Analytic Solver): Premium optimization suite from the original Excel Solver developers. Offers large-scale LP/MIP solvers, nonlinear optimization engines, and stochastic programming. Subscription-based with tiered pricing.
OpenSolver: Free, open-source alternative that connects Excel to powerful COIN-OR solvers (CBC for linear/integer programming, Bonmin for nonlinear). Removes the 200-variable limit of standard Solver and supports linear models with thousands of variables.
XLSTAT: Comprehensive statistical and data analysis suite with advanced regression, multivariate analysis, and some optimization capabilities. Strong curve-fitting tools but limited compared to Python’s ecosystem.
XLeratorDB: Focuses on financial mathematics but includes some numerical methods. Primarily for SQL Server integration.
NumXL: Time series analysis and econometrics add-in with interpolation and forecasting capabilities. Does not cover the full breadth of mathematical optimization or differential equations.
Calculus
Differentiation
| Tool | Description |
|---|---|
| HESSIAN | Compute the Hessian matrix (second derivatives) of a scalar function using CasADi symbolic differentiation. |
| JACOBIAN | Calculate the Jacobian matrix of mathematical expressions with respect to specified variables. |
| SENSITIVITY | Compute the sensitivity of a scalar model with respect to its parameters using CasADi. |
Integration
| Tool | Description |
|---|---|
| DBLQUAD | Compute the double integral of a function over a two-dimensional region. |
| QUAD | Numerically integrate a function defined by a table of x, y values over [a, b] using adaptive quadrature. |
| TRAPEZOID | Integrate sampled data using the composite trapezoidal rule. |
Ode Models
| Tool | Description |
|---|---|
| BRUSSELATOR | Numerically solves the Brusselator system of ordinary differential equations for autocatalytic chemical reactions. |
| COMPARTMENTAL_PK | Numerically solves the basic one-compartment pharmacokinetics ODE using scipy.integrate.solve_ivp. |
| FITZHUGH_NAGUMO | Numerically solves the FitzHugh-Nagumo system of ordinary differential equations for neuron action potentials using scipy.integrate.solve_ivp. |
| HODGKIN_HUXLEY | Numerically solves the Hodgkin-Huxley system of ordinary differential equations for neuron action potentials. |
| LORENZ | Numerically solves the Lorenz system of ordinary differential equations for chaotic dynamics. |
| LOTKA_VOLTERRA | Numerically solves the Lotka-Volterra predator-prey system of ordinary differential equations. |
| MICHAELIS_MENTEN | Numerically solves the Michaelis-Menten system of ordinary differential equations for enzyme kinetics using scipy.integrate.solve_ivp. |
| SEIR | Numerically solves the SEIR system of ordinary differential equations for infectious disease modeling using scipy.integrate.solve_ivp. |
| SIR | Solves the SIR system of ordinary differential equations for infection dynamics using scipy.integrate.solve_ivp (see scipy.integrate.solve_ivp). |
| VAN_DER_POL | Numerically solves the Van der Pol oscillator system of ordinary differential equations. |
Ode Systems
| Tool | Description |
|---|---|
| SOLVE_BVP | Solve a boundary value problem for a second-order system of ODEs. |
| SOLVE_IVP | Solve an initial value problem for a system of ODEs of the form dy/dt = A @ y. |
Curve Fitting
Least Squares
| Tool | Description |
|---|---|
| CA_CURVE_FIT | Fit an arbitrary symbolic model to data using CasADi and automatic differentiation. |
| CURVE_FIT | Fit a model expression to xdata, ydata using scipy.optimize.curve_fit. |
| LM_FIT | Fit data using lmfit’s built-in models with optional model composition. |
| MINUIT_FIT | Fit an arbitrary model expression to data using iminuit least-squares minimization with uncertainty estimates. |
Models
| Tool | Description |
|---|---|
| ADSORPTION | Fits adsorption models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| AGRICULTURE | Fits agriculture models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| BINDING_MODEL | Fits binding_model models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| CHROMA_PEAKS | Fits chroma_peaks models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| DOSE_RESPONSE | Fits dose_response models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| ELECTRO_ION | Fits electro_ion models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| ENZYME_BASIC | Fits enzyme_basic models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| ENZYME_INHIBIT | Fits enzyme_inhibit models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| EXP_ADVANCED | Fits exp_advanced models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| EXP_DECAY | Fits exp_decay models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| EXP_GROWTH | Fits exponential growth models to data using scipy.optimize.curve_fit. |
| GROWTH_POWER | Fits growth_power models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| GROWTH_SIGMOID | Fits growth_sigmoid models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| MISC_PIECEWISE | Fits misc_piecewise models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| PEAK_ASYM | Fits peak_asym models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| POLY_BASIC | Fits poly_basic models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| RHEOLOGY | Fits rheology models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| SPECTRO_PEAKS | Fits spectro_peaks models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| STAT_DISTRIB | Fits stat_distrib models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| STAT_PARETO | Fits stat_pareto models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
| WAVEFORM | Fits waveform models to data using scipy.optimize.curve_fit. See https://docs.scipy.org/doc/scipy/reference/generated/scipy.optimize.curve_fit.html for details. |
Interpolation
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. |
Linear Algebra
| Tool | Description |
|---|---|
| CHOLESKY | Compute the Cholesky decomposition of a real, symmetric positive-definite matrix. |
| EXPM | Compute the matrix exponential of a square matrix using scipy.linalg.expm |
| LSQ_LINEAR | Solve a bounded linear least-squares problem. |
| LSTSQ | Compute the least-squares solution to Ax = B using scipy.linalg.lstsq. |
| PINV | Compute the Moore-Penrose pseudoinverse of a matrix using singular value decomposition (SVD). |
| QR | Compute the QR decomposition of a matrix and return either Q or R. |
| SVD | Compute the Singular Value Decomposition (SVD) of a matrix using scipy.linalg.svd. |
Optimization
Assignment Problems
| Tool | Description |
|---|---|
| LINEAR_ASSIGNMENT | Solve the linear assignment problem using scipy.optimize.linear_sum_assignment. |
| QUAD_ASSIGN | Solve a quadratic assignment problem using SciPy’s implementation. |
Global Optimization
| Tool | Description |
|---|---|
| BASIN_HOPPING | Minimize a single-variable expression with SciPy’s basinhopping algorithm. |
| BRUTE | Perform a brute-force grid search to approximate the global minimum of a function. |
| DIFF_EVOLUTION | Minimize a multivariate function using differential evolution. |
| DUAL_ANNEALING | Minimize a multivariate function using dual annealing. |
| SHGO | Find global minimum using Simplicial Homology Global Optimization. |
Linear Programming
| Tool | Description |
|---|---|
| CA_QUAD_PROG | Solve a quadratic programming problem using CasADi’s qpsol solver. |
| LINEAR_PROG | Solve a linear programming problem using SciPy’s linprog function. |
| MILP | Solve a mixed-integer linear program using scipy.optimize.milp. |
Local Optimization
| Tool | Description |
|---|---|
| CA_MINIMIZE | Minimize a multivariate function using CasADi with automatic differentiation. |
| MINIMIZE | Minimize a multivariate function using SciPy’s minimize routine. |
| MINIMIZE_SCALAR | Minimize a single-variable function using SciPy’s minimize_scalar. |
Root Finding
| Tool | Description |
|---|---|
| CA_ROOT | Solve a system of nonlinear equations using CasADi with automatic Jacobian. |
| FIXED_POINT | Find a fixed point x such that f(x) = x for a scalar function expression. |
| ROOT | Solve a square nonlinear system using SciPy’s root solver. |
| ROOT_SCALAR | Find a real root of a scalar function using SciPy’s root_scalar. |