Calibration curve (linear, with unknowns)

Statistics
Curve Fitting
Scientific Computing
Fit a linear instrument calibration curve with optional internal standard ratio, LOD/LOQ estimated with the ICH Q2(R2) approach (3.3 and 10 times the residual standard deviation divided by the slope), and QC recovery for unknown samples.

Overview

Calibration curve (linear, with unknowns) fits an instrument calibration curve to relate detector response to known analyte concentrations, following standard analytical practice in chromatography (HPLC, GC) and mass spectrometry (LC-MS). It supports both direct instrument signals (such as UV absorbance) and internal-standard response ratios (analyte peak area divided by internal-standard peak area). The template estimates regression parameters using ordinary least squares (OLS), estimates detection and quantitation limits (LOD and LOQ) from the calibration curve with the ICH Q2(R2) section 3.2.3.3 approach (3.3σ/S and 10σ/S), where σ is the residual standard deviation s_y.x of the fit, runs automated acceptance checks (including ICH M10 §3.2.4 accuracy across standards), computes %RSD across standard replicates, and back-calculates concentrations and % recovery for quality-control (QC) and unknown test samples.

The workbook ships pre-populated with a 6-level calibration standard sequence with duplicate injections and an internal standard, quality-control check samples, unknown samples, a configurable Defaults table, an in-workbook modification guide, and method performance benchmark datasets that show PASS on the shipping data.

Before you start

Requirements. Excel with Python for Excel enabled. The template operates entirely within the workbook sheets without requiring external databases or third-party instrument connections.

Core chromatography and calibration concepts. - Calibration level: A discrete target concentration of the analyte standard. Choose calibration levels that span the expected assay range; the shipped sequence uses 6 non-zero levels. - Internal standard (IS): A compound structurally similar or isotopically labelled added at a fixed concentration to all calibrators, QC samples, and unknowns. Dividing the analyte peak area by the IS peak area normalizes for injection volume variability, ionization fluctuation, and sample evaporation. - Response ratio: The quotient of analyte signal divided by internal-standard signal. When an internal standard is omitted or unavailable, the raw analyte signal serves as the response. - Limit of Detection (LOD): The lowest analyte concentration reliably distinguished from background noise, estimated as \(3.3 s_{y.x} / S\), where \(s_{y.x}\) is the residual standard deviation of the calibration regression and \(S\) is the calibration slope. This is the ICH Q2(R2) DL formula (section 3.2.3.3) with σ taken from the calibration curve. The template computes this estimate and does not validate it; ICH Q2(R2) section 3.2.3.5 says an estimated DL can be confirmed by analysing samples near it. - Limit of Quantitation (LOQ): The lowest concentration quantifiable with acceptable precision and accuracy, estimated as \(10 s_{y.x} / S\). This is the ICH Q2(R2) QL formula (section 3.2.3.3) with σ taken from the calibration curve. The template computes this estimate and does not validate it; ICH Q2(R2) section 3.2.3.5 says an estimated QL should be confirmed by analysing samples at or near it. - % Recovery: For QC samples with a known nominal concentration, the back-calculated concentration divided by the nominal concentration, multiplied by 100. The default window of \(\pm 15\%\) of nominal is the QC limit in ICH M10 section 3.2.5.2 for bioanalytical methods, which allows \(\pm 20\%\) at the LLOQ. The template applies whichever window you set on Defaults and does not apply the LLOQ limit separately. M10 covers bioanalytical methods, so using its window for instrument calibration is an analogy, not an M10 application.

Physical and statistical assumptions. 1. Linear dynamic range: The detector operates within its linear range where response ratio is proportional to concentration. 2. Homoscedasticity: Residual variance is assumed uniform across concentrations. For analytical methods spanning multiple orders of magnitude, relative error is often constant, making variance heteroscedastic. 3. Independent residuals: Calibration residuals are assumed independent and normally distributed with zero mean. 4. Negligible standard uncertainty: Analyte concentrations in standards are prepared with gravimetric/volumetric precision exceeding detector measurement noise.

Workbook tour

