How to Calculate Percent Error in Excel (and Avoid Rounding Errors)

Percent error is one of the most common calculations in laboratory reports, engineering notebooks, and spreadsheet assignments. In Excel, it is simple to compute—but it is equally easy to introduce rounding errors, use the wrong denominator, or confuse percent error with percent difference or measurement uncertainty. This reference explains the rule, the Excel implementation, the standards that govern rounding, and the traps that produce misleading results.

Rule Statement

For a measured or experimental value E and an accepted, theoretical, or expected value T, percent error is:

Percent error = |E − T| / |T| × 100%

The absolute value in the numerator ensures a positive magnitude. The absolute value in the denominator is essential when T is negative. In Excel, use:

=ABS(E2-T2)/ABS(T2)*100

or omit the *100 and format the cell as a percentage:

=ABS(E2-T2)/ABS(T2)

Report the final result to an appropriate number of significant figures. Do not round intermediate values. If T = 0, percent error is undefined; report absolute error or use a different relative measure. Percent error is a comparison to a reference, not a measurement uncertainty under the GUM.

Worked Examples

Example 1: Density

Expected density: 7.874 g/cm³. Measured density: 7.83 g/cm³.

1. Difference: 7.83 − 7.874 = −0.044 g/cm³.
2. Absolute difference: 0.044 g/cm³.
3. Relative error: 0.044 / 7.874 = 0.005588…
4. Percent error: 0.005588… × 100 = 0.5588…%.
5. Report: 0.56% (two significant figures) or 0.6% (one significant figure), depending on the uncertainty and the precision of the inputs.

Excel: =ABS(7.83-7.874)/ABS(7.874)*100 returns 0.5588…

Example 2: Volume

Expected volume: 100.0 mL. Measured volume: 99.8 mL.

Percent error = |99.8 − 100.0| / |100.0| × 100 = 0.2 / 100.0 × 100 = 0.2%.

Because the difference 0.2 has one significant figure, the percent error is best reported as 0.2%. If the measurement uncertainty is known, report the percent error to the same decimal place as the uncertainty.

Example 3: Negative expected value

Expected temperature: −20.0 °C. Measured temperature: −19.5 °C.

Excel: =ABS(-19.5-(-20.0))/ABS(-20.0)*100 = 0.5 / 20.0 × 100 = 2.5%.

Without the absolute value in the denominator, the result would be −2.5%, which is incorrect for a magnitude-based percent error.

Counter-Examples

• Wrong denominator: =ABS(A2-B2)/B2*100 fails when B2 is negative. Use ABS(B2).
• Dividing by measured value: =ABS(A2-B2)/A2*100 is not percent error against a true value; it is a different relative comparison.
• Rounding too early: Rounding 0.5588% to 0.56% and then using 0.56 in a later calculation propagates error. Keep full precision until the final report.
• Confusing percent error with percent difference: For measured 105 and expected 100, percent error is 5%. Percent difference using the average denominator is |105−100| / ((105+100)/2) × 100 = 4.878%. They are not interchangeable.
• Treating percent error as uncertainty: A small percent error does not prove low measurement uncertainty. The GUM separates error from uncertainty.
• Using Excel ROUND inside the formula: =ROUND(ABS(E2-T2)/ABS(T2)*100,2) may be acceptable for final display, but it uses Excel’s half-away-from-zero rule, which can conflict with ISO 80000 and ASTM E29 ties-to-even rounding.

Convention Comparison Table

Standards Citation

Precision and rounding are not matters of taste. The following standards are directly relevant:

• ASTM E29-13(2019), Standard Practice for Using Significant Digits in Test Data to Determine Conformance with Specifications, Section 6: round only after all arithmetic is complete; do not round intermediate values.
• ISO 80000-1:2009, Quantities and units — Part 1: General, Clause 7.3.3: specifies rounding rules, including ties-to-even for exact halfway cases.
• JCGM 100:2008 (GUM), Evaluation of measurement data — Guide to the expression of uncertainty in measurement, Clause 7.2.6: numerical values of an estimate and its uncertainty should not be given with an excessive number of digits.
• NIST Technical Note 1297, Guidelines for Evaluating and Expressing the Uncertainty of NIST Measurement Results, Section 7.1: report uncertainty to at most two significant digits.
• ISO/IEC 17025:2017, Clause 7.8.3: reporting requirements for accredited testing and calibration laboratories, including uncertainty statements where relevant.

These standards reinforce a simple workflow: calculate with full precision, then round the final reported value according to the applicable significant-figure and uncertainty rules. See our rounding rules and error propagation references for deeper guidance.

Common Mistakes

• Omitting ABS around the denominator when expected values can be negative.
• Multiplying by 100 and also applying Excel’s Percentage format, which multiplies by 100 again for display.
• Using ROUND in intermediate cells and then referencing those rounded cells.
• Reporting 0.5588% as 0.5588% when only two significant figures are justified.
• Assuming Excel’s displayed value is the stored value. Excel stores the full double-precision number unless you change the workbook setting or use ROUND.
• Using percent error when the expected value is zero or when no true reference exists.
• Confusing percent error with percent deviation, percent difference, or uncertainty.
• Ignoring the difference between Excel’s half-away-from-zero rounding and ISO/ASTM ties-to-even rounding.

Software Behavior Note

Excel uses IEEE 754 double-precision floating-point arithmetic. Most decimal fractions cannot be represented exactly in binary, so a formula that mathematically equals 0.559 may display as 0.558999999999999. This is a representation artifact, not a rounding instruction. Excel’s ROUND function rounds half away from zero: ROUND(2.5,0) returns 3, while ISO 80000-1 and ASTM E29 ties-to-even rounding would return 2. For standards-compliant reporting, calculate at full precision and round only the final displayed value, or use a custom function that implements ties-to-even. Avoid the workbook option Precision as displayed, which permanently changes stored values and can corrupt subsequent calculations.

Quick Reference Table

Practice Problems

1. Expected acceleration due to gravity: 9.81 m/s². Measured: 9.75 m/s². Calculate percent error.
   Answer: |9.75 − 9.81| / 9.81 × 100 = 0.6116…% → 0.61%.
2. Expected concentration: 0.500 M. Measured: 0.512 M. Calculate percent error.
   Answer: |0.512 − 0.500| / 0.500 × 100 = 2.4%.
3. Expected value: −20.0 °C. Measured: −19.5 °C. Write the correct Excel formula.
   Answer: =ABS(-19.5-(-20.0))/ABS(-20.0)*100, which gives 2.5%.
4. Expected value: 0.00 V. Measured: 0.01 V. What is the percent error?
   Answer: Undefined, because the denominator is zero. Report absolute error: 0.01 V.
5. Why is =ROUND(ABS(E2-T2)/ABS(T2)*100,2) not always standards-compliant?
   Answer: Excel’s ROUND uses half-away-from-zero, while ISO 80000-1 and ASTM E29 specify ties-to-even in many contexts.