Curve Fitting Toolbox

Nonlinear, parametric, polynomial, and sigmoidal curve fitting workbench for Python for Excel

Fit parametric, nonlinear, and polynomial curves to spreadsheet observations, evaluate goodness-of-fit statistics, compute parameter confidence intervals, and generate dense evaluation grids.

Result preview

The Curve Fitting Toolbox is an interactive curve-fitting and parameter-estimation workbench for Excel. It fits biochemical, pharmacokinetic, physical, and empirical curves to measured x/y data without leaving the spreadsheet.

The default Enzyme Kinetics — Michaelis-Menten model fits substrate saturation data, yielding an estimated maximum velocity Vmax ≈ 91.93 and half-saturation constant Km ≈ 3.38, with R² > 0.998 and SSE ≈ 6.41. Switching Start → Active model instantly recalculates first-order exponential decay, cubic polynomials, Gaussian spectroscopic peaks, 4-parameter logistic immunoassays, or allometric power laws.

For a first run:

1. Leave Start → Active model as michaelis_menten.
2. Inspect the measured concentration and velocity pairs on Data.
3. View Results for converged parameter estimates, standard errors, and confidence intervals.
4. Switch Active model to exponential_decay to fit radioactive half-life or drug clearance.

For a guided tour, see Getting started.

What this toolbox covers

Modules

1. Parametric Model Library — Curated equations spanning saturation, exponential decay, polynomials, Gaussian peaks, 4PL sigmoids, and power laws.
2. Robust Solvers — Levenberg-Marquardt and Trust Region Reflective nonlinear least squares supporting bounded parameters, parameter locking, and robust loss functions.
3. Statistical Inference — Asymptotic covariance matrices, standard errors, Student-t statistics, p-values, and 95% parameter confidence intervals.
4. Goodness of Fit — SSE, RMSE, R-squared, Adjusted R-squared, AIC, and BIC metrics.
5. High-Resolution Evaluation — Dense x/y evaluation grids with pointwise 95% confidence bands and analytical derivatives ready for Excel charts.
6. 7-Check Diagnostics Suite — Model validation, boundary hit detection, goodness of fit, Jacobian conditioning, Shapiro-Wilk normality, Durbin-Watson autocorrelation, and standardized outlier detection.

Documentation

• Getting started — First fit, switching models, locking parameters, adjusting loss functions, and interpreting diagnostic outputs.
• Modeling guide — Data table layout, observation weights and sigmas, parameter bounds, parameter locking, and custom model creation.
• Methods guide — Optimization algorithms, robust loss functions, covariance calculation, confidence band projection, and MATLAB mapping.
• Worked examples — Comprehensive walkthroughs for all six included models across engineering, biology, pharmacology, and physics.
• Reference — Worksheet controls, input/output schemas, diagnostic thresholds, tolerances, and troubleshooting.

Why Python

Excel is the natural home for experimental and business observations: data can be pasted, organized, and shared directly in workbooks. Python provides the numerical engine needed to solve bounded nonlinear least squares, compute parameter covariance matrices, invert Hessians, and evaluate dense confidence bands without fragile third-party macros.

Operating workflow

Author: A data scientist, biostatistician, or quantitative researcher defines custom equations, adjusts parameter bounds, and validates baseline fits.

Workbook user: A laboratory technician, engineer, or analyst pastes new observations into Data, selects the active equation on Start, and reviews fitted parameters and diagnostic checks on Results.

Download the Excel template

• Curve Fitting Toolbox workbook
• Getting started
• Complete reference

Capability reference

Capability map