Sheet and range What it holds Kind
Standards, A4:G504 Calibration standard ladder: Level, Nominal conc. (ug/mL), Replicate, Analyte signal, optional IS signal, Exclude?, and Reason. Sized for up to 500 standards. Editable input
Standards, I4:I504 Computed Response ratio (analyte / IS if IS is present, else raw analyte). Output
Standards, K4:N12 Per-level summary: Level, Nominal conc., Mean ratio, and %RSD across included replicates. Output
Defaults, A4:D24 Configurable analysis settings: Confidence level, Acceptance window (%), Replicates per unknown, Fit through origin (No by default), and optional Run label. Editable input
Unknowns, A4:D204 Test sample table: Sample ID, Analyte signal, optional IS signal, and Nominal conc. (filled for QC, blank for unknowns). Sized for up to 200 unknowns. Editable input
Unknowns, F4:M204 Results table: Sample ID, Response ratio, Back-calculated conc., % Recovery, SE, Low, High, and Flags. Output
Results, A4:E8 Calibration fit parameters (Intercept, Slope) and derived quantities (LOD and LOQ estimated with the ICH Q2(R2) section 3.2.3.3 approach) with SE and confidence bounds. Output
Results, A11:B14 Automated acceptance checks: R² at least 0.99 (uncentred R² when Fit through origin is Yes), LOQ at or below lowest calibration standard, and ICH M10 §3.2.4 accuracy (at least 75% of standards and 6 standard levels within acceptance criteria). Output
Results, A16:B30 Fit statistics: n, levels, degrees of freedom, R², residual SD (s_y.x), AICc, confidence level, acceptance window %, IS status, outlier check, model diagnostic flags, excluded points count, and run label. Output
Results, A32 Native scatter chart plotting calibration standards, fitted regression line, confidence bounds, and excluded points. Output
Curve, A4:D54 Grid of 50 concentration points with fitted line and lower/upper confidence bounds for the Results chart. Output
Plot, A4:C504 Chart helper series: included concentration points, fitted curve, and column C for excluded standard markers. Output
Validation, A4:F10 Method performance checks and illustrative benchmark datasets, six rows including the through-origin slope check (checks should normally pass; flags an analytical or fit issue if any row shows FAIL). Output
Lists, A4:A9 Reference dropdown lists for Exclude? and Reason codes (Air bubble, Pipetting error, Clot/Precipitate, Instrument flag, Operator error). Reference list
Guide, A4:B9 Step-by-step instructions for adapting the workbook to laboratory SOPs. Authored reference

Using the template

1. Enter calibration standards

  1. Navigate to the Standards sheet.
  2. Enter your calibration levels, nominal concentrations, replicate identifiers, and analyte signals starting in row 5 (sized for up to 500 standards, rows 5 to 504).
  3. If an internal standard is used, enter the IS peak area or signal in column E (IS signal). If not using an internal standard, leave column E blank; the template will regress raw analyte response directly.
  4. To exclude a compromised standard observation (e.g. air bubble or bad injection), select Yes in column F (Exclude?) and select an analytical reason in column G (Reason). Excluded rows are automatically styled in gray with strikethrough text, removed from the fit and per-level summary, and plotted as distinct gray markers on the Results scatter chart.
  5. The Response ratio (column I) and the per-level Mean ratio and %RSD (columns K:N) recalculate automatically.

2. Configure defaults

  1. Open the Defaults sheet.
  2. Select cell B5 (Confidence level) to adjust the statistical interval width (default 0.95 for 95% confidence).
  3. Select cell B6 (Acceptance window (%)) to set the QC recovery tolerance (default 15 for \(\pm 15\%\); the QC limit in ICH M10 §3.2.5.2 for bioanalysis).
  4. Select cell B7 (Replicates per unknown) if unknown signals represent an average of multiple test injections.
  5. Select cell B8 (Fit through origin) to choose No (default: intercept and slope fitted) or Yes (intercept fixed at zero). Read the Fit through origin limitation below before choosing Yes.
  6. Select cell B9 (Run label) to record an optional analytical sequence or batch ID (e.g. Run-20261028-01), displayed on Results.

