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

Q: How do I allow 5% over but nothing under?

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

Q: How do I use a fixed amount instead of a percentage?

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

Formula (Excel and Google Sheets)

=IF(ABS(C2-B2)/B2<=$G$1,"OK","Check")

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

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

See it work

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

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.

=IF(ABS(C2-B2)/B2<=$G$1,"OK","Check")

Result"OK"

C2$1,030.00
The value in C2.
B2$1,000.00
The value in B2.
C2-B230
The - operator subtracts.
ABS(C2-B2)30
ABS: Absolute value.
- numberC2-B230
ABS(C2-B2)/B20.03
The / operator divides.
$G$15%
The value in G1.
ABS(C2-B2)/B2<=$G$1TRUE
The <= operator tests at most.
IF(ABS(C2-B2)/B2<=$G$1,"OK","Check")"OK"
IF: Returns one value if a test is true, another if false.
- logical_testABS(C2-B2)/B2<=$G$1TRUE
- value_if_true"OK"
- value_if_false"Check"not used

In Google Sheets

The same formula works in Google Sheets: =IF(ABS(C2-B2)/B2<=$G$1,"OK","Check"). 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.

Formula	Result	When to use it
=IF(AND(C2>=B2(1-$G$1),C2<=B2(1+$G$1)),"OK","Check")	OK, Check, OK…	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.

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.

Check if a value is within a percentage of a target

In D2, show "OK" if the invoice is within the tolerance in G1 of the PO amount, otherwise "Check", then fill down to D6.

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").

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.