Description
Use this function to truncate a number to a specified number of digits. Supported in Chains.
TRUNC removes the fractional part of a number without rounding. It's useful for removing decimal places or extracting the integer part of a number.
Note: TRUNC differs from the ROUND function, in that TRUNC always truncates towards zero, while ROUND rounds up or down based on the digit to the right of the specified decimal place.
Syntax
TRUNC(number, [num_digits])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
number |
Yes | The number you want to truncate. | A real number, a reference to a cell containing a real number, or a formula which results in either of these. |
num_digits |
No | The number of digits to which you want to truncate the number. | An integer, a reference to a cell containing an integer, or a formula which results in either of these. If omitted, it's treated as 0. |
Example
Sample Data
| A | B | ||
|---|---|---|---|
| 1 | Number | Description | |
| 2 | 3.14159 | Pi | |
| 3 | -2.718 | Negative e | |
| 4 | 1234.5678 | Large number | |
| 5 | 0.00123 | Small number |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Drop the decimal portion of a number to get just its whole-number part, without rounding. | =TRUNC(A2) |
Removes everything after the decimal point in 3.14159 without rounding up or down. Result: 3 |
| Keep a set number of decimal places from a value without rounding it. | =TRUNC(A2, 2) |
Cuts 3.14159 off after 2 decimal places instead of rounding to the nearest hundredth. Result: 3.14 |
| Get the whole-number part of a negative value without rounding. | =TRUNC(A3) |
Truncation always moves toward zero, so −2.718 loses its decimal portion but isn't rounded down to −3. Result: −2 |
| Round a large number down to the nearest hundred by discarding the tens and ones digits. | =TRUNC(A4, -2) |
A negative num_digits truncates digits to the left of the decimal point, zeroing out the last two digits of 1234.5678.Result: 1200 |
| Keep a set number of decimal places from a very small value without rounding it. | =TRUNC(A5, 4) |
Cuts 0.00123 off after 4 decimal places rather than rounding the last digit. Result: 0.0012 |
Notes
- TRUNC always truncates towards zero, regardless of whether the number is positive or negative.
- If num_digits is greater than 0, TRUNC truncates to the specified number of decimal places.
- If num_digits is 0 or omitted, TRUNC truncates to an integer.
- If num_digits is negative, TRUNC truncates digits to the left of the decimal point.
- TRUNC does not round numbers; it simply removes the specified digits.
Tips
- Use TRUNC when you need to remove decimal places without rounding.
- TRUNC can be useful in financial calculations where you want to discard fractional cents.
- Combine TRUNC with other functions like INT or MOD for more complex number manipulations.
- Be aware of the difference between TRUNC and ROUND to choose the appropriate function for your needs.
- Consider using the other rounding functions (linked in Related functions) if you need to round numbers in a different manner.