In quantitative finance, programming logic, and operational reporting, analysts often need to extract the whole number component of a value or truncate digits at an exact decimal position without altering the preceding figures. While conventional rounding functions like ROUND, ROUNDUP, or ROUNDDOWN apply mathematical rounding rules, the TRUNC function simply discards unwanted trailing digits, preventing unwanted boundary adjustments.
Syntax and Argument Structure
The TRUNC function accepts a numeric value and an optional place parameter:
=TRUNC(value, [places])- value: The numeric constant, formula result, or cell reference to truncate.
- places (optional): The number of decimal positions to retain. The default value is
0(yielding an integer). A positive value truncates to that many decimal places to the right of the decimal point; a negative value replaces digits to the left of the decimal point with zeros.
TRUNC vs. INT and ROUNDDOWN
Understanding the distinction between TRUNC and related functions is essential when working with negative figures:
| Value | =TRUNC(value) |
=INT(value) |
=ROUNDDOWN(value, 0) |
Behavioural Difference |
|---|---|---|---|---|
8.85 |
8 |
8 |
8 |
Identical behaviour for positive numbers. |
-8.85 |
-8 |
-9 |
-8 |
INT rounds down to the next lower integer; TRUNC strips decimals towards zero. |
1245.8 (places: -2) |
1200 |
N/A (no places arg) | 1200 |
TRUNC supports negative digit precision to zero out tens and hundreds. |
Practical Business Use Cases
- Extracting Pure Integer Years or Months: When calculating elapsed age or tenure from fractional year differences (such as
3.97years),ROUNDwould prematurely advance the count to 4 years. UseTRUNC:
This ensures an employee or asset is strictly credited for completed years.=TRUNC(YEARFRAC(A2, B2)) - Financial Currency Precision Without Upward Bias: In cryptocurrency tracking or micro-transaction auditing, truncate exchange rates to four decimal places without rounding:
A value of=TRUNC(A2, 4)0.008492becomes0.0084, avoiding inflated asset valuations. - Zeroing Trailing Significance (Negative Places): Truncate large inventory serial numbers or revenue figures to the nearest hundred without upward adjustment:
A revenue figure of=TRUNC(A2, -2)58,490truncates to58,400. - Dynamic Multi-Row Processing: Use
ARRAYFORMULAto process complete columns:=ARRAYFORMULA(IF(ISBLANK(A2:A), , TRUNC(A2:A, 2)))
Formatting vs Truncation Caution
Hiding decimal places using the Google Sheets toolbar (“Decrease decimal places”) only changes cell visual formatting while retaining the full underlying floating-point precision in calculations. In contrast, TRUNC permanently modifies the calculation value, preventing fractional cent discrepancies in subsequent sum formulas.