Capability	Status	Current implementation
Nonlinear least squares	Available	SciPy curve_fit with Trust Region Reflective (TRF) algorithm
Bounded parameters	Available	Explicit lower and upper box constraints for all model parameters
Parameter locking	Available	Lock parameters via Fixed = TRUE with exact degrees-of-freedom tracking and zero standard error
Observation weighting & uncertainty	Available	Relative weights \(w\) and measurement sigmas \(\sigma\) scaled via effective error \(\sigma_{\text{eff}} = \sigma / \sqrt{w}\)
Robust loss functions	Available	Linear, Huber, Soft L1, and Cauchy robust loss functions to suppress experimental outliers
Asymptotic covariance & inference	Available	Scaled Jacobian parameter covariance matrix with standard errors, Student-t statistics, p-values, and confidence intervals
Goodness-of-fit metrics	Available	SSE, RMSE, R², Adjusted R², AIC, BIC, and residual degrees of freedom
Dense curve evaluation	Available	High-resolution evaluation grid with pointwise 95% confidence bands and derivatives
Pointwise confidence bands	Available	Delta-method projection via \(\text{SE}(\hat{y}) = \sqrt{\nabla f(x)^T \mathbf{C} \nabla f(x)}\) and shaded task-pane plot
Finite difference derivatives	Available	Forward/backward boundary and central interior finite difference approximations
Automated Diagnostics Suite	Available	7-check suite: Model validation, Convergence, Goodness of fit, Jacobian conditioning, Shapiro-Wilk normality, Durbin-Watson autocorrelation, Outlier detection
Spline interpolation	Planned	Cubic smoothing spline fitting via scipy.interpolate
Surface fitting (\(z = f(x, y)\))	Exploring	2D bivariate surface fitting
Custom AST expressions	Planned	Arbitrary user-defined formulas parsed via Python abstract syntax tree

Inputs and assumptions

Workbook map

Sheet	Purpose	Primary user
Start	Active model selector, loss function, confidence level, outlier filter, and model catalog	Author and workbook user
Data	Measured observation table (Model, X, Y, Weight, Sigma)	Workbook user
Parameters	Initial estimates, lower bounds, upper bounds, parameter locks, and descriptions	Author
Results	Summary fit metrics and parameter estimation table	Workbook user and reviewer
Fitted Curve	Dense evaluation grid, confidence intervals, and derivatives	Workbook user
Diagnostics	7-check statistical suite covering convergence, residual structure, and outlier detection	Reviewer

Notebook implementation

inputs = bf.inputs(
    controls=bf.ref("Start!A5:B10", headers=True),
    models=bf.ref("Start!D5:H20", headers=True),
    data=bf.ref("Data!A5:E200", headers=True),
    parameters=bf.ref("Parameters!A5:G50", headers=True),
)

The notebook extracts observations and parameters for the active model, executes scipy.optimize.curve_fit with parameter locking and effective sigma weighting, derives statistical confidence intervals, evaluates dense confidence bands, executes the 7-check diagnostics suite, and publishes structured outputs:

bf.publish(outputs={
    "summary": df_summary_output,
    "parameters": df_parameters_output,
    "curve": df_curve_output,
    "diagnostics": df_diagnostics_output,
})

How the calculation works

1. Ingestion & Filtering: Matches active model ID against rows on Data and Parameters, applying pairwise coercion to valid numeric \((x, y)\) pairs and weighting by \(\sigma_{\text{eff}} = \sigma / \sqrt{w}\).
2. Parameter Partitioning: Separates free parameters from locked parameters (Fixed = TRUE), reducing optimization dimensionality and maintaining exact residual degrees of freedom.
3. Optimization: Calls curve_fit using the Trust Region Reflective (TRF) algorithm, respecting box bounds and the user-selected loss function (Linear, Huber, Soft L1, Cauchy).
4. Inference & Covariance: Extracts parameter covariance matrix \(\mathbf{C} = (\mathbf{J}^T \mathbf{W} \mathbf{J})^{-1} s^2\), derives standard errors, Student-t statistics, p-values, and confidence intervals.
5. Evaluation Grid & Confidence Bands: Evaluates the fitted curve across a dense grid, computing gradient vectors via finite differences and projecting pointwise confidence bands \(\hat{y} \pm t_{\alpha/2, \nu} \text{SE}(\hat{y})\).
6. Diagnostics Suite: Executes 7 automated diagnostic checks evaluating degrees of freedom, parameter boundary proximity, \(R^2\), scaled Jacobian condition number \(\kappa\), Shapiro-Wilk normality, Durbin-Watson autocorrelation, and standardized residuals.

Validation and expected results