3. Evaluate unknowns and QC samples

  1. On the Unknowns sheet, enter sample identifiers and detector signals in columns A through C (sized for up to 200 unknowns, rows 5 to 204).
  2. For quality control check samples, enter the known standard concentration in column D (Nominal conc. (ug/mL)). For unknown test samples, leave column D blank.
  3. Review columns F through M. QC samples display computed % Recovery; unknown samples display N/A.
  4. Check column M (Flags) for diagnostic notices (below LOQ, extrapolated, or recovery outside window).

4. Review results and acceptance checks

  1. On the Results sheet, inspect the fitted Intercept and Slope, LOD, LOQ, and confidence intervals.
  2. Review the Acceptance checks table to verify that \(R^2 \ge 0.99\), that calculated LOQ is at or below the lowest standard, and that the ICH M10 §3.2.4 accuracy check passes (\(\ge 75\%\) of standards and \(\ge 6\) standard levels within acceptance limits).
  3. Review the native chart displaying experimental standards, fitted regression envelope, and any excluded standard markers.

5. Running repeat analytical runs

To analyze a new analytical run: 1. Save a copy of the workbook with a new file name (e.g. calibration-curve-batch20261028.xlsx). 2. Update the Run label on Defaults cell B9. 3. Paste or enter the new sequence data into Standards (rows 5 to 504) and Unknowns (rows 5 to 204). 4. If candidate outliers are flagged in Results row 27 without an explicit exclusion, review the raw chromatogram; if justified by an analytical event, select Yes and a reason on Standards to exclude the point. 5. Python recalculates all regression parameters, acceptance checks, and unknown concentrations immediately upon data entry.

Inputs and settings

Standards (A5:G504): - Level: Calibration level number (integer, e.g. 1 to 6). - Nominal conc. (ug/mL): Prepared concentration of the calibration standard in µg/mL. (The template labels assume µg/mL; adapting the template to other units such as ng/mL or mg/L requires editing the CONCENTRATION_UNIT constant in notebook.py and the corresponding headers and chart axis in workbook.py.) - Replicate: Injection replicate number (1, 2, …). - Analyte signal: Peak area, peak height, or detector response for the analyte. - IS signal: Peak area or detector response for the internal standard. Leave blank if no internal standard is used. - Exclude?: Set to Yes to exclude the observation from regression and per-level summary. Excluded rows appear in gray strikethrough. - Reason: Reason code for auditability (Air bubble, Pipetting error, Clot/Precipitate, Instrument flag, Operator error).

Defaults (A5:D9): - Confidence level (B5): Two-sided Student \(t\) confidence level for prediction and parameter intervals. Allowed values: 0.50 to 0.99. Default 0.95. - Acceptance window (%) (B6): Symmetrical percentage window around nominal concentration for QC sample acceptance. Default 15 (±15%), the QC limit in ICH M10 section 3.2.5.2 for bioanalytical methods. M10 allows ±20% at the LLOQ; this template applies one window to all QC levels and does not switch automatically. Set it to your own acceptance criterion. - Replicates per unknown (B7): Number of replicate measurements averaged into the test signal. Default 1. - Fit through origin (B8): No (default) fits intercept and slope. Yes fixes the intercept at zero and fits the slope only, for a response that is proportional to concentration with no blank offset. Y, yes and true (case-insensitive) also turn it on. Any other value is treated as No. - Run label (B9): Optional free-text identifier for the analytical run or sequence (e.g. batch ID), recorded on Results.

Unknowns (A5:D204): - Sample ID (A5:A204): Alphanumeric sample or vial identifier. - Analyte signal (B5:B204): Detector response measured for the sample. - IS signal (C5:C204): Internal standard response measured in the same vial (optional). - Nominal conc. (ug/mL) (D5:D204): Expected concentration for QC samples in µg/mL. Leave blank for true unknowns.

