TRUNC function

The TRUNC function in Excel is used to truncate a number to a specified number of decimal places or to remove the fractional part of a number, essentially rounding it toward zero.

Unlike ROUND, which rounds numbers to the nearest value based on a given number of digits, TRUNC simply removes the decimal portion, keeping the integer part. This function is useful when you need to discard the fractional part of a number without rounding.

Syntax

=TRUNC(number, [num_digits])

Parameters

1. number (required): The number that you want to truncate.
2. num_digits (optional): The number of decimal places to which you want to truncate the number. If omitted, the function defaults to 0, truncating the number to an integer.
   • If num_digits is positive, it truncates to that number of decimal places.
   • If num_digits is 0, it removes the decimal portion and truncates the number to an integer.
   • If num_digits is negative, it truncates the number to the left of the decimal point (removing digits to the right of the decimal point).

Key Points

• No Rounding: The TRUNC function does not round the number. It simply removes the fractional part and keeps the integer part.
• Negative Numbers: The TRUNC function works with both positive and negative numbers. For negative numbers, it will still remove the decimal portion and move closer to zero.

Examples

1. Basic Example (Truncate to Integer): To truncate the number 5.67 to an integer (no decimals):

   =TRUNC(5.67)
   

   Result: 5 (The fractional part .67 is discarded, and the number is truncated to the integer).

2. Truncate to Specific Decimal Places: To truncate the number 5.6789 to 2 decimal places:

   =TRUNC(5.6789, 2)
   

   Result: 5.67 (The number is truncated after two decimal places).

3. Truncate to Negative Decimal Places (Truncate to Tens): To truncate the number 543.76 to the nearest ten:

   =TRUNC(543.76, -1)
   

   Result: 540 (The digit in the ones place is discarded, leaving the number truncated to the nearest ten).

4. Truncate a Negative Number: To truncate the number -5.67:

   =TRUNC(-5.67)
   

   Result: -5 (The decimal portion .67 is discarded, and the number is truncated toward zero).

5. Truncate to Zero Decimal Places: To truncate the number 12.9999 to zero decimal places:

   =TRUNC(12.9999, 0)
   

   Result: 12 (The fractional part .9999 is discarded, and the number becomes an integer).

Notes

• Default Behavior: If you do not specify the num_digits argument, the function will default to truncating the number to an integer.
• Negative num_digits: If num_digits is negative, Excel truncates the number by removing digits to the left of the decimal point. For example, TRUNC(1234.567, -2) results in 1200, truncating to the nearest hundred.
• Comparison with ROUND: The TRUNC function is different from the ROUND function because it does not round the number; it simply removes the decimal portion.

Related Functions

• ROUND: Rounds a number to a specified number of decimal places or significant digits.
• ROUNDUP: Rounds a number up, away from zero, to the nearest specified place value.
• ROUNDDOWN: Rounds a number down, toward zero, to the nearest specified place value.
• INT: Returns the integer portion of a number, rounding down to the nearest integer.
• CEILING: Rounds a number up, away from zero, to the nearest multiple of a specified value.
• FLOOR: Rounds a number down, toward zero, to the nearest multiple of a specified value.

The TRUNC function is typically used when you need to remove the decimal portion of a number without changing its sign or performing rounding, such as in financial calculations or data formatting.