Curve fitting (any x-y table)

Statistics
Curve Fitting
Scientific Computing
Fit nonlinear and linear models to arbitrary x-y data, evaluate parameter uncertainties and confidence bands, and compare candidate models by AICc.

Overview

Curve fitting (any x-y table) fits candidate mathematical models to two-column experimental or engineering observations ($x$ and $y$). It estimates model parameters with standard errors and two-sided confidence intervals, computes goodness-of-fit statistics and automated acceptance checks, generates a 50-point fitted curve grid with confidence envelopes for native Excel scatter charts, and ranks all candidate models side-by-side using the sample-size-corrected Akaike Information Criterion ($ $).

The template offers six standard physical and empirical model forms: Power law ($ y = a x^b $), Exponential decay to offset ($ y = y_0 + A e^{-k x} $), Saturating rise ($ y = A (1 - e^{-k x}) $), Hyperbolic saturation (Michaelis-Menten: $ y = v_{} x / (K_m + x) $), Linear ($ y = b_0 + b_1 x $), and Quadratic polynomial ($ y = b_0 + b_1 x + b_2 x^2 $).

The workbook ships pre-populated with illustrative allometric scaling data representing mammalian body mass ($ $) versus basal metabolic rate ($ $) spanning 0.025 to 520 kg (Kleiber’s 3/4-power scaling law with modest scatter). These are illustrative numbers generated for demonstration rather than a single published empirical study. The workbook includes an active Defaults configuration table, an in-workbook modification guide, and a Validation sheet verifying every routine against certified NIST StRD datasets (DanWood, Misra1a, Norris) and noise-free ground truth benchmarks.

Before you start

Requirements. Excel with Python for Excel enabled. The template operates self-contained within workbook sheets and does not require external databases or third-party fitting software.

Core curve fitting and model selection concepts. - Nonlinear least squares (NLS): Fits arbitrary nonlinear response functions by minimizing the sum of squared residuals using SciPy’s bounded curve fitting (trust-region reflective algorithm). Unlike linear transformations, NLS optimizes parameters in the original measurement scale. - Asymptotic standard errors and confidence intervals: Parameter uncertainties are estimated from the linearized covariance matrix ($ V = s_{y.x}^2 (J^T J)^{-1} $), scaled by Student’s $ t $-distribution quantile at the specified degrees of freedom ($ n - p $). - Corrected Akaike Information Criterion ($ $): An information-theoretic metric that penalizes model complexity while rewarding fit quality, with an exact small-sample correction: $$ = n () + 2k + $$ where $ n $ is the number of data points, $ $ is the residual sum of squares, and $ k $ is the number of free parameters. - $ $ relative evidence: The difference between a candidate model’s $ $ and the minimum $ $ across all models. The template categorizes empirical support as: $ < 2.00 $ (Substantial support), $ 2.00 < 7.00 $ (Less support), and $ $ (No support). - Power-law bounds and decreasing trends: The power law model uses bounds lower=(0, 0), restricting scaling exponents to $ b $. It requires strictly positive values ($ x > 0 $). Observations with $ x $ are excluded from the fit with a visible notice. If data exhibits a decreasing trend (log-log slope < 0), the optimizer returns $ b $ at the lower bound with the diagnostic flag exponent at lower bound and a pre-fit notice is displayed in the task pane. - Nonlinear fit vs. log-log regression: For power laws, nonlinear regression minimizes absolute errors ($ y - $), whereas log-log linear regression ($ y = a + b x $) minimizes relative percentage errors. In scientific practice, comparing both approaches reveals whether measurement noise is additive or proportional.

Workbook tour

Sheet and range What it holds Kind
Data, A4:B104 Observation table: independent variable ($ x $) and dependent variable ($ y $). Pre-populated with 12 illustrative mammal scaling points. Editable input
Defaults, A4:D12 Configuration table: active Model selector, Confidence level, R-squared floor, Outlier threshold, and X/Y labels and units. Editable input
Results, A4:E7 Parameter estimates (Estimate, Standard error, Low, High) for the selected model. Output
Results, A10:B13 Automated acceptance checks: R² floor, distinct x count, power-law domain/guard checks. Output
Results, A16:B26 Fit statistics: points used, degrees of freedom, R², residual SD ($ s_{y.x} $), AICc, confidence level, outlier check, model diagnostic flags. Output
Results, A29:F35 Model comparison table: all candidate models evaluated side-by-side with R², residual SD, AICc, Delta-AICc, and Best/support status. Output
Results, A37 Native Excel scatter chart plotting observations, fitted curve, and confidence envelope. Output
Curve, A4:D54 Grid of 50 x values with fitted curve and confidence bounds feeding the Results chart. Output
Validation, A4:G22 Benchmark verification table comparing calculated values against certified NIST StRD datasets and synthetic recovery benchmarks (all PASS). Output
Guide, A4:B12 Step-by-step instructions for pasting custom data, choosing candidate models, interpreting AICc, and extending engine equations. Authored reference