How results are calculated

  1. Pre-processing and response ratio: For each standard or unknown sample, if an internal standard signal is present and positive, the response ratio is computed as \(y_i = \text{Analyte signal} / \text{IS signal}\). If the IS signal is blank or non-numeric, the raw analyte signal is used directly: \(y_i = \text{Analyte signal}\).
  2. Linear regression: Ordinary least squares minimizes the sum of squared residuals to estimate intercept \(b_0\) and slope \(b_1\): \[b_1 = \frac{\sum (x_i - \bar{x})(y_i - \bar{y})}{\sum (x_i - \bar{x})^2}, \quad b_0 = \bar{y} - b_1 \bar{x}\]
  3. Residual standard deviation: \[s_{y.x} = \sqrt{\frac{\sum (y_i - \hat{y}_i)^2}{n - 2}}\]
  4. Detection and quantitation limits (ICH Q2(R2) section 3.2.3.3, 3.3σ/S and 10σ/S): \[\text{LOD} = \frac{3.3 s_{y.x}}{b_1}, \quad \text{LOQ} = \frac{10 s_{y.x}}{b_1}\] Standard errors for LOD and LOQ are computed using the delta method, incorporating the uncertainty of the slope and the sampling variance of the residual standard deviation (\(\mathrm{Var}(s_{y.x}) = s_{y.x}^2 / (2\,\mathrm{df})\)).
  5. Inverse prediction and QC recovery: For a measured response ratio \(y_0\), the back-calculated concentration \(\hat{x}_0\) is: \[\hat{x}_0 = \frac{y_0 - b_0}{b_1}\] When a nominal concentration \(x_{\text{nom}}\) is provided, % recovery is calculated as \((\hat{x}_0 / x_{\text{nom}}) \times 100\%\). If the deviation from 100% exceeds the configured acceptance window, the flag recovery outside window is triggered.
  6. Fit through origin (only when Fit through origin = Yes): the intercept is fixed at zero, so \(b_0 = 0\), and the slope is the least-squares slope through the origin: \[b_1 = \frac{\sum x_i y_i}{\sum x_i^2}\] The residual variance uses \(n - 1\) degrees of freedom, \(s_{y.x} = \sqrt{\mathrm{SSR}/(n-1)}\), and the slope standard error is \(\mathrm{SE}(b_1) = s_{y.x} / \sqrt{\sum x_i^2}\). The intercept has no standard error or interval; Results shows it as n/a. R-squared is uncentred, \(R^2 = 1 - \mathrm{SSR}/\sum y_i^2\), because the usual centred R-squared is not defined for a model without an intercept. Results labels it “R-squared (uncentred)” and the acceptance check uses it. It is not comparable with the centred value from a free-intercept fit. LOD and LOQ use the formulas in item 4 with this \(s_{y.x}\) and \(\mathrm{df} = n-1\). They remain defined, so no n/a is needed for them. Inverse prediction is \(\hat{x}_0 = y_0 / b_1\), and QC recovery and the flags work as in item 5.

Reading the results

Results (A4:E8): - Intercept (ratio): Fitted baseline response offset \(b_0\) with standard error and confidence bounds. When Fit through origin is Yes, the row reads “(fixed at 0, not estimated)” with 0 as the estimate and n/a for the standard error and bounds. - Slope (ratio/(ug/mL)): Analytical sensitivity \(b_1\) measuring response ratio increase per unit concentration. - Limit of detection (LOD): Lowest concentration distinguishable from noise. - Limit of quantitation (LOQ): Lowest concentration quantifiable with established precision. - Model diagnostic flags (A16:B30, row 28): Shows None normally. When LOQ is above the lowest standard (in either fit mode), it shows “LOQ is above the lowest standard; the lowest standard is not quantifiable at this precision”. When LOQ is not a positive number (NaN or zero or below), it shows “LOQ could not be estimated; check the fit.”; the LOQ acceptance check reports FAIL in that case as well.