Fixture	Model ID	Family	Expected evidence
Enzyme Kinetics	michaelis_menten	Saturation	OPTIMAL; Vmax ≈ 91.93; Km ≈ 3.38; R² > 0.998; SSE ≈ 6.41
Radioactive Decay	exponential_decay	Decay	OPTIMAL; y0 ≈ 99.95; k ≈ 0.5000; R² > 0.999; SSE ≈ 0.0294
Thermal Expansion	polynomial_3	Polynomial	OPTIMAL; a ≈ 1.91; b ≈ 0.96; c ≈ 1.29; d ≈ 3.06; R² > 0.999; SSE ≈ 0.1587
Spectroscopic Absorption	gaussian_peak	Peak	OPTIMAL; amp ≈ 97.63; mean ≈ 4.01; std ≈ 0.72; R² > 0.987; SSE ≈ 89.29
Bioassay Dose-Response	logistic_4p	Sigmoidal	OPTIMAL; bottom ≈ 5.83; top ≈ 96.56; ec50 ≈ 1.50; hill ≈ 2.60; R² > 0.999; SSE ≈ 6.05
Allometric Scaling	power_law	Power	OPTIMAL; a ≈ 2.50; b ≈ 1.50; R² ≈ 1.000; SSE ≈ 0.0008

Limitations

• Dataset size: Designed for decision-scale datasets up to 1,000 observations per model.
• Parameter count: Models with up to 10 parameters are supported by default layouts.
• Dimensionality: Focuses on 1D curves (\(y = f(x)\)); multidimensional surface fitting is planned.
• Dynamic models: Models are pre-registered in the catalog and Python dispatch map; custom formulas require declaring the equation in notebook.py.

When to use this approach

Use this toolbox when experimental or commercial measurements live in Excel and require rigorous nonlinear parameter estimation, confidence intervals, and diagnostic verification without external statistical software.

Related templates

• Nonlinear Curve Fitting — Focused single-equation enzyme kinetics example.
• Optimization Toolbox — General mathematical programming workbench.

How this compares with MATLAB and Excel

Dimension	Native Excel	MATLAB Curve Fitting Toolbox	Boardflare Curve Fitting Toolbox
Nonlinear Fitting	Solver add-in only; no standard errors	fit, cfit, full GUI app	Automated bounded Levenberg-Marquardt / TRF
Statistical Errors	None without complex manual formulas	Full asymptotic and bootstrap errors	Parameter SEs, t-stats, p-values, 95% CIs
Confidence Bands	Manual delta-method matrix formulas	Pointwise and simultaneous bands via predint	Automated pointwise 95% Jacobian projection bands
Robust Losses	None (least squares only)	Bisquare (Tukey), LAR (L1)	Huber, Soft L1, Cauchy, and Linear
Parameter Locking	Manual cell formula replacement	Fixed parameters in fitoptions	Fixed = TRUE column with exact DoF tracking
Residual Diagnostics	Manual calculation	Residual plots and statistical tests	7-check automated suite with PASS/WARN/FAIL badges
Integration	Worksheet-native formulas	External desktop application	Interactive Python running directly inside Excel

Changelog

2026-10-18 (Issue #93)

• Expanded summary output block and spillRef geometry to row 17 to accommodate Residual DoF.
• Fixed 4-Parameter Logistic formula to correctly fit increasing sigmoidal dose-response bioassays.
• Implemented parameter locking via the Parameters Fixed column with exact degrees-of-freedom tracking and zero standard error.
• Added pairwise observation filtering and relative weight/sigma scaling into nonlinear least squares.
• Added pointwise 95% confidence bands via asymptotic parameter covariance Jacobian projection and shaded task-pane plot.
• Implemented 7-check automated Diagnostic Suite covering model validation, boundary hits, goodness-of-fit, Jacobian conditioning, Shapiro-Wilk residual normality, Durbin-Watson autocorrelation, and standardized outlier detection.
• Expanded verification scenarios across all 6 models, robust losses, parameter locking, weighting, and diagnostic controls.
• Published comprehensive Toolbox documentation bundle across getting-started, modeling, methods, examples, and reference guides.