< Previous lesson | Overview | Next lesson >


How to cut off decimals in an elegant way

=TRUNC() is a formula which is very similar to =ROUND(), and does essentially the same thing, except for one thing; it doesn't round anything, it cuts the decimals off where you tell it to.

It's also similar to =ROUND() in that it's a very simple formula to learn :-)

The syntax is as follows:
=TRUNC([number, cell etc.];[number of decimals to keep])

This way the formula takes any number, and cuts off all decimals to the right of the number of decimals specified.

[number, cell etc.]
In this area you can insert any number, formula, cell reference or calculation, such as:
  • [2333/15]
  • [$B$5]
  • [sum(A3:C38)]
  • [58,3333]

All of these are valid.

[number of decimals to keep]
This are describes how many decimals you want, as long as you use positive numbers. If you use negative numbers, you instruct how many places to the left of the comma you want to erase. Here are som excamples:
  • =TRUNC(2333,6678;3) => 2333,667
  • =TRUNC(2333,6678;1) => 2333,60
  • =TRUNC(2333,6678;-3) => 2000,00
  • =TRUNC(2333,6678;-1) => 2330,00

As you can see, nothing gets rounded here, it simply cut of the numbers.