Acceptance checks (A11:B14): - Fit R-squared at least 0.99: Confirms that the linear model accounts for at least 99% of calibration variance. When Fit through origin is Yes, the check reads “Fit R-squared (uncentred) at least 0.99” and uses the uncentred R-squared described in the calculation section. - LOQ at or below lowest standard: Automated check verifying that the calculated LOQ does not extrapolate below the lowest physical calibrator. - ICH M10 §3.2.4 accuracy across standards: Verifies that at least 75% of calibration standards (and a minimum of 6 standard levels) meet back-calculation accuracy within the acceptance window (±15% of nominal, ±20% at LLOQ).

Unknowns table (F4:M204): - Back-calculated conc. (ug/mL): Estimated analyte concentration in the same units as the calibration standards (µg/mL). - % Recovery: Recovery percentage for QC samples; displays N/A for unknown samples. - Flags: below LOQ marks estimates below quantitation limit; extrapolated marks samples outside the calibration range; recovery outside window marks QC samples exceeding acceptance criteria.

Saving, sharing and App mode

  • Saving: Save the workbook normally as an .xlsx file. Standard inputs, settings, and Python calculation outputs persist with the file.
  • Sharing: Distribute the workbook to colleagues who have Python for Excel installed. When opened, the notebook automatically re-evaluates and updates all outputs.
  • App mode: In App mode, non-technical users interact exclusively with the grid (Standards, Defaults, Unknowns, and Results) while the Python notebook executes in the background.

Errors, empty states and limits

  • Empty state: The workbook ships populated with sample data. If Standards is cleared, the notebook displays a callout and retains the built-in reference example.
  • Minimum observations: At least 5 complete calibration rows are required. Rows with non-numeric text or blanks in concentration or response are excluded.
  • Minimum distinct levels: At least 3 distinct concentration levels are required to determine a linear fit and residual variance.
  • Row limits: Up to 500 calibration standards (rows 5 to 504) and up to 200 unknown samples (rows 5 to 204) are ingested. An overflow probe at Standards!A505 flags if extra rows are pasted beyond row 504.

Customizing as an author

To tune quality-control rules or diagnostic thresholds: 1. Open the Boardflare task pane and switch to Edit mode. 2. Locate the marked rules section: python # --- Edit these rules --- DEFAULT_CONFIDENCE = 0.95 DEFAULT_ACCEPTANCE_WINDOW_PCT = 15.0 # QC recovery window (+/-15%, the ICH M10 section 3.2.5.2 bioanalytical QC limit, applied here by analogy; M10 allows +/-20% at LLOQ; adjust to your SOP) DEFAULT_REPLICATES = 1 RSD_MAX_PCT = 15.0 # analytical-chemistry replicate %RSD threshold R_SQUARED_MIN = 0.99 # Minimum R^2 required for calibration acceptance OUTLIER_SD = 3.0 # Studentized residual threshold for candidate outlier warning (not automatically excluded) MIN_LEVELS = 3 # Minimum distinct calibration levels required MIN_OBSERVATIONS = 5 # Minimum usable standard observations required CONCENTRATION_UNIT = "ug/mL" # Default concentration unit for report labels and headers # --- End of edit rules --- 3. Edit thresholds directly (for example, adjusting RSD_MAX_PCT or OUTLIER_SD), then save the cell. The workbook re-evaluates immediately.

The through-origin choice is a Defaults setting (Defaults B8), not an edit rule, so change it on the Defaults sheet rather than in the notebook.