Using the template

1. Enter observations

  1. Navigate to the Data sheet.
  2. Paste your data pairs into columns A (x) and B (y) starting at row 5. Up to 100 observation rows are supported by default.
  3. Non-numeric rows, comment headers, or blank cells are ignored automatically.
  4. On the Defaults sheet, adjust X label, X unit, Y label, and Y unit to name your variables. The X label and unit appear in the Curve sheet header. The Y label and unit are not shown on any sheet, and the chart axis titles stay fixed as “x” and “y”.

2. Select a model and configure thresholds

  1. Open the Defaults sheet.
  2. Select cell B5 (Model) and choose from the validation dropdown list:
    • Power law: $ y = a x^b $ (baseline allometric and physical scaling)
    • Exponential decay to offset: $ y = y_0 + A e^{-k x} $ (first-order relaxation with plateau)
    • Saturating rise: $ y = A (1 - e^{-k x}) $ (first-order approach to an asymptote)
    • Hyperbolic saturation: $ y = v_{} x / (K_m + x) $ (Michaelis-Menten and Langmuir adsorption forms)
    • Linear: $ y = b_0 + b_1 x $ (straight-line calibration)
    • Quadratic: $ y = b_0 + b_1 x + b_2 x^2 $ (parabolic response)
  3. Select cell B6 (Confidence level) to set statistical interval width (default 0.95 for 95% two-sided intervals).
  4. Select cell B7 (R-squared floor) to configure minimum acceptable fit quality (default 0.90).
  5. Select cell B8 (Outlier threshold (SD)) to set the residual multiplier for outlier detection (default 3.0 standard deviations).

3. Review parameters and acceptance checks

  1. Open the Results sheet.
  2. Review the parameter estimates, standard errors, and confidence intervals in rows 4 to 7.
  3. Check the Acceptance checks block in rows 10 to 13. A FAIL indicates that $ R^2 $ fell below your floor, that there were insufficient distinct $ x $ values, or that non-positive $ x $ values violated power-law model assumptions.
  4. Inspect the Fit statistics block in rows 16 to 26 for residual standard deviation ($ s_{y.x} $) and degrees of freedom.

4. Compare candidate models by AICc

  1. Scroll to the Model comparison table on Results (rows 28 to 35).
  2. The workbook automatically fits all candidate models to your data.
  3. The model with minimum $ $ is marked Best.
  4. Review the Delta-AICc column:
    • $ = 0.00 $: Best supported model (Best).
    • $ < 2.00 $: Substantial support (competing candidate).
    • $ 2.00 < 7.00 $: Less support.
    • $ $: No support.
  5. If a model cannot be mathematically evaluated on your data (for example, non-positive values for a power law), the table displays n/a with the reason.

Inputs and settings

Data (A5:B104): - x (column A): Independent variable (e.g. body mass, concentration, time, or voltage). - y (column B): Dependent variable (e.g. metabolic rate, absorbance, decay signal, or sensor output).

Defaults (A5:D12): - Model (B5): Active mathematical equation to fit. Dropdown choice among the six candidate models. Default: Power law. - Confidence level (B6): Two-sided Student $ t $ confidence level for parameter intervals and curve bands (0.50 to 0.99). Default: 0.95. - R-squared floor (B7): Minimum coefficient of determination required for acceptance check (0.00 to 0.99). Default: 0.90. - Outlier threshold (SD) (B8): Number of residual standard deviations ($ s_{y.x} $) for flagging outlier points (2.0 to 5.0). Default: 3.0. - X label (B9): Name of the independent variable. Appears in the Curve sheet header with the X unit. The chart x-axis title is fixed text ‘x’. Default: Body mass. - X unit (B10): Engineering or scientific unit for the independent variable. Default: kg. - Y label (B11): Name of the dependent variable. Not displayed on any output sheet; the chart y-axis title is fixed text ‘y’. Default: Metabolic rate. - Y unit (B12): Engineering or scientific unit for the dependent variable. Default: W.

