Price bucket
=TRUNC(B2, -1) groups prices into ten-dollar bands for a simple histogram tab.
What is TRUNC in Google Sheets?
TRUNC chops off digits after a chosen decimal place without rounding up or down. TRUNC(3.99) is 3, not 4. Use negative place values to truncate to tens, hundreds, or thousands. It is common in pricing tiers, ID bucketing, and reports where rounding would misstate totals.
When to use it
Use TRUNC when policy says drop fractions, when grouping numbers into bins, or when you need consistent floor-style cuts without INT quirks on negatives.
When to skip it
Skip TRUNC when standard rounding is legally or financially required. Tax lines, currency settlement, and statistical averages usually need ROUND, not TRUNC.
How it works
-
1
Write =TRUNC(value) to drop all decimals from a positive number.
-
2
Add a second argument for places, for example TRUNC(A2, 2) keeps two decimals and cuts the rest.
-
3
Use negative places to truncate left of the decimal, such as TRUNC(1234, -2) for 1200.
-
4
Pair with ABS when you need symmetric behavior on negative inputs before reapplying sign.
-
5
Document TRUNC in model README tabs so finance reviewers know you are not rounding.
-
6
Compare sample rows against ROUND in a helper column before shipping executive reports.
Examples in Google Sheets
Hours without rounding up
=TRUNC(C2, 2) stores billable hours cut to hundredths when contracts forbid rounding up partial hours.
TRUNC vs INT on negatives
TRUNC(-3.7) returns -3 while INT(-3.7) returns -4. Pick the function that matches your accounting rule.
Build this without starting from a blank cell
Use a Better Sheets tool for TRUNC, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Using TRUNC when stakeholders expect ROUND, causing penny drift across long reports.
- Forgetting TRUNC on text-looking numbers that are still strings, which returns errors.
- Truncating before SUM instead of after, which changes totals versus truncating each line item.
- Negative place confusion, truncating to thousands when you meant two decimal places.
- Nesting TRUNC inside charts without checking that labels still make sense to readers.
Frequently asked questions
- TRUNC vs ROUND?
- TRUNC cuts digits off. ROUND moves to the nearest value at the chosen precision.
- TRUNC vs INT?
- Both drop decimals on positives. On negatives, INT rounds down toward negative infinity while TRUNC cuts toward zero.
- TRUNC with currency?
- Possible, but confirm finance policy. Many invoices require ROUND, not TRUNC.
- Can TRUNC return text?
- No. It returns a number unless the input is invalid.
- TRUNC in QUERY?
- You can wrap fields in QUERY selects when building grouped reports, but test spill ranges carefully.
- Truncate dates?
- Dates are serial numbers. TRUNC on a datetime drops time if stored as decimal fraction of a day.
- TRUNC with ARRAYFORMULA?
- Yes. Apply across columns when many rows need the same cut rule.
Related Tutorials
Watch how TRUNC works
How to Write a Function - Learn to Code in Google Sheets Part 2
Search Every Function in Google Sheets
Related glossary terms
Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →Google Sheets formulas
A formula is an instruction in a cell that calculates a result from other cells, ranges, or built-in functions. Every formula begins with =. When inputs change, the sheet recalculates so dashboards and models stay live without manual copy-paste. Formulas are the default automation layer inside the grid before Apps Script or add-ons.
Read guide →default values in Sheets
Default values fill gaps when a cell is empty or pre-populate new rows with standard choices. Use IFBLANK in formulas, data validation lists with a suggested first option, template row copy, or ARRAYFORMULA that writes a fallback until someone overrides manually.
Read guide →Done reading about TRUNC?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.