How to Use TRUNC Function in Excel + tips

Today, I'm going to show you how to use the TRUNC function in Microsoft Excel. This formula helps you remove the decimal part of a number, leaving only the integer part. It's super useful for cleaning up data or performing specific calculations.

I'll explain the syntax, provide clear examples, and also include a video tutorial in Khmer to make it easier to understand. Let's dive in!

🛠️ Understanding the TRUNC Function

The TRUNC function in Excel removes the decimal part of a number, returning only the integer part. It essentially "truncates" the number, ignoring anything after the decimal point.

📋 Syntax

The syntax for the TRUNC function is straightforward:

=TRUNC(number, [digits])

Parameters:

• number (Required): The number you want to truncate. This can be a number, a cell reference containing a number, or a formula that returns a number.
• [digits] (Optional): The number of decimal places to which you want to truncate.
  • If omitted, it defaults to 0, meaning it will truncate to the nearest whole number (remove decimals).
  • If specified as a positive integer, it truncates to that many decimal places (e.g., 2 for two decimal places).
  • If specified as a negative integer, it truncates to the left of the decimal point (e.g., -1 truncates to the tens place, -2 to the hundreds place).

Note: The TRUNC function simply removes the decimal part; it does not round the number. This is the key difference between TRUNC and ROUND functions.

📊 Simple Examples with Formulas

Let's look at some practical examples to understand how TRUNC works in different scenarios.

Example 1: Truncating to Whole Number (Default Behavior)

If you want to remove the decimal part and get a whole number:

Number	Formula	Result	Explanation
12.99	=TRUNC(12.99)	12	The decimal part (.99) is removed.
5.432	=TRUNC(5.432)	5	The decimal part (.432) is removed.
-3.75	=TRUNC(-3.75)	-3	Removes the decimal part (-.75). Note: It doesn't round towards zero, it just cuts off the decimal.

Excel Formula:

=TRUNC(12.99)

Result: 12

Example 2: Truncating to Specific Decimal Places

You can specify the number of decimal places using the optional digits argument.

Number	Formula	Result	Explanation
12.995	=TRUNC(12.995, 2)	12.99	Truncates to 2 decimal places.
5.4321	=TRUNC(5.4321, 1)	5.4	Truncates to 1 decimal place.
10.12345	=TRUNC(10.12345, 3)	10.123	Truncates to 3 decimal places.

Excel Formula:

=TRUNC(12.995, 2)

Result: 12.99

Example 3: Truncating Negative Numbers

The TRUNC function works differently with negative numbers compared to rounding.

Number	Formula	Result	Explanation
-12.99	=TRUNC(-12.99)	-12	Removes the decimal part.
-12.995	=TRUNC(-12.995, 2)	-12.99	Truncates to 2 decimal places.
-88.45	=TRUNC(-88.45, -1)	-80	Truncates to the tens place (removes .45).

Excel Formula:

=TRUNC(-12.995, 2)

Result: -12.99

Key Point: TRUNC(-12.995, 2) gives -12.99, while ROUND(-12.995, 2) would give -13.00 (because it rounds to the nearest number).

Example 4: Truncating to Left of Decimal Point (Negative Digits)

Using negative values for the digits argument truncates the number to the left of the decimal point.

Number	Formula	Result	Explanation
1234.56	=TRUNC(1234.56, -1)	1230	Truncates to the tens place.
987.65	=TRUNC(987.65, -2)	900	Truncates to the hundreds place.
56789	=TRUNC(56789, -3)	56000	Truncates to the thousands place.

Excel Formula:

=TRUNC(1234.56, -1)

Result: 1230

🎥 TRUNC Function Video Tutorial (Khmer)

Watch this short video to see how the TRUNC function works in real Excel:

📝 Common Use Cases & Pro Tips

1. Quick Data Cleaning

Use TRUNC to remove unwanted decimal values from a list of numbers without rounding.

2. Mathematical Calculations

If you need to perform calculations based on the integer part of a number (e.g., calculating full units of something).

3. Difference Between TRUNC and ROUND

• TRUNC(number, [digits]): Simply removes the decimal part (cuts off).
• ROUND(number, [digits]): Rounds to the nearest specified number of decimal places (rounds up or down).

4. Using with Other Functions

TRUNC can be combined with other functions like SUM, AVERAGE, IF, etc., to perform more complex operations.

Example:

=SUM(A1:A10) - TRUNC(SUM(A1:A10), 0)

This formula calculates the total decimal values in the range A1:A10.

---

📚 Related Resources

If you're interested in learning more Excel functions, check out these tutorials:

• How to Use MIN and MAX Functions in Excel - Find minimum and maximum values.
• How to Use COUNTIF and COUNTIFS Functions - Count cells based on criteria.
• Understanding Excel Operators (+, -, *, /, ^, &) - Learn basic Excel operators.
• Top 15 Basic Excel Formulas for Beginners - Essential formulas for beginners.

---

That'