Solver for Excel

This add-in provides advanced solver functions for Python, including optimization algorithms, calculus-based ODE solvers, numerical differentiation, and integration methods. These functions leverage the widely used and powerful SciPy Python library. Since the functions are Python, they can be run using either Excel's integrated =PY() function or simply as a custom function which uses Pyodide locally in your browser. Either way you have full access to the Python source code for review and customization. More details are available in the Solver for Excel add-in documentation.

Features

🆓 Unlimited FREE use

🌐 Works in Excel for web and desktop

✅ Use Excel =PY() or local Python runtime

🔒 No data is shared outside Excel

🔍 Python source code available for review

📐 20+ optimization and calculus algorithms

Functions

The following solver functions are included:

Local Optimization

• MINIMIZE: Multivariate minimization with gradient-based and derivative-free methods.
• MINIMIZE_SCALAR: Single-variable minimization using Brent, golden-section, or bounded methods.

Curve Fitting

• CURVE_FIT: Fit nonlinear models to data using least-squares optimization.

Assignment Problems

• LINEAR_ASSIGNMENT: Solve linear assignment problems using the Hungarian algorithm.
• QUADRATIC_ASSIGNMENT: Solve quadratic assignment problems with interaction costs.

Global Optimization

• BASIN_HOPPING: Global minimization using basin-hopping with local search.
• DIFFERENTIAL_EVOLUTION: Population-based evolutionary global optimization.
• SHGO: Simplicial Homology Global Optimization for systematic global search.

Linear & Mixed-Integer Programming

• LINEAR_PROG: Solve linear programs with multiple solver backends.
• MILP: Solve mixed-integer linear programs using the HiGHS solver.

Root Finding

• ROOT: Solve nonlinear systems of equations with robust methods.
• ROOT_SCALAR: Scalar root-finding using Brent, Newton, secant, or other methods.
• FIXED_POINT: Find fixed points for scalar functions.

Calculus

• SOLVE_IVP: Solve initial value problems for systems of ordinary differential equations.
• JACOBIAN: Compute Jacobian matrices for multivariate functions.
• HESSIAN: Compute Hessian matrices for sensitivity and curvature analysis.
• SENSITIVITY: Parameter sensitivity analysis using automatic differentiation.
• QUAD: 1D numerical integration using adaptive quadrature.
• DBLQUAD: 2D numerical integration over rectangular regions.
• TRAPEZOID: Numerical integration using the trapezoidal rule for sampled data.

Background

Solvers address two fundamental mathematical challenges: **optimization** (finding the best solution) and **calculus** (computing derivatives, integrals, and solving differential equations).

Optimization finds the best solution from feasible alternatives. Linear Programming (LP) solves linear problems with guaranteed optimality. Mixed-Integer Programming (MILP) handles discrete decisions. Nonlinear Programming (NLP) solves curved problems. Global optimization finds the absolute best solution; Local optimization finds nearby minima efficiently. Least-squares fits models to data. Root-finding solves nonlinear equations.

Calculus functions compute derivatives (Jacobians and Hessians for sensitivity analysis), numerical integration (1D and 2D), and solve ordinary differential equations (ODEs). ODE solvers simulate dynamic systems including population models (SEIR, SIR), chaotic dynamics (Lorenz), biological systems (Hodgkin-Huxley, FitzHugh-Nagumo), chemical reactions (Brusselator), and enzyme kinetics (Michaelis-Menten).

For more details, see the detailed documentation.

Use Cases

• Operations & Supply Chain: Route optimization minimizes transportation costs and delivery times. Production planning optimizes resource allocation and inventory levels. Workforce scheduling balances labor costs with service levels. Facility location selects optimal warehouse and distribution center locations. Vehicle routing determines efficient delivery sequences for fleets. Assignment problems solve crew scheduling and project resource allocation.
• Finance & Economics: Portfolio optimization balances risk and return by selecting optimal asset allocations. Pricing strategies determine revenue-maximizing prices under demand constraints. Capital budgeting allocates investment across competing projects. Asset-liability management matches financial obligations with available assets. Hedging strategies optimize financial instruments to reduce risk. Trend analysis and forecasting use curve fitting to identify market patterns.
• Engineering & Design: Design optimization tunes parameters for structural strength, efficiency, or cost. Control system tuning optimizes PID controllers and feedback loops. Aerodynamic design optimizes airfoil shapes for lift and drag. Mechanical design optimizes component dimensions for weight and durability. Power systems optimization balances generation, transmission, and demand. Van der Pol oscillators model circuit behavior and mechanical vibrations.
• Life Sciences & Medicine: Epidemiology uses SIR and SEIR models to forecast disease spread and evaluate intervention strategies. Pharmacokinetics models drug absorption, distribution, and elimination across body compartments. Enzyme kinetics characterizes reaction rates and substrate specificity using Michaelis-Menten kinetics. Neuroscience simulates neuronal dynamics with Hodgkin-Huxley and FitzHugh-Nagumo models to understand action potentials. Population dynamics models predator-prey relationships with Lotka-Volterra equations.
• Chemistry & Chemical Engineering: Reaction optimization tunes temperature, pressure, and catalyst to maximize product yield. Chemical equilibrium modeling predicts concentrations of reactants and products. Autocatalytic reactions are simulated using Brusselator models. Reaction pathway optimization identifies efficient synthetic routes. Process design optimizes separation, mixing, and heat transfer unit operations.
• Business Analytics & Optimization: Resource allocation maximizes productivity subject to budget and availability constraints. Scheduling problems optimize employee shifts, project timelines, and equipment utilization. Network design optimizes communication and transportation network topology. Inventory optimization balances holding costs against stockout risks. Revenue management optimizes pricing and capacity allocation to maximize profit.