Function Problems

Fix Excel INT and TRUNC Functions Returning Incorrect Values

Steven ParkerSteven Parker Nov 2, 2026 870 views

Question details

The user needs to understand why the Excel INT and TRUNC functions return an integer that is one less than expected (e.g., 12 instead of 13) and how to fix the calculations.

How to Fix Excel INT and TRUNC Functions Returning Incorrect Values
Product
Excel
Device & OS
not provided
Scenario
Extracting integer parts from calculated decimal values or performing degree, minute, and second conversions.
Observed behavior
The functions return a value one integer lower than the displayed value because the underlying floating-point calculation produces a number slightly below the whole number (e.g., 12.9999999 instead of 13).
Before you start

Before applying a fix, try increasing the decimal places on your calculated cell to reveal its true underlying value, which often exposes the invisible floating-point discrepancy causing the issue.

Solution 1Recommended

Combine INT or TRUNC with the ROUND Function

Applying the ROUND function before extracting the integer corrects tiny floating-point inaccuracies.

Because spreadsheet software uses binary floating-point arithmetic, calculations can result in values like 12.99999999 instead of 13. Since INT and TRUNC simply remove or round down the decimal portion, they will output 12. Using ROUND first ensures the number is perfectly aligned before the integer extraction.

1
Select the target cell

Click on the cell where you want the corrected integer result to appear.

2
Enter the nested formula

Type the formula by nesting your original cell reference inside the ROUND function, and then wrap it in INT. For example: =INT(ROUND(A2, 8)).

3
Apply and fill

Press Enter to execute the formula, then click and drag the fill handle to apply the correct formula to the rest of the column.

Combine INT or TRUNC with the ROUND Function
Precision Rule: Rounding to 8 decimal places is usually sufficient to eliminate binary floating-point errors while safely preserving your genuine decimal data.
Powerful Spreadsheet Capabilities

Easily Manage Complex Formulas with WPS Spreadsheet

WPS Office provides a highly compatible, free spreadsheet tool that flawlessly handles advanced functions like INT, TRUNC, ROUND, and MOD. Resolve calculation precision issues effortlessly with its intuitive interface.

  1. 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the calculation errors.
  2. 2. Check decimal precision: Click the cell with the calculated value and click 'Increase Decimal' on the Home tab to verify the floating-point values.
  3. 3. Apply the nested formula: Type =INT(ROUND(A2, 8)) in the formula bar to accurately extract the integer, then press Enter to instantly fix the issue.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Handles floating-point calculations with enterprise-level accuracy.Lightweight software with fast calculation speeds for large datasets.Free built-in formatting tools to easily adjust decimal precision visibility.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my spreadsheet show 13.00 but the INT function returns 12?

Spreadsheets often round the display value for visual readability. If the actual underlying calculation evaluates to 12.9999999 due to binary floating-point arithmetic precision limits, the INT function simply truncates the decimals without rounding, resulting in 12.

What is the primary difference between the INT and TRUNC functions?

TRUNC simply chops off the fractional part of a number, moving it closer to zero (e.g., TRUNC(-4.8) becomes -4). INT mathematically rounds numbers down to the nearest integer (e.g., INT(-4.8) becomes -5). For positive numbers, they return the exact same result.

Can I fix the issue by formatting the cell to show zero decimal places?

No. Changing the cell format only changes how the number looks on the screen, not its actual underlying value. If you use that cell in another formula, the software will still use the full underlying decimal value, meaning INT will still return the lower integer.