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.
Offsetting two dynamic slices inside SUMPRODUCT evaluates the exact Composite Trapezoidal Rule SUM(0.5 * (x_{i+1} - x_i) * (y_i + y_{i+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, t_{i+1}] (Hours) | Width Delta_t (h) | Endpoint Heights y_i + y_{i+1} | Trapezoid Area = 0.5 * dt * (y_i + y_{i+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(A6 <= A2, "Invalid X Span", 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(A6 <= A2, "Invalid X Span", 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.