We can use the TRUNC function to remove the decimal part of a number and only get the integer portion. This post provides an step by step guide on how we can get the integer of a number.
Figure 1: Using TRUNC to get integer portion of a number
Syntax of the formula
Number refers to the value that we want to find its integer. We can also use a cell reference in the place of number.
The Excel TRUNC function is useful in slicing the decimal part of a number and returning its integer part. In our example above, the formula in B1 is as below;
When we press Enter, the formula will slice off the decimal part (.4578) and only returns its integer (4).
Copying the formula across the other cells:
- Select the cell with the formula, i.e. B1.
- Point the cursor to the bottom-left corner of the cell, until we see a plus sign.
- Hold the cursor down and drag it across the other cells.
Note, this function does not round off a number, it only removes the decimal portion.
We can supply a second argument to specify the precision of the truncation, though this is optional. When not supplied, the function treats it as zero. This means that the truncation will happen at the decimal point.
What of INT or ROUND functions?
With default settings, INT works the same way as TRUNC, when it comes to positive numbers. The only difference is that the INT function will round a number down to the next integer, before returning the integer portion of the number.
Figure 2: INT rounding off numbers away from zero
But for negative numbers, INT function deviates from how TRUNC works. The INT function rounds numbers down away from zero, irrespective of the decimal value.
If we want to round a number to the next integer, whether positive or negative, then use the ROUND function. This function looks as below;
Figure 3: Using the ROUND function