1. Computing

TRUNC Function: Truncate Numbers in Excel


Truncate Values to a set Number of Decimal Places
Truncate Numbers in Excel

Truncate Numbers in Excel

© Ted French

Note: If you are experienced using Excel functions try the Excel TRUNC Function Quick Tutorial which is a bare bones tutorial on the TRUNC function.

This tutorial is intended for users new to using Excel's rounding functions.

TRUNC Function: Truncate Numbers in Excel

There are a number of rounding functions in Excel and the one you use depends on the results you want.

The TRUNC function is usually included with this group even though it does no rounding.

What it does do is truncate or shorten the target number to a set number of decimal places without rounding the remaining digits or whole number.

Like other rounding functions, the TRUNC function actual alters the data in your worksheet and will, therefore affect the results of any calculations that use the truncated values.

There are, on the other hand, formatting options in Excel that allow you change the number of decimal places displayed by your data without changing the numbers themselves.

Making formatting changes to data has no effect on calculations.

The TRUNC Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the TRUNC function is:

= TRUNC (number , num_digits)

Number - the value to be truncated. This argument can contain the actual data to be truncated or it can be a cell reference to the location of the data in the worksheet.

Num_digits (Optional): The number of decimal places to be left by the function. If this argument is omitted a default value of 0 (zero) is used for num_digits (no decimal places removed).

TRUNC Function Example: Truncate to a set Number of Decimal Places

For help with this example, see the image above.

In this example we will use the TRUNC function to remove three decimal places from the target number.

To show the effect TRUNC function has on calculations, both the original number and the truncated one will be multiplied by 10 and the results compared.

Entering the Data

Enter the following data into the designated cells.
Cell    Data 
 D1  -  27.3579
 E1  -  10
 E2  -  10

Entering the TRUNC Function

  1. Click on cell D2 to make it the active cell - this is where the results of the TRUNC function will be displayed

  2. Click on the Formulas tab of the ribbon menu

  3. Choose Math & Trig from the ribbon to open the function drop down list

  4. Click on TRUNC in the list to bring up the function's dialog box

  5. In the dialog box, click on the Number line

  6. Click on cell D1 in the worksheet to enter that cell reference into the dialog box

  7. In the dialog box, click on the Num_digit line

  8. Type a " 1 " (no quotations) on this line since we want to leave the target number with only one digit after the decimal point

  9. Click OK

  10. The answer 27.3 should appear in cell D2

  11. When you click on cell D2 the complete function = TRUNC ( D1, 1 ) appears in the formula bar above the worksheet

Using the Truncated Number in Calculations

  1. Click on cell F1 to make it the active cell - this is where the multiplication formula will be entered.

  2. Type an equal sign to begin the formula

  3. Click on cell D1 to enter that cell reference into the formula

  4. Type an asterisk ( * ) - the symbol for multiplication in Excel

  5. Click on cell E1 to enter that cell reference into the formula

  6. Press the Enter key on the keyboard to complete the formula

  7. The answer 273.579 should appear in cell F1

  8. Click on cell F1 again to make it the active cell

  9. Copy the formula in F1 to cell F2 using the Fill Handle or Copy and Paste

  10. The answer 273 should appear in cell F2

The different formula results in cells F1 and F2 - 273.579 vs. 273 show the effect truncating numbers can have on calculations

While this may not be a significant difference for many calculations, it can be significant for calculations in such fields as engineering or for calculations involving the results of scientific experiments.

©2014 About.com. All rights reserved.