How to Calculate Absolute and Relative Error in Excel: Rules, Formulas, and Precision Standards

Excel is often used to compare experimental results with an accepted value, but the calculation is only as good as the definitions behind it. This reference explains absolute error, relative error, and percent error, shows exact Excel formulas, and connects the arithmetic to measurement standards such as the GUM and ASTM E29.

Key convention: absolute error = |measured − reference|; relative error = |measured − reference| / |reference|; percent error = relative error × 100. Excel implements these with ABS, IFERROR, and standard cell references.

Rule Statement

Let x be the measured or experimental value and x_ref be the reference value. The reference may be a true value, accepted value, nominal value, or theoretical value. For a nonzero reference:

• Signed error: E = x − x_ref. This preserves direction and units.
• Absolute error: |E| = |x − x_ref|. This is a magnitude in the same units as x.
• Relative error: |E| / |x_ref| = |x − x_ref| / |x_ref|. This is dimensionless.
• Percent error: 100 × |x − x_ref| / |x_ref|.

In Excel, if measured values are in B2 and reference values in C2:

• Signed error: =B2-C2
• Absolute error: =ABS(B2-C2)
• Relative error: =ABS(B2-C2)/ABS(C2)
• Relative error with zero-safe handling: =IFERROR(ABS(B2-C2)/ABS(C2), ‘undefined’) or =IF(C2=0, ‘undefined’, ABS(B2-C2)/ABS(C2))
• Percent error: =ABS(B2-C2)/ABS(C2)*100, or format the relative-error cell as a percentage.

If the reference value is the same for every row, anchor it with absolute references: =ABS(B2-$C$2)/ABS($C$2). Apply rounding and significant-figure rules only after the error calculation, not before. See Rounding Rules and Significant Figures.

Standards Citation

Metrology standards do not prescribe Excel formulas, but they define the concepts that Excel formulas must respect. The International Vocabulary of Metrology defines measurement error as the measured quantity value minus a reference quantity value (JCGM 200:2012, VIM, entry 2.16). The Guide to the Expression of Uncertainty in Measurement (GUM) treats error and uncertainty as distinct: error is a difference from a true value, while uncertainty characterizes the dispersion of values that could reasonably be attributed to the measurand (ISO/IEC Guide 98-3:2008, Clause 3). NIST Technical Note 1297 gives practical guidance for expressing uncertainty in NIST measurement results.

For significant digits and conformance decisions, ASTM E29-22 provides a standard practice for using significant digits in test data to determine conformance with specifications. ISO 80000-1:2009 establishes general principles for quantities, units, and numerical values. A precision reference site should therefore treat absolute and relative error as definitions, and uncertainty as the separate quantitative statement of confidence.

Absolute error answers ‘how far off in measurement units?’ Relative error answers ‘how large is that miss compared with the reference?’ Percent error is a presentation convention, not a different physical quantity.

Worked Examples

Example 1: Mass measurement

A calibrated reference mass is 10.000 g. An analytical balance reads 10.042 g. Measured x = 10.042 g; reference x_ref = 10.000 g.

1. Signed error: 10.042 − 10.000 = 0.042 g.
2. Absolute error: |0.042| = 0.042 g.
3. Relative error: 0.042 / 10.000 = 0.0042.
4. Percent error: 0.0042 × 100 = 0.42%.

Excel, with B2 = 10.042 and C2 = 10.000: absolute error =ABS(B2-C2) returns 0.042; relative error =ABS(B2-C2)/ABS(C2) returns 0.0042, displayed as 0.42% when formatted as a percentage. Because the difference uses subtraction, the result 0.042 g has two significant figures; the relative error 0.42% also has two significant figures.

Example 2: Density comparison

Accepted density of a metal is 2.70 g/cm³. A student obtains 2.65 g/cm³.

1. Absolute error = |2.65 − 2.70| = 0.05 g/cm³.
2. Relative error = 0.05 / 2.70 = 0.018518…
3. Percent error = 1.8518…%, which should be rounded to 1.9% if the inputs justify two significant figures, or 1.85% if the reference value is treated as having three.

