# How to calculate percent error in Excel

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

**Excel:** `=ABS(B2-$F$1)/$F$1` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Trial | Measured g (m/s²) | Percent error |  | Accepted value | 9.81 |
| 1 | 9.62 |  |  |  |  |
| 2 | 9.85 |  |  |  |  |
| 3 | 10.04 |  |  |  |  |
| 4 | 9.71 |  |  |  |  |
| 5 | 9.79 |  |  |  |  |

Formula in C2, filled to C6: `=ABS(B2-$F$1)/$F$1`

Result: 1.94%; 0.41%; 2.34%; 1.02%; 0.20%

## 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.

## Other ways

- `=ABS(($F$1-B2)/$F$1)`: 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!.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-percent-error