How results are calculated

  1. Filtering and data validation: The workbook extracts numeric entries from Data columns A and B. Blank cells and text are excluded. The template requires at least 5 usable observations and at least $ k $ distinct $ x $ values, where $ k $ is the number of free parameters.
  2. Model fitting:
    • Linear and quadratic models are solved using weighted linear least squares with coordinate scaling for numerical stability.
    • Nonlinear models (Power law, Exponential decay with offset, Saturating rise, Hyperbolic saturation) are fitted using SciPy’s bounded curve fitting (scipy.optimize.curve_fit, trust-region reflective method) with domain-informed parameter initializations; parameter covariance is evaluated from the model’s analytic Jacobian.
  3. Parameter covariance and standard errors: Parameter variance-covariance matrix: $$ V = s_{y.x}^2 (J^T J)^{-1}, s_{y.x} = $$ where $ J $ is the Jacobian matrix evaluated at the optimum parameters, $ n $ is the number of points, and $ p $ is the parameter count. Standard errors are the square roots of the diagonal elements ($ _i = $).
  4. Confidence intervals: Two-sided confidence intervals at level $ 1 - $: $$ i t{n-p, 1-/2} _i $$
  5. Confidence bands on the curve grid: For each point $ x_0 $ on the 50-point grid, the gradient vector $ (x_0) = _f(x_0, ) $ is evaluated, giving the variance of the fitted mean: $$ (_0) = (x_0)^T V (x_0) $$ The lower and upper bounds are $ 0 t{n-p} $.
  6. Information criterion ($ $): Evaluated for every candidate model: $$ = n () + 2k + $$ The lowest $ $ designates the Best model, and relative evidence is quantified as $ = - () $.

Reading the results

Parameter estimates table (Results!A4:E7): - Parameter: Name of the model coefficient (e.g. Scale ($ a $), Exponent ($ b $), Offset, Rate). - Estimate: Optimized numerical coefficient. - Standard error: Asymptotic standard error reflecting parameter uncertainty. - Low / High: Lower and upper limits of the two-sided confidence interval at the configured confidence level.

Acceptance checks table (Results!A10:B13): - Fit R-squared >= floor: Confirms the regression accounts for the minimum required proportion of variance. - Distinct x values >= k: Verifies that sufficient distinct independent points exist to identify all free parameters. - Domain requirements / diagnostic flags: Confirms mathematical validity (e.g. $ x > 0 $ for power law).

Fit statistics table (Results!A16:B26): - Comprehensive summary including data point count ($ n $), residual degrees of freedom ($ n - p $), coefficient of determination ($ R^2 $), residual standard deviation ($ s_{y.x} $), $ $, and outlier detection summary.

Model comparison table (Results!A29:F35): - Side-by-side performance across all six candidate models, displaying $ R^2 $, $ s_{y.x} $, $ $, and $ $, with the best model flagged.

Saving, sharing and App mode

  • Saving: Save the workbook as an ordinary .xlsx file. All input tables, settings, chart graphics, and Python calculation results persist in the file.
  • Sharing: Distribute the workbook to any user with Python for Excel enabled. When opened, the notebook automatically re-evaluates and updates all outputs.
  • App mode: In App mode, non-technical users interact exclusively with worksheet cells (Data, Defaults, Results, and Guide) while the Python runtime executes in the background.

Errors, empty states and limits

  • Non-positive x domain check: The power law equation $ y = a x^b $ requires strictly positive values ($ x > 0 $). Any observations with $ x $ are excluded from the fit with a visible notice, the domain check in the acceptance table reports FAIL, and n/a: Requires x > 0 is displayed in the comparison table.
  • Out-of-domain x and the comparison table: When x values fall outside the selected model’s domain, the selected model is fitted only to its in-domain observations (Points used and the Domain notice count those rows), and its row in the Model comparison table shows n/a. The comparison then fits Linear and Quadratic on all rows, including negative x, so the Best flag and Delta-AICc rank models fitted to different observations from the selected fit. The selected fit is not part of that ranking.
  • Decreasing power law: The built-in power law model uses bounds lower=(0, 0), restricting exponents to $ b $. Decreasing relationships (where the least-squares slope of $ y $ on $ x $ is negative) cannot be fitted with a negative exponent; the optimizer converges to the lower bound ($ b $), the model diagnostic flags display exponent at lower bound, and a pre-fit notice alerts the user in the task pane. For decreasing power laws, fit log-log linear regression or customize bounds in notebook.py.
  • Minimum observations: At least 5 complete observation rows are required. Rows containing non-numeric characters or empty cells are excluded from fitting.
  • Row limits: Up to 100 observation rows (Data!A5:B104) are read by default. An overflow probe at Data!A105 alerts you if additional rows are pasted beyond row 104.

Customizing as an author

To adjust default rules or diagnostic thresholds: 1. Open the Boardflare task pane and switch to Edit mode. 2. Locate the marked edit section in notebook.py: python # --- Edit these rules --- DEFAULT_MODEL = "Power law" DEFAULT_CONFIDENCE = 0.95 DEFAULT_R_SQUARED_FLOOR = 0.90 DEFAULT_OUTLIER_SD = 3.0 DEFAULT_X_LABEL = "Body mass" DEFAULT_X_UNIT = "kg" DEFAULT_Y_LABEL = "Metabolic rate" DEFAULT_Y_UNIT = "W" GRID_POINTS = 50 3. Modify any constant and save the cell. The workbook immediately recomputes and publishes updated outputs.