Limitations and when to use this

  • No signal-to-noise (S/N) alternative: Detection limits are calculated from the regression with the ICH Q2(R2) section 3.2.3.3 approach (\(3.3 s_{y.x}/S\) and \(10 s_{y.x}/S\)). The template computes these values and does not validate them; ICH Q2(R2) section 3.2.3.5 says an estimated QL should be confirmed by samples at or near it. Signal-to-noise (3:1 DL, 10:1 QL, section 3.2.3.2) is not implemented. Signal-to-noise estimation requires raw chromatographic baseline noise measurements from instrument data systems (Empower, Chromeleon) that are not present in tabular spreadsheets.
  • Weighted regression deferred: This template performs unweighted ordinary least squares. In wide dynamic ranges (spanning \(>2\) orders of magnitude), heteroscedastic variance may warrant \(1/x\) or \(1/x^2\) weighted regression, which is deferred to a future release.
  • Configurable QC window: The default acceptance window (±15%) matches the QC limit in ICH M10 section 3.2.5.2 for bioanalytical methods, which allows ±20% at the LLOQ; the template does not apply the LLOQ limit separately. Adjust the setting on Defaults to your own acceptance criterion. M10 governs bioanalysis, so the window here is borrowed by analogy.
  • Fit through origin (Defaults B8): Forcing the intercept to zero is valid only when the blank response is truly zero. If the blank or background gives an offset, the forced fit can bias the slope, LOD and LOQ. The shipped standards have a fitted intercept of about 0.2 ratio units, and with Fit through origin set to Yes the LOQ check reports FAIL (LOQ 0.72 µg/mL is above the lowest standard, 0.5 µg/mL). The template now flags this case: the Results model diagnostic flags row (B28) shows the note “LOQ is above the lowest standard; the lowest standard is not quantifiable at this precision” whenever LOQ exceeds the lowest standard, in either mode. The template keeps No as the default and does not change the shipped data.

Free alternatives

Free alternative: Excel trendlines (linear, with equation and R-squared) and the Real Statistics add-in fit the same straight line. This workbook adds LOD/LOQ from the residual standard deviation, back-calculation of unknowns with prediction intervals, and QC recovery checks against the acceptance window.

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(
    standards=bf.ref("Standards!A4:G504", headers=True),
    defaults=bf.ref("Defaults!A4:B24", headers=True),
    unknowns=bf.ref("Unknowns!A4:D204", headers=True),
    overflow="Standards!A505",
)

The fit executes ordinary linear regression via POLYNOMIAL_LINEAR. When Fit through origin is Yes, the fit cells pass fixed={"intercept": 0.0} to fit_model. The uncentred R-squared is computed in the fit cell, because the engine’s r_squared is centred. Derived quantities compute LOD and LOQ with delta-method standard errors. The publication cell outputs eight distinct tables to their workbook anchors:

publication = bf.publish(
    outputs={
        "standards_ratios": standards_ratios_table,
        "level_summary": level_summary_table,
        "results": results_table,
        "checks": checks_table,
        "statistics": statistics_table,
        "curve": curve_table,
        "unknowns": unknowns_table,
        "validation": validation_table,
    }
)

How the calculation/model works

The model implements degree-1 polynomial regression: \[y = \beta_0 + \beta_1 x\] Solved via SVD/QR factorization with coordinate scaling for numerical stability.

With Fit through origin = Yes the model is \(y = \beta_1 x\), with one free parameter, so \(\mathrm{df} = n - 1\). The intercept rows of the covariance matrix are zero, and the inverse prediction variance reduces to \(\mathrm{Var}(\hat{x}_0) = (\hat{x}_0^2\,\mathrm{Var}(b_1) + s_{y.x}^2/m)/b_1^2\).

Parameter covariance matrix: \[V = s_{y.x}^2 (J^T J)^{-1}, \quad s_{y.x} = \sqrt{\frac{\sum (y_i - \hat{y}_i)^2}{n - 2}}\]

Derived LOD and LOQ standard errors combine slope parameter uncertainty with sampling variance of the residual standard deviation: \[\mathrm{SE}(\text{LOD}) = \sqrt{\left(\frac{3.3 s_{y.x} \mathrm{SE}(b_1)}{b_1^2}\right)^2 + \left(\frac{3.3}{b_1}\right)^2 \frac{s_{y.x}^2}{2\,\mathrm{df}}}\]

Inverse prediction variance: \[\mathrm{Var}(\hat{x}_0) = \frac{\mathbf{j}(x_0)^T V \mathbf{j}(x_0) + \frac{s_{y.x}^2}{m}}{b_1^2}\] where \(m\) is the number of replicates averaged into the signal.

Validation / expected results

