# How to check if a value is within a percentage of a target in Excel

Compare the relative difference with the tolerance: `=IF(ABS(C2-B2)/B2<=$F$1,"OK","Check")`.

**Excel:** `=IF(ABS(C2-B2)/B2<=$G$1,"OK","Check")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| PO | PO amount | Invoice amount | Result |  | Tolerance | 0.05 |
| PO-301 | 1000 | 1030 |  |  |  |  |
| PO-302 | 2500 | 2690 |  |  |  |  |
| PO-303 | 480 | 456 |  |  |  |  |
| PO-304 | 150 | 165 |  |  |  |  |
| PO-305 | 7200 | 7056 |  |  |  |  |

Formula in D2, filled to D6: `=IF(ABS(C2-B2)/B2<=$G$1,"OK","Check")`

Result: OK; Check; OK; Check; OK

## How it works

`ABS(C2-B2)/B2` is how far the invoice is from the PO, as a share of the PO, ignoring direction. If that is at most 5%, the invoice passes. PO-302 is 7.6% over and PO-304 10% over, so they need checking; PO-303 is exactly 5% under and passes.

## Other ways

- `=IF(AND(C2>=B2*(1-$G$1),C2<=B2*(1+$G$1)),"OK","Check")`: Builds the lower and upper limits explicitly, which is easier to show to auditors.

## Common mistakes

- Values exactly on the limit can fail because of floating-point rounding. If that matters, round the ratio: `ROUND(ABS(C2-B2)/B2,6)`.
- Divide by the target (the PO), not the invoice, or the tolerance shifts with the error.

## Questions

**How do I allow 5% over but nothing under?**

Test one side: `=IF(AND(C2>=B2,C2<=B2*(1+$G$1)),"OK","Check")`.

**How do I use a fixed amount instead of a percentage?**

Compare the absolute difference: `=IF(ABS(C2-B2)<=25,"OK","Check")`.

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/check-if-within-a-percentage-tolerance