Trunc in Excel

Trunc Function in Excel

Trunc is the excel function used to trim the decimal numbers to integers. It is one of the trigonometry and mathematic functions introduced in the 2007 version of excel. TRUNC returns only the integer part by eliminating the fractional portion of a decimal number by specifying the required precision.

Even though INT is also used for returning the integer part, there is a drawback with it. When INT function in excel is used for negative numbers, it rounds of value to a lower number instead of the resulting integer part. The present article explains the use of truncate function in excel with examples and formula by covering the following topics.

Explanation

Trunc function returns the trimmed value of a number based on the number of digits. It is a built-in function utilized as an excel worksheet function. It will be input as a formula into the cells of the Excel sheet.

Syntax

• Parameters Required: The parameters used by the TRUNC function are identified as number and num_digits
• Number: It is the mandatory parameter that a user wants to truncate the digits of the fractional part
• num_digits: It is the optional parameter used to specify the number of digits to be truncated after decimal numbers. The default value of this parameter is 0 if no value is given
• Returns: This function results in a numeric number omitting the fractional part

This function works in all kinds of excel versions including 2007, 2010, 2013, 2016, and office 365.

If the value is given to the parameter of Num_digits

• Value is equal to zero, the function returns the rounded value
• Value is more than zero, it indicates the number of digits to be truncated and presented the right side of the decimal
• Value is less than zero, it indicates the number of digits to be truncated and presented a left side of the decimal

How to Use Truncate Function in Excel? (Example)

Example #1 – Basic Usage of TRUNC Function

This example best illustrates the basic applications of the truncated function

Step 1: In the first step, consider the following data shown in the figure

Step 2: Place the cursor into the appropriate cell to enter the TRUNC function in excel

Step 3: Enter the excel truncate formula as shown in the figure

Step 4: Select the cell address of number that wants to be truncated

In this, no cell address is given for the num_digits parameter and it takes the default value of zero. Press Enter to see the result.

Step 5: Apply the formula to the remaining cells by dragging through the mouse.

Step 6: Observe the results shown as mentioned below screenshot

In this, only the left part of the decimal results since the default value is zero

Example #2 – Applying TRUNC Function on a Set of Decimal Numbers

This example best illustrates the basic applications of the truncated function on the set of decimal numbers

Step 1: In the first step, consider the following data shown in the figure

In this example, the number of digits after decimal point going to be truncated are considered

Step 2: Place the cursor into the appropriate cell.

Step 3: Enter the truncated excel function as shown in the figure

Step 4: Select the cell address of number that wants to be truncated and number of digits

In this, cell address is given for values and num_digits parameters and it takes the value mentioned in the column. Press Enter to see the result as shown below

Step 5: Apply the formula to the remaining cells by dragging through mouse

Step 6: Observe the results shown as mentioned below screenshot

In this, the right part of a decimal number is resulted based on the value given in the column of a number of digits. A number of digits 5 indicates a decimal number is truncated to 5 digits after the decimal point.

Example #3 – To Extract Date from Date and Time

Step 1: In the first step, consider the following data shown in the figure

In this example, the number of digits for extracting date are considered as zero

Step 2: Place the cursor into the appropriate cell called “Extracted Date” to enter the TRUNC function in excel

Step 3: Enter the truncated excel function as shown in the figure

In this, cell address is given for date and time and num_digits parameter as zero as mentioned in the column.

Step 4: Press Enter to see the result as shown below

Step 5: Apply the excel formula to the remaining cells by dragging through mouse

Step 6: Observe the results shown as mentioned below screenshot

As shown in the figure, only the date value is extracted from date and time using the truncated function excel. If we don’t give a number of digits, it takes the default value of zero to the result date.

Application

The Trunc function has a lot of applications in Excel

• This excel function is useful in financial analysis to trim the values to preferred precision levels.
• It is used in mining the time and date values from date
• Truncating a decimal number to nearest integer number
• It is used when there is no need for grounding of a number

Things to Remember

• INT and TRUNCATE excel functions are identical since they result in integer values. But, these are different when they are used with negative, For example, INT(-5.3) returns -6 since it is lesser value but TRUNC (-5.3) returns -5 only removing the fractional value
• When the value of Num_digits is negative, it returns the truncated value as zero

Recommended Articles

This has been a guide to Trunc Function in Excel. Here we learn how to how to use the truncate function using TRUNC formula along with examples and downloadable excel template. You may learn more about excel from the following articles –

• Excel Match Multiple Criteria
• Multiple Condition in Excel COUNTIF
• Excel Convert Function
• MROUND Function in Excel