Statistical & Mathematical Formulas
Trapezoidal Rule Area Under the Curve (AUC) With SUMPRODUCT
Integrate discrete sensor, pharmacokinetic (AUC), or power-load curves across uneven X-intervals without helper columns using 0.5 * SUMPRODUCT(A3:A6 - A2:A5, B2:B5 + B3:B6).
Integrate unevenly spaced clinical PK, battery, and hydrograph curves in a single cell
Pharmacokinetic blood draws (t = 0, 1, 3, 6, 10 hours), storm-runoff hydrographs, and dynamometer power logs almost never arrive on uniform Delta-t spacing. Multiplying AVERAGE(y) by total span wildly distorts Area Under the Curve (AUC), and creating a dedicated trapezoid helper column clutters multi-subject summary tables. The result is exact for the piecewise-linear interpolant between samples, not necessarily for the unknown curve. Input times must be complete numeric values in strictly increasing order; a positive first-to-last span alone is insufficient.
Offsetting two dynamic slices inside SUMPRODUCT evaluates the exact Composite Trapezoidal Rule SUM(0.5 * (xi+1 - x_i) * (y_i + yi+1)) in one cell: =0.5 * SUMPRODUCT(A3:A6 - A2:A5, B2:B5 + B3:B6). Check our 5-point plasma drug concentration curve (0 h: 4 mg/L, 1 h: 8, 3 h: 14, 6 h: 10, 10 h: 6): the four uneven trapezoids contribute 6 + 22 + 36 + 32 = 96.0 mg*h/L, giving a time-weighted mean concentration AUC / (t_end - t_start) = 96 / 10 = 9.60 mg/L.
| Interval [t_i, ti+1] (Hours) | Width Delta_t (h) | Endpoint Heights y_i + yi+1 | Trapezoid Area = 0.5 * dt * (y_i + yi+1) |
|---|---|---|---|
| [0, 1] h & [1, 3] h | 1 h & 2 h | (4 + 8) & (8 + 14) mg/L | 6.0 + 22.0 = 28.0 mg*h/L |
| [3, 6] h & [6, 10] h | 3 h & 4 h | (14 + 10) & (10 + 6) mg/L | 36.0 + 32.0 = 68.0 mg*h/L |
| Total Composite AUC [0, 10] h | Total Span = 10 h | Time-Weighted Mean = 9.60 mg/L | 96.0 mg*h/L |
Single-Cell Uneven-Interval Trapezoidal AUC Formula
Pairs adjacent X widths (A3:A6 - A2:A5) with adjacent Y height sums (B2:B5 + B3:B6) while guarding non-positive total span.
=IF(OR(COUNT(A2:A6) <> 5, COUNT(B2:B6) <> 5, SUMPRODUCT(--(A3:A6 <= A2:A5)) > 0), "Invalid or unordered data", ROUND(0.5 * SUMPRODUCT(A3:A6 - A2:A5, B2:B5 + B3:B6), 2))Time-Weighted Mean Value = Trapezoidal AUC / (X_end - X_start)
Divides integrated area under the curve by total domain width (A6 - A2) to eliminate uneven-sampling bias.
=IF(OR(COUNT(A2:A6) <> 5, COUNT(B2:B6) <> 5, SUMPRODUCT(--(A3:A6 <= A2:A5)) > 0), "Invalid or unordered data", ROUND((0.5 * SUMPRODUCT(A3:A6 - A2:A5, B2:B5 + B3:B6)) / (A6 - A2), 2))Try the example
Use the original values or change them to test your understanding.