The baseline workbook evaluation with sample data produces: - Slope: \(2.4000\ \text{ratio}/(\mu\text{g/mL})\) (SE: \(0.0011\), 95% CI: \(2.3975\) to \(2.4025\)) - Intercept: \(0.2000\ \text{ratio}\) (SE: \(0.0257\), 95% CI: \(0.1428\) to \(0.2572\)) - LOD: \(0.0941\ \mu\text{g/mL}\) (SE: \(0.0210\), 95% CI: \(0.0472\) to \(0.1409\)) - LOQ: \(0.2850\ \mu\text{g/mL}\) (SE: \(0.0637\), 95% CI: \(0.1430\) to \(0.4271\)) - Acceptance checks: \(R^2 \ge 0.99\) (PASS) and \(\text{LOQ} \le 0.50\ \mu\text{g/mL}\) (PASS). - Validation sheet: All six method performance and benchmark checks report PASS on this shipped sample data (a check can report FAIL if the data it evaluates fails that criterion). The sixth check compares the engine’s through-origin slope on the illustrative linearity ladder with \(\sum xy / \sum x^2\) (expected 2.4115, tolerance 0.01%). - Fit through origin = Yes (scenario on the shipped standards, checked against an independent numpy calculation): slope \(2.4055\) (SE \(0.0022\), 95% CI \(2.4008\) to \(2.4103\)), \(s_{y.x} = 0.1734\) with 11 degrees of freedom, uncentred \(R^2 = 0.999991\) (PASS), LOD \(0.2379\ \mu\text{g/mL}\) (SE \(0.0507\)), LOQ \(0.7209\ \mu\text{g/mL}\) (SE \(0.1537\), 95% CI \(0.3826\) to \(1.0591\)). The LOQ check reports FAIL because \(0.72\) is above the lowest standard, \(0.50\ \mu\text{g/mL}\), and the Model diagnostic flags row shows the LOQ note.

Changelog

2026-10-26 (Issue #236)

  • Added Defaults option Fit through origin (Yes/No, default No). Yes fixes the intercept at zero and fits the slope through the origin, with n-1 degrees of freedom, uncentred R-squared, and n/a for the intercept standard error and bounds. With No, all outputs and cell positions are unchanged.
  • Added a sixth Validation check comparing the through-origin slope from the engine with the closed form sum(xy)/sum(x^2) on the illustrative linearity ladder.
  • Added the through-origin scenario, with expectations computed independently in numpy.
  • Results model diagnostic flags row (B28) now shows the note “LOQ is above the lowest standard; the lowest standard is not quantifiable at this precision” whenever LOQ exceeds the lowest standard, in either mode. Output cell positions are unchanged; with the note absent the row reads None as before.
  • Reworded regulatory attributions (FDA, EMA, ICH Q2) to describe what is computed: LOD/LOQ are estimated from the calibration curve with the ICH Q2 3.3σ/10σ approach (σ is the residual standard deviation s_y.x), and the default ±15% QC acceptance window is the window used in bioanalytical method validation guidance (for example FDA and ICH M10), adjustable to your SOP. These names identify where the formula or window comes from; the template is not validated or compliant against them.
  • Added the B28 note “LOQ could not be estimated; check the fit.” for an LOQ that is NaN or not positive, mirroring the LOQ acceptance check (which fails in that case). The blank-is-signal scenario now expects this note on B28, since its negative slope gives a non-positive LOQ on Results!B8.

2026-10-25 (Issue #236)

  • Rebuilt template as a field workbook (tier: field-workbook), replacing the initial prototype with a field-authentic chromatography and LC-MS workflow.
  • Added Standards sheet with Level grouping, internal-standard (IS) signal input, automatic response ratio computation, and per-level %RSD summary.
  • Added Defaults sheet for configurable confidence levels, QC acceptance window percentages, and sample replicate counts.
  • Added Unknowns/QC sheet with back-calculated concentrations, % recovery against acceptance criteria, and diagnostic flags (below LOQ, extrapolated, recovery outside window).
  • Added automated acceptance check verifying that LOQ falls at or below the lowest calibration standard.
  • Added Validation sheet with five reference benchmark datasets and an in-workbook Modification Guide.
  • Softened guideline citation to generic “ICH Q2”.