There’s an important difference between two seemingly similar Excel functions: Int() and Trunc() don’t always give the same results so we’ll explain how to choose wisely.
Both Int() and Trunc() ‘shorten’ a number by removing digits to the right of the decimal place. Int() rounds the number, Trunc() doesn’t. And Trunc() has an option to control how many digits to drop.
Int() rounds a decimal number down to the nearest integer. Int(123.456) = 123
Trunc() drops any fractional value Trunc(123.456) = 123
They seem the same but not if you’re using them with a negative number:
Int(-123.456) = -124
Trunc(-123.456) = -123
Let’s dig into Int() and Trunc() to understand what’s going on.
Int () details
= INT (number)
Number – refers to the real number you want to round down to the integer.
The input argument can be entered manually, as a cell reference or even result of another formula.
Int () In Action
Below is an example of finding the integer value of a positive number in Excel. INT(100.1) returns the value 100, and likewise with INT(100.9), however INT(99.9) returns the value of 99.
But if the number is negative, the INT function will round down the number away from zero.
Trunc () details
= TRUNC(number, [num_digits])
Number – refers to the number you want to truncate.
Num_digits – refers to a number specifying how precise the truncation is. Optional. The default value is zero.
The input argument can be entered manually, as a cell reference or even result of another formula.
Trunc () In Action
Below is an example of a positive number in Excel. TRUNC(0.45) returns the value 0, TRUNC(1.9) returns 1, and TRUNC(99.9) returns the value of 99.
If we change those values to negative, unlike the int() function, it’ll return the same values but with a minus.
Int() vs Trunc()
The Trunc () function in Excel truncates (cuts off) a number to an integer by getting rid of the fractional part of the number. Similar to Int(), it also returns an integer.
But, when it comes to negative numbers Trunc() and Int() are different.
For example, TRUNC(-5,3) will return -5, but INT(-5,3) returns -6 because -6 is the lower number.
To put it another way, Trunc() is short for Truncate or shorten and cuts off whatever is to the right of the decimal point.
Dates, time and duration, the truth in Excel
PERCENTRANK Excel Functions
Excel Function Keys explained
Excel’s Trunc() Function
