Calculus

Overview

Calculus is the mathematical study of continuous change, providing the theoretical foundation for modeling dynamic systems across science, engineering, and economics. The two fundamental operations—differentiation (measuring rates of change) and integration (computing accumulated quantities)—enable us to analyze everything from population growth and chemical reactions to planetary motion and market dynamics. When combined with ordinary differential equations (ODEs), calculus becomes a powerful tool for predicting system behavior over time.

In physics and engineering, calculus describes motion, heat transfer, fluid dynamics, and electrical circuits. In biology and medicine, it models population dynamics, epidemics, and pharmacokinetics. In economics and finance, it underpins optimal control theory, option pricing, and growth models. The functions in this category bring these computational capabilities directly into Excel, leveraging Python’s SciPy and CasADi libraries for numerical and symbolic computation.

Differentiation

Differentiation computes the rate at which a function changes. While the first derivative gives the slope (velocity), higher-order derivatives provide deeper insights into system behavior.

• Jacobian matrices capture first-order partial derivatives of vector-valued functions, essential for sensitivity analysis and optimization. The JACOBIAN function uses CasADi symbolic differentiation to compute exact derivatives of complex mathematical expressions.

• Hessian matrices contain second-order partial derivatives, revealing curvature and helping identify optimal points (minima, maxima, saddle points). The HESSIAN function computes these for scalar functions, critical for Newton’s method and uncertainty quantification.

• Sensitivity analysis quantifies how model outputs respond to changes in input parameters. The SENSITIVITY function provides automatic differentiation for parameter estimation and model calibration.

Integration

Numerical integration (quadrature) computes definite integrals when analytical solutions are unavailable or when working with sampled data.

• Adaptive quadrature algorithms automatically refine the grid where the function changes rapidly. The QUAD function implements Gaussian quadrature from SciPy, handling smooth functions with high accuracy.

• Trapezoidal rule is the simplest numerical integration method, approximating the area under a curve as a series of trapezoids. The TRAPEZOID function efficiently integrates discrete sampled data, common in experimental measurements.

• Double integrals extend integration to two dimensions, computing volumes under surfaces. The DBLQUAD function handles two-dimensional regions with adaptive refinement.

Ordinary Differential Equations (ODEs)

Ordinary differential equations describe how systems evolve over time. The general form \frac{dy}{dt} = f(t, y) specifies the rate of change based on the current state. Solving an ODE means finding the function y(t) that satisfies this relationship given initial or boundary conditions.

ODE Systems provides general-purpose solvers:

• Initial value problems (IVPs) specify the system state at a starting time and integrate forward. The SOLVE_IVP function wraps scipy.integrate.solve_ivp, offering multiple algorithms (Runge-Kutta, Adams, BDF) for stiff and non-stiff systems.

• Boundary value problems (BVPs) specify conditions at both ends of the time interval, requiring iterative solution methods. The SOLVE_BVP function implements collocation methods from scipy.integrate.solve_bvp.

ODE Models provides specialized implementations of scientifically important dynamical systems:

• Biology and Epidemiology:
  • SIR and SEIR models track infectious disease spread through Susceptible, Infected, and Recovered populations.
  • Lotka-Volterra models predator-prey dynamics in ecosystems.
  • Michaelis-Menten describes enzyme kinetics in biochemistry.
• Chemistry and Physics:
  • Brusselator models autocatalytic chemical reactions exhibiting oscillations.
  • Lorenz system demonstrates deterministic chaos in atmospheric convection.
  • Van der Pol oscillator models nonlinear damping in electrical circuits.
• Neuroscience and Physiology:
  • FitzHugh-Nagumo simplifies neuron action potential dynamics.
  • Hodgkin-Huxley provides the detailed biophysical model of neuron firing (Nobel Prize 1963).
  • Compartmental PK models drug absorption and elimination in pharmacokinetics.

Native Excel capabilities

Excel provides minimal native calculus functionality:

• Numerical differentiation: No built-in functions exist. Users manually compute finite differences like =(F(x+h) - F(x))/h, which are sensitive to step size choice and numerical errors.

• Numerical integration: The trapezoidal rule can be implemented with formulas, and Simpson’s rule with VBA. However, these lack adaptive refinement and error control.

• Differential equations: Excel’s Solver can perform manual Euler method integration using iterative cell references, but this is cumbersome, unstable for stiff systems, and impractical for anything beyond simple educational examples.

The Python functions in this category provide production-grade implementations with automatic error control, multiple algorithm choices, and symbolic differentiation capabilities unavailable in native Excel.

Third-party Excel add-ins

• xlwings: While primarily a Python-Excel bridge, xlwings enables integration of Python scientific libraries (NumPy, SciPy) into Excel workflows, though it requires local Python installation and manual function wrapping.

• Maple Add-in for Excel: Provides symbolic calculus (analytical derivatives and integrals) and differential equation solving directly in Excel cells. Commercial software with significant licensing costs.

• MATLAB with Excel: MATLAB’s Symbolic Math Toolbox can connect to Excel via COM automation, offering symbolic and numerical calculus. Requires MATLAB license and complex setup.

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.