To calculate the difference between an expected value and a measured value in Excel, subtract the expected value from the measured one; to show the size of the error regardless of direction, use the absolute difference. For example, with the expected value in B2 and the measured value in C2, enter =C2-B2 for signed error or =ABS(C2-B2) for absolute error. This guide shows how to calculate both, express error as a percentage, summarize several results, and handle the different meaning of statistical standard error.
What You’ll Need
- Excel for Microsoft 365 on Windows. The basic formulas also work in recent desktop versions of Excel and Excel for the web.
- A worksheet with a clear expected or reference value and a corresponding measured, observed, or predicted value for each comparison.
- Agreement on what the reference value is. Percentage error is usually measured against the expected value, but a project or industry may define the denominator differently.
Step-by-Step
- Set up and label your data. Put expected values in column B and measured values in column C. In row 1, label the columns “Expected” and “Measured”; use row 2 for the first pair. Keep each pair on the same row, and avoid blank cells or text in the data range. For a worked example, enter expected values of 100, 50, 80, 120, and 40 in B2:B6, and measured values of 96, 55, 72, 126, and 38 in C2:C6. Each row is one comparison. These labels and matching rows make it easier to check that you have not compared a measurement with the wrong target.
- Calculate signed error. Signed error preserves direction: a negative result means the measurement is below the expected value, and a positive result means it is above. With the expected value in B2 and measured value in C2, enter
=C2-B2in D2. For the first example, the result is -4 because 96 minus 100 equals -4. Copy the formula down column D by dragging the fill handle, the small square at the bottom-right corner of the selected cell. The five signed errors are -4, 5, -8, 6, and -2. Keep this signed column if you need to see whether results tend to be high or low. - Calculate absolute error when direction is not needed. Absolute error reports the size of the difference as a nonnegative number. With expected values in B2 and measured values in C2, enter
=ABS(C2-B2)in E2, then fill it down. The first result is 4, not -4; the five absolute errors in the example are 4, 5, 8, 6, and 2. This is useful for describing how far results are from their targets without allowing positive and negative differences to cancel each other out. - Calculate percentage error. Percentage error puts the difference in context by dividing absolute error by the magnitude of the expected value. With expected values in B2 and measured values in C2, enter
=ABS(C2-B2)/ABS(B2)in F2. For the first example, the result is 0.04, or 4%. Select F2:F6 and choose Home > Number > Percent Style to display the results as percentages; adjust the decimal places if needed. The five results are 4%, 10%, 10%, 5%, and 5%. This formula uses absolute error, so it describes the size of the percentage difference, not whether the measurement is high or low. - Make the percentage formula safe for a zero reference. Percentage error cannot be calculated in the ordinary way when the expected value is zero, because the calculation would divide by zero. With expected values in B2 and measured values in C2, use
=IF(B2=0,"",ABS(C2-B2)/ABS(B2))in F2 if you want the cell left blank for zero expected values. Fill down and format the column as a percentage. Decide how to report those blank rows separately; do not interpret them as zero percent error. If your work has a specific convention for zero references, use that convention and document it. - Summarize a set of comparisons. The mean absolute error (MAE) is the average of the absolute differences, so it is expressed in the same units as the original measurements. With the absolute errors in E2:E6, enter
=AVERAGE(E2:E6)in a summary cell. The example returns 5 units: (4 + 5 + 8 + 6 + 2) divided by 5. To calculate root mean square error (RMSE), which gives more weight to larger differences, use the signed errors in D2:D6 and enter=SQRT(SUMSQ(D2:D6)/COUNT(D2:D6)). The result is about 5.39 units, because the mean of the squared errors is 29 and the square root of 29 is about 5.39. If you want mean absolute percentage error (MAPE), average the nonblank percentage results; with percentages in F2:F6 and no zero expected values, enter=AVERAGE(F2:F6). The example returns 6.8%. - Check whether you mean statistical standard error. “Error” can also mean the standard error of a sample mean, which estimates how much a sample average may vary. It is not the same as the difference between an expected value and a measurement. With a sample of observations in C2:C6, enter
=STDEV.S(C2:C6)/SQRT(COUNT(C2:C6))to calculate the standard error of the mean. For the example measurements 96, 55, 72, 126, and 38, the result is about 14.8. Use this calculation only when the values are a sample and that statistical measure is what you need; it does not measure prediction accuracy for each row.
Common Mistakes
- Reversing the subtraction without noticing.
=C2-B2means measured minus expected. Reversing it changes the sign, though not the absolute error. Pick a convention, label it, and use it consistently. - Using the wrong denominator for percentage error. Dividing by the measured value instead of the expected value gives a different percentage. State which value is the reference, especially when sharing results or comparing with another report.
- Expecting signed errors to show total error size. Positive and negative errors can cancel when averaged. For instance, a result of 5 and a result of -5 have an average signed error of zero, even though both differ from their targets. Use MAE or RMSE when you want an overall measure of error magnitude.
- Including a zero expected value in a percentage calculation. Division by zero produces an Excel error. Identify these cases and report them using an agreed rule rather than quietly treating them as zero percent.
- Confusing measurement error with a worksheet error. A value such as -4 can be a valid calculated difference. Excel messages such as #DIV/0! or #VALUE! indicate a formula or data issue, not the size of a real-world measurement error.
Tips
- Use descriptive headers such as “Expected,” “Measured,” “Signed error,” “Absolute error,” and “Percentage error.” A reader should not have to guess what a negative number or percentage represents.
- Keep the raw expected and measured values unchanged, and put calculations in separate columns. This makes formulas easier to audit and lets you correct source data without losing the original readings.
- Format percentage results as percentages rather than multiplying by 100 in the formula. Excel stores 4% as 0.04; percentage formatting changes its display without changing the underlying value.
- Check the first and last rows after filling formulas down. Excel normally adjusts relative references as formulas are copied, but a blank or misplaced value can still make the summary misleading.
- Choose a summary that fits the question. MAE is straightforward to interpret in the original units; RMSE emphasizes large misses; MAPE is a percentage but can be misleading when expected values are very small or zero.
- If your Excel regional settings use semicolons as formula separators, replace the commas in a formula with semicolons. The formula names and calculation are otherwise the same.
Troubleshooting
The formula displays #DIV/0!. Check whether the expected value used as the percentage denominator is zero. Use the guarded IF formula in Step 5 or apply a documented rule for zero-reference cases. Also check that the formula points to the expected-value column, not a blank cell.
The formula displays #VALUE!. One or both cells may contain text that looks like a number, such as a number imported with an unexpected decimal or thousands separator. Select the source cells and check whether Excel recognizes them as numbers. Convert valid numeric text to numbers before calculating, and remove labels or stray spaces from the data range.
The result is a decimal rather than a percentage. A result of 0.04 represents 4%. Select the result cells and choose Home > Number > Percent Style. If the displayed value is 400%, check that the formula does not already multiply the ratio by 100 before applying percentage formatting.
The summary differs from another report. Compare the definition, not just the cell values. Confirm whether the other calculation uses signed error, absolute error, squared error, a different denominator, or excludes certain rows. For MAPE, verify how zero and very small expected values are handled and whether blank rows are omitted.
Copied formulas show the same result on every row. Select a formula cell and inspect its references. For this guide’s formulas, the row references should change as the formula is filled down, such as B2 and C2 becoming B3 and C3. If references were made absolute with dollar signs, remove those signs where row-by-row adjustment is required.
Frequently Asked Questions
What is the simplest formula for error in Excel?
With the expected value in B2 and measured value in C2, use =C2-B2. It returns a signed difference, so the sign tells you whether the measurement is above or below the expected value.
How do I calculate absolute error in Excel?
With expected value in B2 and measured value in C2, use =ABS(C2-B2). The result is the size of the difference without a positive or negative direction.
Should percentage error be positive or negative?
It depends on what you need to communicate. The formula in this guide gives a nonnegative percentage showing the size of the difference. To show direction as well, calculate signed error separately or divide the signed difference by the expected value.
What is the difference between MAE and RMSE?
Both summarize multiple errors in the original measurement units. MAE averages absolute differences, while RMSE squares the errors before averaging and taking the square root, making larger errors count more heavily.
Can I calculate percentage error when the expected value is zero?
Not with the standard percentage formula, because it divides by the expected value. Report the case using a project-specific rule, or leave the formula result blank and explain why it is excluded.
Is standard error the same as error percentage?
No. Percentage error compares an individual measured value with a reference value. Standard error of the mean describes the estimated variability of a sample mean and uses the sample’s standard deviation and size.
Related
- How to Add Borders in Excel With Shortcut Keys
- Add a Zero Before a Number in Excel Without Losing It
- How to Add a Color Key in Excel for Clearer Worksheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- How to Fix a #REF! Error in Excel Without Losing DataTo remove a #REF! error in Excel, first make a copy of the workbook, then find the formula…
- Excel Row Numbers Missing? Causes and How to Restore Them in ExcelIf Excel’s row numbers have disappeared, first turn row headings back on: in Excel for Microsoft 365 on…
- Why Are My SUMIFS Not Working? Excel FixesWhen SUMIFS is not working in Excel, the most common causes are mismatched range sizes, criteria that do…
- Excel SUM Formula Not Working? Fix It Step by StepIf an Excel SUM formula shows zero, leaves out values, or does not update, first check that the…
- Excel Cell Pull-Down Lists: Create, Edit, and Fix ThemTo create an Excel cell pull-down list in Microsoft 365 for Windows, select the cell, choose Data >…
- Excel File Won’t Scroll? Causes and Fixes to Try FirstIf an Excel file will not scroll, first press Scroll Lock to turn it off, then check View…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.