Learn About TRUNC Formula In Excel

TRUNC formula is one of the Maths formula. TRUNC formula is one of the important formula for finance professionals. Especially, if you are working with any financials statements preparation, you would appreciate TRUNC formula.

TRUNC Formula

Full form of TRUNC is TRUNCATION

TRUNC formula, truncates a number to an integer by removing the decimal or fractional or part of the number.

Syntax of TRUNC formula is


TRUNC(number,num_digits)

Where as,

• Number is the number which you want to truncate
• num_digits is a number specifying the precision of the truncation.

Excel takes Zero (0) by default, if you don't give the num_digits

Example 1:

=TRUNC(19.2962,0)

TRUNC formula returns the result 19

=TRUNC(19.9962,0)

TRUNC formula returns the result 19

You have to notice from the two examples that TRUNC formula least bothered about rounding off.

If you give instruction that you want 0 decimals, TRUNC formula removes all the decimals and present before you. If you instruct to display only two decimals, TRUNC formula removes all the decimals after second decimal in the number (ex: =TRUNC(19.2299,2) = 19.22)

Example 2:

Let us see the results, by changing number of degits

1. =TRUNC(195,829.62852,4)  =195,829.6285

Trunc formula removes all the decimals except the first 4 decimals.

2. =TRUNC(195,829.62852,3)  =195,829.628

Trunc formula removes all the decimals except the first 3 decimals.

3. =TRUNC(195,829.62852,2)  =195,829.62

Trunc formula removes all the decimals except the first 2 decimals.

4. =TRUNC(195,829.62852,1)  =195,829.6

Trunc formula removes all the decimals except the first 1 decimals.

5. =TRUNC(195,829.62852,0)  =195,829

Trunc formula removes all the decimals

6. =TRUNC(195,829.62852,-1) =195,820

Trunc formula replace the immediate digit to the left of the decimal point with 0

7. =TRUNC(195,829.62852,-2) =195,800

Trunc formula replaces the immediate two digits to the left of the decimal points with 0's

8. =TRUNC(195,829.62852,-3) =195,000

Trunc formula replaces the immediate three digits to the left of the decimal points with 0's

9. =TRUNC(195829.62852,-4) =190000

Trunc formula replaces the immediate four digits to the left of the decimal points with 0's

* Same results with -ve sign appears, if you try to truncate the -ve number 

=TRUNC(-195,829.62852,-4) = -190,000