Curve Fitting Toolbox: Modeling guide

Complete worksheet schemas for controls, observation data, parameters, box bounds, weighting, and custom model definitions.

← Curve Fitting Toolbox overview · Getting started · Methods guide · Examples · Reference

Modeling architecture

The Curve Fitting Toolbox separates experimental observations from model definitions and solver parameters. This decoupling allows you to maintain multiple datasets and model families within a single workbook without duplicating sheets or formulas.

The durable data model is structured across three primary input sheets: 1. Start (Start!A5:B10 and Start!D5:H20): Fitting controls and the curated model catalog. 2. Data (Data!A5:E200): Raw experimental \((X, Y)\) observations with individual weights and measurement standard deviations. 3. Parameters (Parameters!A5:G50): Initial parameter estimates, lower and upper box bounds, parameter lock flags, and descriptive labels.

Input schemas

1. Fitting controls (Start!A5:B10)

The controls table configures solver behavior for the active curve fit:

Cell Control Type Valid options / Range Default Functional behavior
B6 Active model Dropdown Defined in Model Catalog michaelis_menten Filters rows on Data and Parameters by matching the Model identifier.
B7 Loss function Dropdown Linear, Huber, Soft L1, Cauchy Linear Selects the optimization objective \(\rho(z)\) used by the Trust Region Reflective algorithm.
B8 Confidence level Dropdown 0.90, 0.95, 0.99 0.95 Sets the two-tailed Student-t critical value \(t_{\alpha/2, \nu}\) for parameter CIs and curve bands.
B9 Eval grid points Integer \(10 \le N \le 50\) 50 Number of uniformly spaced points evaluated across \([X_{\min}, X_{\max}]\) on the Fitted Curve sheet.
B10 Robust outliers Dropdown Auto, Disabled, Strict Auto Controls threshold for outlier flagging: Auto (\(> 2.5\sigma\)), Strict (\(> 2.0\sigma\)), or Disabled.

2. Model catalog (Start!D5:H20)

The catalog registers all available equation formulations. Up to 15 model families can be cataloged in the pre-styled range D5:H20:

Column Header Type Description
D Model Text Unique programmatic identifier (snake_case) matching entries in Data and Parameters.
E Label Text Human-readable title displayed on Results and chart headers.
F Family Text Mathematical family classification (e.g. Saturation, Decay, Polynomial, Sigmoidal, Peak, Power).
G Formula Text Mathematical equation syntax in standard mathematical notation.
H Description Text Scientific or engineering context explaining typical applications.

Pre-configured catalog entries:

  1. michaelis_menten: Enzyme substrate saturation kinetics, \(v = \frac{V_{\max} x}{K_m + x}\).
  2. exponential_decay: First-order clearance and radioactive half-life, \(y = y_0 e^{-k x}\).
  3. polynomial_3: Cubic polynomial thermal expansion, \(y = a x^3 + b x^2 + c x + d\).
  4. gaussian_peak: Spectroscopic absorption and chromatography peak profiling, \(y = \text{amp} \cdot e^{-\frac{1}{2} \left(\frac{x - \text{mean}}{\text{std}}\right)^2}\).
  5. logistic_4p: 4-parameter logistic sigmoidal dose-response immunoassay, \(y = \text{bottom} + \frac{\text{top} - \text{bottom}}{1 + (\text{ec50} / x)^{\text{hill}}}\).
  6. power_law: Empirical allometric scaling and fluid drag, \(y = a \cdot x^b\).

3. Observation data table (Data!A5:E200)

The Data sheet stores measured coordinate pairs across all models in a unified table spanning rows 5 to 200:

Column Header Type Required Description
A Model Text Yes Model ID matching Start!D5:D20. Used to partition data by model.
B X Numeric Yes Independent variable (e.g. concentration, time, temperature, wavelength).
C Y Numeric Yes Dependent measured response (e.g. reaction velocity, activity, absorbance, signal).
D Weight Numeric No (Default: 1.0) Relative statistical weight \(w_i \ge 0\). Points with higher weight exert proportional influence.
E Sigma Numeric No (Default: 1.0) Known instrumental measurement uncertainty \(\sigma_i > 0\) in units of \(Y\).

Data handling and filtering rules:

  • Pairwise Coercion: Values in columns \(X\) and \(Y\) are automatically coerced to numeric floats via pd.to_numeric(..., errors="coerce"). Any row containing non-numeric, blank, or #N/A values in \(X\) or \(Y\) is safely dropped.
  • Effective Sigma Weighting: The solver combines relative observation weight \(w_i\) and measurement uncertainty \(\sigma_i\) into an effective error scale: \[\sigma_{\text{eff}, i} = \frac{\sigma_i}{\sqrt{w_i}}\] Non-positive weights or sigmas (\(\le 0\)) are clamped to \(10^{-6}\) to prevent division by zero or negative variances.
  • Minimum Observations: The active model requires at least 3 valid coordinate pairs and must strictly exceed the number of free parameters (\(n \ge p_{\text{free}} + 1\)).

4. Parameters and bounds table (Parameters!A5:G50)

The Parameters sheet defines the optimization landscape for each model across rows 5 to 50:

Column Header Type Description
A Model Text Model ID matching Start!D5:D20.
B Parameter Text Parameter symbol (e.g. Vmax, Km, y0, k, ec50).
C InitialValue Numeric Starting estimate (\(p_0\)) supplied to the iterative nonlinear solver.
D LowerBound Numeric Strict lower box constraint (\(l_j\)). Parameter cannot fall below this value.
E UpperBound Numeric Strict upper box constraint (\(u_j\)). Parameter cannot exceed this value.
F Fixed Boolean If TRUE, locks the parameter at InitialValue. If FALSE, parameter is freely fitted.
G Description Text Scientific explanation of the physical parameter and its units.

Configuring optimization settings

1. Choosing realistic initial guesses

Nonlinear least squares algorithms are iterative local optimizers. Supplying unreasonable starting estimates can lead to local minima, slow convergence, or boundary hits. Follow these practical heuristics: - Asymptotes (\(V_{\max}, \text{top}\)): Set initial guess close to the maximum observed \(Y\) value (\(\max(Y)\)). - Baselines (\(y_0, \text{bottom}\)): Set initial guess near the minimum observed \(Y\) value (\(\min(Y)\)) or \(Y(X \approx 0)\). - Midpoints (\(K_m, \text{ec50}\)): Estimate the \(X\) value at which \(Y\) reaches approximately half of its dynamic range: \(X\) where \(Y \approx \frac{Y_{\max} + Y_{\min}}{2}\). - Decay or rate constants (\(k\)): Estimate from the approximate half-life: \(k \approx \frac{\ln 2}{t_{1/2}}\).

2. Setting box bounds

Box constraints \([l_j, u_j]\) prevent the optimizer from venturing into physically impossible or numerically unstable regions: - Set lower bounds to small positive values (e.g., \(l_j = 10^{-4}\) or \(0.01\)) for parameters that must remain strictly positive (e.g., rate constants, standard deviations, half-saturation constants). - Prevent divide-by-zero or imaginary numbers in power or logarithmic terms. - Keep bounds wide enough to avoid false convergence: if the optimal value is trapped against a boundary, the Diagnostics sheet will flag a WARN in the Convergence check.

3. Parameter locking mechanics

When a physical constant or instrumental baseline is known independently, setting Fixed = TRUE provides substantial advantages: 1. Reduces Parameter Dimensionality: The locked parameter is treated as a constant scalar during optimization, allowing smaller datasets to be fitted reliably without overparameterization. 2. Preserves Degrees of Freedom: Because the locked parameter is not estimated from the data, it does not consume a degree of freedom. Residual degrees of freedom are calculated as: \[\nu = n - p_{\text{free}}\] where \(p_{\text{free}} = n_{\text{params}} - n_{\text{fixed}}\). 3. Exact Zero Variance: The published parameter table reports StdError = 0.0, tStat = 0.0, and pValue = 0.0, and the confidence interval collapses exactly to [InitialValue, InitialValue].

Adding a custom model equation

You can extend the toolbox with proprietary or domain-specific equations in 5 simple steps.

Step 1: Add the model to the Catalog (Start)

In the next available row of Start!D5:H20 (e.g., row 11), enter: - Model: hill_inhibition - Label: Enzyme Inhibition — Hill Equation - Family: Inhibition - Formula: v0 / (1 + (x / ic50)**n) - Description: Dose-dependent enzyme inhibition curve with Hill cooperativity coefficient.

Step 2: Define parameters on Parameters

On the Parameters sheet, add rows for each parameter: - hill_inhibition, v0, 100.0, 0.0, 500.0, FALSE, Uninhibited baseline reaction rate - hill_inhibition, ic50, 5.0, 0.001, 100.0, FALSE, Half-maximal inhibitory concentration - hill_inhibition, n, 1.0, 0.1, 5.0, FALSE, Hill cooperativity coefficient

Step 3: Add observations on Data

On the Data sheet, enter experimental coordinate pairs tagging each row with hill_inhibition in column A:

hill_inhibition, 0.0, 100.2, 1.0, 1.0
hill_inhibition, 1.0,  83.5, 1.0, 1.0
hill_inhibition, 3.0,  62.1, 1.0, 1.0
hill_inhibition, 5.0,  50.4, 1.0, 1.0
hill_inhibition, 10.0, 33.2, 1.0, 1.0
hill_inhibition, 20.0, 20.1, 1.0, 1.0

Step 4: Register the Python function in notebook.py

In the Python for Excel notebook, define the mathematical function and add it to func_lookup:

def hill_inhibition_func(x, v0, ic50, n):
    safe_x = np.maximum(np.asarray(x, dtype=float), 1e-9)
    safe_ic50 = np.maximum(float(ic50), 1e-9)
    return v0 / (1.0 + (safe_x / safe_ic50)**n)

func_lookup = {
    "michaelis_menten": mm_func,
    "exponential_decay": exp_func,
    "polynomial_3": poly3_func,
    "gaussian_peak": gauss_func,
    "logistic_4p": log4p_func,
    "power_law": power_func,
    "hill_inhibition": hill_inhibition_func,  # New model mapping
}

Step 5: Run and verify

  1. On Start, select hill_inhibition in cell B6.
  2. Inspect the Results, Fitted Curve, and Diagnostics sheets.
  3. Verify that all 7 diagnostic checks report PASS.