How to Propagate Uncertainty in Excel: A Step-by-Step Guide Using Formulas

Excel is a powerful platform for measurement uncertainty, but it does not know which cells are uncertain. To propagate uncertainty correctly, you must encode the error propagation model yourself, following the GUM Guide and the significant figures conventions used for reporting. This reference explains the formulas, the Excel functions, and the rounding rules that keep your results defensible.

Rule Statement

For a measurement model y = f(x1, x2, …, xn), the combined standard uncertainty u_c(y) is the positive square root of the combined variance. The GUM gives the general law of propagation of uncertainty in JCGM 100:2008, Clause 5.1.2, Equation (10):

u_c^2(y) = Σ (∂f/∂xi)^2 u^2(xi) + 2 Σ Σ (∂f/∂xi)(∂f/∂xj) u(xi,xj)

When inputs are independent, the covariance terms are zero. In Excel, the independent case is usually implemented as:

=SQRT(SUMPRODUCT(partials^2, u^2))

For addition or subtraction, q = x1 + x2 – x3, the partial derivatives are ±1, so the formula simplifies to =SQRT(SUMSQ(u1, u2, u3)). For multiplication or division, q = x1*x2/x3, use the relative form: =q*SQRT(SUMPRODUCT((u/x)^2)). Always propagate standard uncertainties, not expanded uncertainties. Convert expanded uncertainty U to standard uncertainty with u = U/k before using these formulas (GUM Clause 5.2.2). Round only at the final reporting stage; see rounding rules.

Worked Examples

Example 1: Density from Mass and Volume

Suppose mass m = 12.345 g with u(m) = 0.002 g, and volume V = 5.00 mL with u(V) = 0.02 mL. The model is ρ = m/V. In Excel, place values in B2:B5: B2=12.345, B3=5.00, B4=0.002, B5=0.02.

1. Compute density: =B2/B3 → 2.469 g/mL.
2. Compute relative uncertainties: =B4/B2 → 0.000162; =B5/B3 → 0.00400.
3. Combine relative uncertainties: =SQRT((B4/B2)^2+(B5/B3)^2) → 0.004003.
4. Convert to absolute uncertainty: =B6*B7 → 0.00988 g/mL.
5. Report with two significant digits in the uncertainty: 2.469 ± 0.010 g/mL, or 2.47 ± 0.01 g/mL if using one significant digit.

Example 2: Sum of Lengths

L = L1 + L2, where L1 = 10.0 cm ± 0.1 cm and L2 = 5.00 cm ± 0.05 cm. Because the model is a sum, use absolute uncertainties:

=SQRT(SUMSQ(0.1,0.05)) → 0.1118 cm. The sum is 15.00 cm, so report 15.00 ± 0.11 cm. Note that the decimal places of the uncertainty determine the decimal places of the result.

Example 3: Power Law

For q = x^n, the relative uncertainty is |n| times the relative uncertainty of x: =q*ABS(n)*u_x/x. For q = x^2 with x = 3.00 ± 0.05, q = 9.00 and u_q = 9.00*2*(0.05/3.00) = 0.30. Excel: =B2^2*ABS(2)*B3/B2.

Counter-Examples

• Adding uncertainties directly. For independent quantities, u_total ≠ u1 + u2. That overestimates uncertainty. Use root-sum-of-squares unless inputs are perfectly correlated.
• Using STDEV.S as the standard uncertainty. For repeated measurements, the standard uncertainty of the mean is =STDEV.S(range)/SQRT(COUNT(range)), not =STDEV.S(range).
• Rounding intermediate cells. If you round 2.469 to 2.47 before propagation, you introduce double-rounding error. Keep full precision in Excel and round only the final reported value.
• Ignoring covariance. If inputs share a calibration or reference, the covariance terms in GUM Equation (13) may be non-zero. Treating them as independent can understate or overstate u_c.
• Confusing expanded and standard uncertainty. A coverage factor k = 2 gives U = 2u_c. Do not plug U into the propagation formula without dividing by k.

Convention Comparison Table

Standards Citation

The authoritative reference is JCGM 100:2008 (GUM), Evaluation of measurement data — Guide to the expression of uncertainty in measurement. Clause 5.1.2 and Equation (10) define the combined standard uncertainty; Clause 5.2.2 defines the expanded uncertainty U = k u_c. For numerical simulation, see JCGM 101:2008, Supplement 1 — Propagation of distributions using a Monte Carlo method. NIST TN 1297 provides practical NIST guidance on evaluating and expressing uncertainty. ISO 80000-1:2009, Clause 7.3.3, gives rounding rules for numerical values. ASTM E29-13a covers significant digits in test data for conformance with specifications.

Common Mistakes

1. Using ROUND on intermediate results. Excel’s stored value should remain unrounded.
2. Forgetting to convert expanded uncertainty to standard uncertainty: u = U/k.
3. Using STDEV.P when the data are a sample; use STDEV.S for sample standard deviation.
4. Treating display formatting as data precision. Formatting to two decimals does not change the stored value.
5. Ignoring correlation when the same instrument or standard calibrates multiple inputs.
6. Reporting too many digits. Match the last significant digit of the result to the last significant digit of the uncertainty.
7. Using relative uncertainty for addition. Relative uncertainties do not add directly for sums.

Software Behavior Note

Excel stores numbers in IEEE 754 binary64 format, which provides about 15–17 significant decimal digits. Display formatting, column width, and the Precision as displayed option do not change the underlying value unless you enable that option, which can permanently truncate data. Use ROUND, ROUNDUP, or ROUNDDOWN only for final reporting. For independent propagation, SUMPRODUCT and SUMSQ are efficient. For correlated inputs, build a covariance matrix and use an array formula such as =SQRT(MMULT(TRANSPOSE(J),MMULT(Cov,J))), where J is the vector of partial derivatives. In older Excel versions, enter with Ctrl+Shift+Enter; in Microsoft 365, dynamic arrays handle it automatically. For Monte Carlo, use NORM.INV(RAND(),mean,u) and a data table. Excel’s STDEV.S is appropriate for Type A evaluation, but the standard uncertainty of the mean requires division by the square root of n.

Quick Reference Table

Sources & Further Reading

• JCGM 100:2008, Evaluation of measurement data — Guide to the expression of uncertainty in measurement (GUM), Clauses 5.1.2, 5.2.2.
• JCGM 101:2008, Supplement 1 — Propagation of distributions using a Monte Carlo method.
• NIST Technical Note 1297, Guidelines for Evaluating and Expressing the Uncertainty of NIST Measurement Results.
• ISO 80000-1:2009, Quantities and units — Part 1: General, Clause 7.3.3.
• ASTM E29-13a, Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications.

For related precision rules, see Error Propagation, Rounding Rules, and Why Sig Figs Are an Approximation.