Arte SheetsLearn. Try. Understand.

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+1Trapezoid Area = 0.5 * dt * (y_i + yi+1)
[0, 1] h & [1, 3] h1 h & 2 h(4 + 8) & (8 + 14) mg/L6.0 + 22.0 = 28.0 mg*h/L
[3, 6] h & [6, 10] h3 h & 4 h(14 + 10) & (10 + 6) mg/L36.0 + 32.0 = 68.0 mg*h/L
Total Composite AUC [0, 10] hTotal Span = 10 hTime-Weighted Mean = 9.60 mg/L96.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.

Further reading