Spreadsheet Practice

How to calculate percent error in Excel

Formula (Excel and Google Sheets)

=ABS(B2-$F$1)/$F$1

Divide the absolute difference by the accepted value: =ABS(B2-$F$1)/$F$1, formatted as a percentage.

Result on the example
one result per row in C2, filled down to C6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in C2, filled down to C6. Change any input and the result updates, or edit the formula to experiment.

How it works

Percent error is |measured − accepted| ÷ accepted. Trial 1 is off by 0.19, and 0.19 ÷ 9.81 is 1.94%. ABS keeps the error positive whether the measurement was high or low, and the percentage format multiplies by 100 for display.

=ABS(B2-$F$1)/$F$1

Result0.01936799185

  1. B29.62

    The value in B2.

  2. $F$19.81

    The value in F1.

  3. B2-$F$1-0.19

    The - operator subtracts.

  4. ABS(B2-$F$1)0.19

    ABS: Absolute value.

    • numberB2-$F$1-0.19
  5. ABS(B2-$F$1)/$F$10.01936799185

    The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =ABS(B2-$F$1)/$F$1. We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=ABS(($F$1-B2)/$F$1)1.94%, 0.41%, 2.34%…The same calculation with ABS around the whole fraction.

Common mistakes

  • Dividing by the measured value instead of the accepted one gives a slightly different, wrong percentage.
  • Multiplying by 100 and also formatting as a percentage shows 194% instead of 1.94%. Do one or the other.
  • If the accepted value is 0, percent error is undefined and the formula returns #DIV/0!.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate percent error

In C2, calculate the percent error of the measurement against the accepted value in F1, then fill down to C6.

Questions

How do I keep the sign to see whether a measurement was high or low?

Drop ABS: =(B2-$F$1)/$F$1 is negative when the measurement is low.

How do I calculate the average percent error?

Average the column, =AVERAGE(C2:C6), or in one formula =SUMPRODUCT(ABS(B2:B6-F1))/F1/COUNT(B2:B6).

What is the difference between percent error and percentage difference?

Percent error compares with a known true value; percentage difference compares two measurements, dividing by their average.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.