TRUNC
Cuts a number down to a set number of digits, toward zero, without rounding.
TRUNC(<number> [, <num_digits>])Returns: A number (a scalar).
What it does
TRUNC removes the digits past the number you choose, without rounding. It always moves toward zero, so TRUNC(2.99) is 2 and TRUNC(-2.99) is -2. Use TRUNC when a policy or a contract says to cut the extra digits off, rather than round them.
Syntax and arguments
- number: the value to truncate.
- num_digits (optional): the number of decimal places to keep. If it is left out, the result is a whole number.
Example
A contract states that a service fee is calculated as 3.5 percent of the billed amount and that the result is truncated to the cent, not rounded.
Service Fee = TRUNC(Invoices[BilledAmount] * 0.035, 2)
A fee of 12.349 becomes 12.34 under truncation, while ROUND would give 12.35. The policy and the contract match the model because the rule is written in the formula.
Example: a second, different use
Remove the cents from an amount to show whole dollars in a budget summary, keeping the sign for negative values:
Whole Dollar Budget = TRUNC([Budget Amount])
For a negative budget variance, TRUNC moves the figure toward zero, so -1234.56 becomes -1234. That is the same direction as the cut, not the floor.
Things to watch
- TRUNC does not round. A number of 9.99 truncated to one decimal place is 9.9, not 10.0.
- For positive numbers, TRUNC and ROUNDDOWN give the same answer. For negative numbers they differ, so check which one the rule uses.
- For a mathematical floor of a negative number, use INT, which goes further down.
- Truncating each row and summing can differ from summing and truncating once. Decide which order the source system uses.
- The digits argument is optional. Leave it out for a whole number.
In business terms
TRUNC matches contracts, tax tables and bank rules that say "truncate" or "drop the remaining cents." Using the right rule is what stops a reconciliation from showing a few cents of difference that nobody can explain.