How to compare numbers but allowing a tolerance in Excel?
In Excel, using the standard formula =A1=B1 will directly compare the values in cell A1 and B1, and display TRUE only if they are exactly the same. However, in many practical scenarios—such as data analysis, quality control, or financial projections—you may need to determine if two numbers are "close enough" rather than exactly equal. This could be due to small rounding differences, acceptable measurement errors, or thresholds in calculation results.
For example, you might want to check whether the absolute difference between two results is within ±5 units. Excel does not provide a built-in direct function for comparing with a tolerance, but with formulas, custom functions, or add-ins, you can easily perform this type of comparison.
Compare two numbers but allowing a tolerance with formulas
Excel formulas allow you to compare two numbers and check whether the difference between them falls within a specific tolerance. This is particularly useful when you routinely work with approximate values or need to account for minor deviations.
1. In a blank cell next to the numbers you want to compare (for example, if you want to compare A1 and B1, you might use cell C1), enter any of the following formulas:
=AND(A1<=B1+5,A1>=B1-5)
=ABS(A1-B1)<=5
The first formula checks if A1 is between B1 -5 and B1 +5, while the second formula determines whether the absolute difference between A1 and B1 is less than or equal to 5. Either formula will return TRUE if the two numbers are within the specified tolerance, or FALSE otherwise.
Note: In these formulas, A1 and B1 represent the cells containing the numbers you wish to compare, and 5 is the tolerance range. You can replace "5" with any other value to meet your requirements, including using a cell reference for a flexible, per-row tolerance setting.
This method is fast and suitable for comparing a moderate number of values in a straightforward, tabular format. It is especially recommended when your comparison logic is simple, and you do not need to reuse the comparison formula across multiple places in your workbook.
2. Press Enter to confirm the formula. To apply this comparison to an entire column, drag the fill handle down (the small square in the bottom right corner of the cell) so the formula copies to adjacent rows. Each result will indicate TRUE if the compared values are within your specified tolerance, or FALSE otherwise, as shown below:
Tip: If you need to use varying tolerance values for each row, replace the number 5 with a reference to a cell (e.g., ABS(A1-B1)<=C1), where C1 contains the desired tolerance.
Precautions and troubleshooting: Ensure all referenced cells contain numerical values; text or blank values may cause errors or unexpected results. If the formula returns a #VALUE! error, check for invalid data types or extra spaces in your cells.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in