Formula Watch: Turn off rounding with TRUNC

If you work with currency, you may have noticed that the default behavior in Excel is to round up cents. This may work in most situations, but if you ever want to truncate the value without rounding, you'll want to use the TRUNC function.

For instance, let's say you are dividing a $119.99 subscription service price by 12 to arrive at the monthly price. If you use the simple formular =A2/12, it comes up as an even dollar amount: $10.00. But the marketing team wants to use the truncated version, $9.99.

No problem. Just use:

=TRUNC(A2/12,2)

The ",2" is the number of decimal places - for currency, you'd use "2" but feel free to change this to however many digits you need depending on your application.

Here is an example of how different prices appear using this formula versus simply dividing by 12:

Suzanne