How to Calculate Percent Error in Excel

Percent error tells you how far a measured or estimated value is from the true value, as a percentage of the true value. In Excel it takes one short formula with ABS, plus percentage formatting. This guide is for students writing up lab results and for analysts checking forecasts against actuals, and it shows the formula, a worked example and the mistakes that give wrong answers.

What is percent error?#

Percent error is the size of the error divided by the true value:

Percent error = |measured − true| / |true| × 100%

• Measured value: what you got, such as a lab reading or a forecast.
• True value: the accepted or actual value you compare against. Some textbooks call it the theoretical or exact value.
• The bars | | mean absolute value: the size of the number without its sign. So an error of −0.11 counts as 0.11.

A percent error of 0% means a perfect match. 5% means the measurement was off by 5% of the true value, in either direction.

The percent error Excel formula#

Put the measured value in B2 and the true value in C2, then type in D2:

=ABS(B2-C2)/ABS(C2)

With 2.65 measured and 2.70 true, Excel calculates 0.05 / 2.70 = 0.0185185…. Press Ctrl+Shift+% (Mac: ⌃⇧%) to show it as 2%, then click Increase Decimal twice to see 1.85%.

Each part has a job:

1. B2-C2 is the error: −0.05.
2. ABS(...) removes the sign, giving 0.05. The guide to absolute value in Excel explains ABS.
3. /ABS(C2) divides by the true value. ABS on the true value only matters if it can be negative, such as a temperature below zero.

Excel works out what is inside brackets first, which is why B2-C2 must sit inside ABS before the division. If writing formulas like this is new to you, the first formulas lesson covers the basics of operators and brackets.

Worked example: lab measurements#

Here are four density readings for a material with an accepted value of 2.70 g/cm³:

Type the formula in D2, then fill it down to D5 (Windows: Ctrl+D; Mac: ⌘D). Because the accepted value is the same for every row, you could also type it once in a cell such as G1 and lock it: =ABS(B2-$G$1)/ABS($G$1).

S3 is the closest reading (0.37%) and S4 the furthest (4.07%). To find the worst error in one cell, use =MAX(D2:D5), which returns 4.07%.

How to round percent error#

Use ROUND on the fraction, not on a number you multiplied by 100. To keep two decimal places of a percentage, round to four decimal places:

=ROUND(ABS(B5-C5)/ABS(C5),4)

This returns 0.0407, which shows as 4.07% with percentage format. Rounding to 2 would give 0.04, or 4%. The ROUND function guide and the overview of ROUND, ROUNDUP and ROUNDDOWN explain the second argument.

Only round when the rounded value is what you need, such as a value copied into a report. For averages and further maths, keep the full value and change only the display.

Signed percent error: too high or too low?#

Leave out ABS when you need to know the direction of the error:

=(B2-C2)/C2

S1 gives −1.85% (too low) and S2 gives +2.96% (too high). This is the same formula as percent change, with the true value as the starting point. The percent change formula guide covers that view.

Signed errors are useful for spotting bias. If most readings are negative, the method or instrument may read low. But don't average signed errors to judge accuracy: a +5% and a −5% average to 0% even though both are wrong.

Percent error is not the same as percent difference. Percent difference compares two values when neither is the true one, and divides by their average. The article on percent change vs percent difference shows when to use each.

Average percent error across many rows#

To summarise accuracy across many readings or forecasts, average the absolute percent errors:

=AVERAGE(D2:D5)

For the four samples, the result is 2.31%. When the rows are forecasts compared with actual results, this measure is called the mean absolute percentage error (MAPE). For example, a forecast of 1,150 against an actual of 1,000 has a percent error of 15%; average that across every month to get the MAPE.

In Microsoft 365 you can skip the helper column: =AVERAGE(ABS(B2:B5-C2:C5)/ABS(C2:C5)) returns the same 2.31%. In older Excel, a helper column is easier to check.

Common percent error mistakes#

These slips cause most wrong answers:

1. Dividing by the measured value. =ABS(B2-C2)/B2 gives a slightly different, wrong number. Always divide by the true value.
2. Missing brackets. =ABS(B2)-C2/C2 subtracts 1 from B2. Keep the subtraction inside ABS.
3. Multiplying by 100 and then formatting as a percent. That shows 185% instead of 1.85%. Do one or the other, not both. The percentage formula guide covers this trap.
4. A true value of zero. Dividing by 0 returns #DIV/0!. Use =IF(C2=0,"",ABS(B2-C2)/ABS(C2)) or a different measure.
5. Averaging signed errors. Positive and negative errors cancel out. Average the absolute values instead.

Practise it#

Percent error is a percentage calculation with one extra step, so build the habit with real practice. Practise percentages and percent change in a live spreadsheet: you write the formula, format it and fill it down, and each answer is checked instantly. If brackets and operators still feel shaky, start with your first formulas.