How to check if a value is within a tolerance in Excel

See it work

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

How it works

In Google Sheets

The same formula works in Google Sheets: =IF(ABS(B2-$F$1)<=$F$2,"Pass","Fail"). 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.

Common mistakes

• Values exactly on the limit can fail because decimals aren't stored exactly: 25.05-25 is 0.0500000000000007. Round the difference, as in the second alternative.
• A percentage tolerance needs the target in it: ABS(B2-$F$1)<=$F$1*2%.

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.

Related answers

• How to check if a value is within a percentage of a target in ExcelCompare the relative difference with the tolerance: =IF(ABS(C2-B2)/B2<=$F$1,"OK","Check").
• How to check if a cell is numeric in ExcelUse ISNUMBER inside IF: =IF(ISNUMBER(B2),"OK","Fix"). It is TRUE only for real numbers, not text that looks like one.
• How to check if a date is past due in ExcelFlag unpaid rows whose due date is before today: =IF(AND(C2="",B2<$G$1),"Overdue",""), with =TODAY() or an as-of date in G1.
• How to check if a number is between two values in ExcelUse AND with both limits: =IF(AND(value>=low,value<=high),"In range","Out of range").
• How to check if neither of two conditions is true in ExcelWrap OR in NOT: =IF(NOT(OR(B2="Yes",C2>=$G$1)),"Reject","Review") is TRUE only when both conditions fail.
• How to flag dates that are due in the next 30 days in ExcelTest that the date is on or after today and no later than today plus the window: =IF(AND(B2>=$F$1,B2<=$F$1+$F$2),"Renews soon","").

Functions used: IF · ABS · AND · ROUND

Lessons: The IF function · AND, OR & IFS · LET (Pro)