The primary use of the TRUNC function is to truncate or remove the fractional part of a number. The function can be configured to remove any number after a set number of digits.
Contents:
Syntax
= TRUNC(number, [num_digits])number = the number you wish to round down to the nearest integer
[num_digits] = optional argument, a positive value specifying the precision of the truncation. The default value is 0.
Explanation
The TRUNC function is part of the "Math and Trigonometry" group of functions within Excel.
This function will truncate any number, positive or negative, to a specified digits place. The default is 0 or anything after the decimal point will be removed, but this can be changed with the num_digits argument.
Truncating a number is unlike rounding in that instead of rounding up or down either way, by truncating a number you simply remove the decimal portion of a number. For example, truncating 2.856 would just give you 2.
To truncate to different digits place, increase the num_digits argument. A value of 1, would truncate after 1 decimal place (2.856 becomes 2.8). A value of 2 would truncate after 2 decimal places (2.85) and so on.
A negative value can even be used to truncate numbers before the decimal point. For example, a num_digits value of -2 would truncate 7,456.5 to 7,400. Essentially rounding to the specified tens/hundreds/thousands... etc. place.
Notes:
- If the input is non-numerical, a #VALUE! error will be returned
- A blank cell will be returned as a 0
Different Methods of Rounding in Excel
There are a lot of different ways to round numbers, and it all depends on the end goal you're trying to reach. Here are a few different rounding functions and when you would want to use them:
ROUND - normal rounding, rounds a specified place
MROUND - rounds to the nearest specified multiple
ROUNDUP - rounds up away from 0, to the nearest specified place
CEILING.MATH - rounds up to the nearest integer or significant multiple
ROUNDDOWN -rounds down away from 0, to the nearest specified place
FLOOR.MATH - rounds down to the nearest integer or significant multiple
INT - rounds down to the nearest integer, returns an integer
TRUNC - truncates all decimal places to specified place
Examples
1. How to Truncate a Number
To easily truncate a number in Excel, we only need to look to the TRUNC function.
By feeding TRUNC any number, by either a cell reference or hardcoded number, we can return the same number truncated to the nearest integer.
= TRUNC(number)= TRUNC(B3)
2. How to Truncate a Number to a Specific Decimal Place
The TRUNC function can also be augmented to truncate a number to a specific decimal place.
This is useful in case we want to keep some remaining decimals and don't want to round our numbers.
Here is a list of the Num_Digits value and corresponding digit place:
0 - Truncates all decimals
1 - Truncates to the tenths place
2 - Truncates to the hundredths place
3 - Truncates to the thousandths place
4 - Truncates to the ten thousandths place
5 - Truncates to the hundred thousandths place
6 - Truncates to... etc.
= TRUNC(number, decimal_place)
= TRUNC(B3, C3)
3. How to Round a Number to the Nearest Ones, Tens, Hundreds, Thousands Place or Greater
The TRUNC function can also be used to truncate numbers before the decimal, allowing us to round any number to the nearest ones, tens, hundreds or greater place.
To do this, we must use negative values for the Num_Digits argument. By using negative values, we move the truncate location up past the decimal point into a large number.
Here is a list of the Num_Digits value and corresponding digit place:
0 - Truncates all decimals / ones place
-1 - Truncates to the tens place
-2 - Truncates to the hundred place
-3 - Truncates to the thousands place
-4 - Truncates to the ten thousands place
-5 - Truncates to the hundred thousands place
-6 - Truncates to... etc.
= TRUNC(number, decimal_place)
= TRUNC(B3, C3)