How to Fix Excel Formulas Ignoring Tolerance Values in Comparisons
Question details
Users need to compare values within a specific tolerance limit in Excel (such as 0.01), but the formula fails to recognize them as equal due to floating-point precision errors.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two numerical values in a spreadsheet where slight mathematical differences should be treated as equal.
- Observed behavior
- Excel ignores the intended tolerance value because background floating-point arithmetic stores calculated numbers slightly differently than displayed, causing strict equality or tolerance checks to fail.
Identify the exact tolerance threshold you want to apply (e.g., 0.01) and determine if your cells contain calculated values rather than manually typed numbers, as calculations are highly prone to floating-point errors.
Use the ABS Function for a Less-Than-Or-Equal Comparison
Calculate the absolute difference between two values and check if it falls within your acceptable tolerance.
Because spreadsheets store numbers in binary format, complex calculations can introduce microscopic rounding errors known as floating-point precision issues. Using the absolute function (ABS) helps bypass this by verifying that the exact difference between two numbers does not exceed your specified limit.
Click on the cell where you want to output the result of your comparison.
Type your formula using the ABS function to find the difference. For example, use: =IFS(F3="","",ABS(F3-G3)<=0.01,1,F3>G3,3,F3<G3,0)
Update the cell references (like F3 and G3) and the tolerance value (0.01) to match the actual data in your spreadsheet.
Press Enter to execute the calculation. Drag the fill handle down to apply this logic to the rest of your data rows.

Round Values Before Applying Logical Comparisons
Use the ROUND function to eliminate floating-point discrepancies before comparing the numbers directly.
Handle Complex Calculations Accurately with WPS Spreadsheet
WPS Spreadsheet offers a high-precision calculation engine and full compatibility with advanced formulas, including IFS, ABS, and ROUND. Easily handle floating-point tolerances and logical comparisons without friction.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your tolerance comparison data.
- 2. Select the target cell: Click on the cell where you want to execute your logical test.
- 3. Input the formula: Type the exact same ABS or ROUND formula as you would normally use (e.g., =ABS(A1-B1)<=0.01).
- 4. Evaluate your numbers: Press Enter to calculate the results with perfect cross-platform formula compatibility.

Frequently Asked Questions
Why does Excel show two numbers as different when they look identical?
This happens due to floating-point precision. Spreadsheets store numbers with up to 15 significant digits. While a cell might visually display 1.01, the underlying value might actually be 1.010000000000001 due to binary calculation variations, causing strict equality checks to fail.
Can I fix floating-point precision globally in my spreadsheet?
Yes, you can enable 'Set precision as displayed' in your spreadsheet's advanced calculation options. However, use this feature with caution as it permanently alters the underlying data to match the displayed decimal places, which can affect the accuracy of future calculations.
What does the ABS function do in a tolerance formula?
The ABS (Absolute) function returns the absolute value of a number, turning any negative difference into a positive one. This ensures that whether the first cell is slightly larger or slightly smaller than the second cell, the absolute difference is correctly compared against your maximum tolerance limit.