Excel formula: =ABS(B3-C3)/ABS(C3). To display a percent without multiplying by 100, format the cell as Percentage. If the formula already multiplies by 100, format as General or Number but add the percent sign in text.

Example 3: Same reference for many measurements

If the true value is fixed in C2 and measurements fill B2:B100, enter in D2: =ABS(B2-$C$2) and in E2: =ABS(B2-$C$2)/ABS($C$2). Fill down. The dollar signs prevent Excel from changing the reference row.

Counter-Examples

These are common Excel constructions that produce mathematically wrong or misleading error values.

• Wrong denominator: Using =ABS(B2-C2)/B2 when B2 is the measured value. If measured = 9.8 and reference = 10.0, this gives 0.2/9.8 = 2.04%, but the correct relative error against the reference is 0.2/10.0 = 2.00%. The denominator must be the reference, not the measurement.
• Missing absolute value on the denominator: =ABS(B2-C2)/C2 can return a negative relative error if C2 is negative, even though the numerator is a magnitude. Use ABS(C2) for a magnitude ratio.
• No zero guard: =ABS(B2-C2)/C2 returns #DIV/0! when the reference is zero. Relative error is undefined at zero; use IFERROR or a custom message.
• Rounding before dividing: Rounding 0.042 to 0.04 and then dividing by 10.000 gives 0.40% instead of 0.42%. In Excel, keep full precision in the cell and round only for final display.
• Confusing percentage difference: Percentage difference often uses the average of the two values in the denominator: |a−b| / ((a+b)/2) × 100. That is not relative error against a reference.

Convention Comparison Table

Common Mistakes

• Using the measured value as the denominator for relative error. The reference value is the denominator.
• Forgetting that percent formatting in Excel multiplies the displayed value by 100. A cell containing 0.0042 shown as a percentage reads 0.42%.
• Using ROUND inside the error formula and then reporting the rounded result as if it were exact. Rounding changes the stored value; use cell formatting for display when possible.
• Reporting more significant figures than the measurement supports. Absolute error inherits the precision of the subtraction; relative error inherits the limiting significant figures of the inputs.
• Treating relative error as uncertainty. Error is a difference from a reference; uncertainty is a dispersion parameter (GUM). They are related but not interchangeable.
• Assuming Excel stores exactly what it displays. Excel carries about 15 significant digits and can show floating-point artifacts such as 1.0000000000000002.
• Ignoring a zero or near-zero reference value, which makes relative error unstable or undefined.

Practice Problems

1. A thermometer reads 98.7 °C; the true temperature is 100.0 °C. Calculate absolute error, relative error, and percent error. Answer: absolute error = 1.3 °C; relative error = 1.3/100.0 = 0.013; percent error = 1.3%.
2. A part has nominal length 25.00 mm and measured length 24.82 mm. What Excel formula gives percent error if B2 is measured and C2 is nominal? Answer: =ABS(B2-C2)/ABS(C2)*100, or format =ABS(B2-C2)/ABS(C2) as a percentage. Result = 0.72%.
3. The reference value in C5 is zero. What should relative error show? Answer: It is undefined. Use =IFERROR(ABS(B5-C5)/ABS(C5), ‘undefined’) or state that relative error cannot be computed against a zero reference.
4. A student rounds an absolute error of 0.046 to 0.05 before dividing by a reference of 2.00. What is the mistake? Answer: Early rounding changes the relative error. Compute 0.046/2.00 = 0.023 first, then round for reporting.

Software Behavior Note

Excel’s ABS function returns the absolute value of a number and is the correct tool for magnitude. IFERROR catches division-by-zero and other errors without hiding logic mistakes. ROUND(number, digits) changes the stored value, while number formatting only changes the display. For sig-fig display, custom formats or a TEXT presentation can help, but calculations should normally use unrounded cells. Excel stores numbers in binary floating point; values such as 0.1 and 0.2 may not be represented exactly. For ordinary error calculations this is negligible, but for conformance decisions, use a tolerance and round only at the final comparison. See Excel Floating-Point Errors and Rounding vs Significant Figures.

Quick Reference Table