Contact
12 programs
Explore the site

TRUNC in Zoho Sheet: Remove Decimal Digits Without Rounding

Mathematical Beginner Zoho Sheet

Shorten a number to a chosen decimal precision, understand negative values, and distinguish truncation from rounding.

Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Zoho Sheet. How formula examples are checked

Quick answer: =TRUNC(B2;1) keeps one digit after the decimal point and discards later digits without rounding. For B2 = -14.75, the mathematically expected value is -14.7. =TRUNC(-3.6) predicts -3, not -4: truncation toward zero is different from INT’s rounding down. The examples in this tutorial were independently calculated, not executed in Zoho Sheet.

TRUNC is useful when a business rule requires a fixed number of decimal places without rounding, such as preparing a provisional display or reproducing a legacy calculation method. Do not assume it is appropriate for taxes, invoices or regulated measurements; those calculations commonly require an explicit rounding policy rather than discarded precision.

Syntax from Zoho’s official documentation

=TRUNC(number; [places])
Argument Required? Meaning Example
number Yes Numeric value, expression or cell to truncate. B2, 0.2453
places No Count of decimal digits to retain; Zoho says it defaults to 0. 1, 2, 3

The brackets around [places] indicate an optional argument; do not type the brackets in a formula. The official Zoho TRUNC article gives =TRUNC(0.2453;2) → 0.24, =TRUNC(21.880432;3) → 21.88, and =TRUNC(-0.6642;1) → -0.6. The second output may be displayed as 21.88 despite three retained decimal positions because the third position is zero; numeric formatting and internal precision are distinct concepts.

Zoho’s published page does not explicitly specify the behavior of negative places, fractional places, extreme magnitudes or all numeric-text conversions. Excel documents additional behaviors in its own system, but they are not established as identical in Zoho. This tutorial therefore sticks to nonnegative integer place counts supported by the displayed Zoho examples.

Enter the practice data

Use the included CSV math-sign-and-rounding-practice.csv, which places signed numeric variances in B2:B9. The negative examples are particularly important because TRUNC and INT differ there.

Row Input B =TRUNC(B-row;1) independently expected
2 -14.75 -14.7
3 8.20 8.2
4 0 0
5 -3.60 -3.6
6 12.49 12.4
7 25.00 25
8 -0.04 0 (numeric zero; optional display sign is implementation-dependent)
9 5.20 5.2

Step by step: retain one decimal position

  1. Type Truncated to 1 decimal in E1.
  2. Enter =TRUNC(B2;1) into E2. The independently computed answer is -14.7, because the second decimal digit is dropped instead of causing a round to -14.8.
  3. Fill the formula from E2 down to E9 and compare the outputs with the table.
  4. Change the formula temporarily to =TRUNC(B2) and predict -14. The optional argument defaults to zero decimal places.
  5. Compare =TRUNC(B5) → -3 with =INT(B5) → -4 for B5 = -3.60. Choose based on the rule your report requires.
  6. If E3 displays 8.20 rather than 8.2, check the cell’s Number format. Two-digit display formatting does not mean the formula retained an additional meaningful digit.

This formula’s output is numeric and can be used in further arithmetic. Applying number formatting alone, by contrast, normally changes only the displayed representation while the underlying value retains more digits. If downstream arithmetic must use truncated values, put TRUNC in an explicit helper column.

Advanced: truncate a calculated unit rate

Suppose C2 is an item count of 25 and D2 is a divisor of 12 representing a batch size. To keep two decimal places of the count-per-batch rate without rounding, use:

=TRUNC(C2/D2;2)

The exact fraction 25/12 is about 2.08333; truncating to two decimal places predicts 2.08. For comparison, a quotient of 17/5 is 3.4 and remains 3.4 when truncated to two places. This is a truncated decimal, not the count of complete batches and not the remainder of a division.

For reporting where the divisor could be zero, the following adds an explicit guard:

=IF(D2=0;"Fix divisor";TRUNC(C2/D2;2))

With C2 = 25 and D2 = 12, the intended result is 2.08. With D8 = 0, the intended status is Fix divisor. The logical expression has not been tested in native Zoho Sheet, and its handling of unusual imported datatypes should be verified before adoption.

A second advanced use is keeping three decimal places in a calculated measurement: =TRUNC(21.880432;3) yields a numeric value equivalent to 21.880; the screen may show 21.88 until its decimal display format is adjusted. Treat the output as a number, not an automatically zero-padded text code.

TRUNC versus INT and ROUND

Input TRUNC(…;0) INT(input) Explanation
3.6 3 3 Both are 3 for this positive input.
-3.6 -3 -4 TRUNC moves toward zero; INT floors.
-0.04 0 -1 Tiny negative input illustrates the distinction.
12.49 12 12 Both return 12 for a positive value.

ROUND can increase or decrease a value according to its rounding rule, whereas TRUNC discards unwanted digits. ABS ignores the sign; MOD returns a division remainder. These do not perform the same operation. If your use case requires a financial rounding policy, do not silently substitute TRUNC for ROUND.

Troubleshooting and limitations

Observation Possible cause Next step
#VALUE! Input value or places is nonnumeric. Check CSV import types and locale decimal separator.
#NAME! Misspelled function or invalid named reference. Re-enter TRUNC and check names.
#REF! The referenced range or cell is invalid. Fix the cell reference.
You expected -4 but got -3 TRUNC removes digits toward zero. Use INT only if the rule is to round downward.
Output has extra displayed zeros Cell formatting fixes the number of displayed decimal places. Inspect formula output separately from Number format.
Negative or noninteger places gives an unexpected result Such cases are not established by the cited Zoho article. Run a native Zoho test and document the observed behavior before generalizing.

The Zoho help page includes generic possible errors including #N/A!, which is not evidence that TRUNC performs any sort of search. Deliberately test error handling on representative real inputs, not only clean sample numbers.

Excel and Google Sheets differences to keep in mind

Microsoft Excel has a similarly named TRUNC function, but the English examples commonly use commas instead of the semicolons in Zoho’s published syntax. Microsoft documents behavior beyond Zoho’s listed basic examples, particularly for place counts. Do not assume those extra cases work identically in Zoho until tested. Google Sheets also has TRUNC; shared names do not certify all input coercion, array behavior or precision limits.

Related guides: INT for floor rounding, MOD for remainders, ABS for magnitude and SIGN for direction. These are proposed review URLs; no new site content is published by saving this guide.

Official reference and verification

Verification status: Official documentation checked; example outcomes were independently reasoned or arithmetically checked where applicable. Not executed in Zoho Sheet.

  • No genuine Zoho Sheet screenshots are included; this guide does not use mock application images.