Calculation Issues

How to Fix Excel Formulas Ignoring Tolerance Values in Comparisons

Kushani NimanthikaKushani Nimanthika Nov 10, 2026 868 views

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.

How to Fix Excel Formulas That Ignore Tolerance Values
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click on the cell where you want to output the result of your comparison.

2
Enter the ABS tolerance formula

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)

3
Adjust references and tolerance

Update the cell references (like F3 and G3) and the tolerance value (0.01) to match the actual data in your spreadsheet.

4
Apply the formula

Press Enter to execute the calculation. Drag the fill handle down to apply this logic to the rest of your data rows.

Use the ABS Function for a Less-Than-Or-Equal Comparison
Adjusting the Tolerance Limit: If the standard 0.01 tolerance still fails due to background decimals exceeding the limit, try using a slightly larger tolerance threshold, such as 0.011, to capture those microscopic differences.

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your tolerance comparison data.
  2. 2. Select the target cell: Click on the cell where you want to execute your logical test.
  3. 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. 4. Evaluate your numbers: Press Enter to calculate the results with perfect cross-platform formula compatibility.
Fully compatible with all Microsoft Excel formulas, functions, and formats (XLSX).High-precision calculation engine reduces unexpected mathematical errors.Free and lightweight alternative for seamless data analysis and logical testing.
microsoft office alternative - wps office

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.