Limitations and when to use this

  • Unweighted least squares: The template assumes homoscedastic residual variance across observations. If relative error is constant across several orders of magnitude, heteroscedasticity may warrant weighted least squares ($ 1/y^2 $ or $ 1/x^2 $) or log-transformed linear regression.
  • Linear chart axes and titles: Native Excel charts in this workbook use linear axes to uniformly display all candidate models (including negative values in linear/quadratic models). Chart axis titles are fixed text (‘x’ and ‘y’) to remain generic across any custom data entered on Defaults. For power-law scaling across wide dynamic ranges, right-click the chart axis in Excel and check “Logarithmic scale” (base 10) to visualize log-log linearity.
  • AICc assumptions: AICc evaluates relative model parsimony based on identical data subsets. When comparing candidate models, all models must be fitted to the exact same observations.

Free alternatives

Free alternative: Excel trendlines and the Real Statistics add-in cover simple linear and common nonlinear forms. This workbook adds bounded engine fits with parameter confidence intervals, fit diagnostics, and validation against NIST StRD datasets.

Developer reference

Notebook implementation

The notebook imports boardflare and standard scientific libraries (numpy, pandas, scipy.optimize, scipy.stats). Input ranges are bound in a single reactive call:

inputs = bf.inputs(
    data=bf.ref("Data!A4:B104", headers=True),
    defaults=bf.ref("Defaults!A4:B12", headers=True),
    overflow="Data!A105",
)

The publication cell spills six tables to their worksheet coordinates:

publication = bf.publish(
    outputs={
        "results": results_table,
        "checks": checks_table,
        "statistics": statistics_table,
        "comparison": comparison_table,
        "curve": curve_table,
        "validation": validation_table,
    }
)

How the calculation/model works

Nonlinear optimization minimizes the weighted residual sum of squares: $$ = _{i=1}^n (y_i - f(x_i, ))^2 $$

Model equations: - Power law: $ f(x, a, b) = a x^b $ - Exponential decay with offset: $ f(x, y_0, A, k) = y_0 + A e^{-k x} $ - Saturating rise: $ f(x, A, k) = A (1 - e^{-k x}) $ - Hyperbolic saturation: $ f(x, v_{}, K_m) = $ - Linear polynomial: $ f(x, b_0, b_1) = b_0 + b_1 x $ - Quadratic polynomial: $ f(x, b_0, b_1, b_2) = b_0 + b_1 x + b_2 x^2 $

Parameter standard errors: $$ (j) = , V = s{y.x}^2 (J^T J)^{-1} $$

Delta-AICc metric: $$ _m = m - {i} _i $$

Validation / expected results

The shipped baseline dataset (mammalian scaling) produces: - Power law fit: Scale $ a = 3.6982 $ (SE: $ 0.3334 $, 95% CI: $ 2.9553 $ to $ 4.4412 $), Exponent $ b = 0.7564 $ (SE: $ 0.0149 $, 95% CI: $ 0.7232 $ to $ 0.7896 $). - Goodness-of-fit: $ R^2 = 0.9991 $, $ s_{y.x} = 4.7388  $, $ = 40.48 $. - Model comparison: Power law ranks #1 (Best, $ = 0.00 $); Hyperbolic saturation ($ = 12.35 $); Saturating rise ($ = 14.15 $); Exponential decay ($ = 15.45 $); Quadratic ($ = 18.96 $); Linear ($ = 29.24 $). - Validation sheet: 17 reference checks plus a summary row against certified NIST StRD datasets (DanWood, Misra1a, Norris) and noise-free synthetic recovery benchmarks, all reporting PASS.

Changelog

2026-10-26 (Issue #236)

  • Rebuilt template as a hand-crafted field workbook (tier: field-workbook), replacing the generic starter.
  • Integrated six candidate engine models: Power law, Exponential decay with offset, Saturating rise, Hyperbolic saturation (Michaelis-Menten), Linear, and Quadratic.
  • Added candidate Model comparison table ranking all models by small-sample corrected Akaike Information Criterion ($ $).
  • Added Data sheet with illustrative mammalian allometric scaling data (body mass vs. resting metabolic rate, exponent near 0.75).
  • Added Defaults sheet for configurable Model selection, confidence level, R-squared floor, outlier standard deviation threshold, and axis labels/units.
  • Added 50-point Curve grid feeding native Excel scatter chart with fitted curve and two-sided confidence envelope.
  • Added Validation sheet with 17 verified reference checks plus a summary row against NIST StRD fixtures (DanWood, Misra1a, Norris) and noise-free synthetic benchmarks.
  • Added comprehensive in-workbook Modification Guide and developer reference.