I couldn’t find a decent article outlining the differences between the various rounding functions in Excel… so I thought I’d create one.
Some functions will round to a set number of decimals, others just drop the fraction part completely. There are also some differences when working with negative numbers.
We’ll look at the following five different functions:
INT
Syntax: =INT(number)
Returns the integer (non-fraction part) of a number, but always rounds to the “smaller” integer. So INT(3.6)=3 and INT(-3.6)=-4.
Something like INT(-3.2) would also round to -4.
So for positive numbers it behaves like ROUNDDOWN and for negatives it behaves like ROUNDUP.
TRUNC
Syntax: =TRUNC(number, [num_digits])
Also returns the integer of a number. But this time it doesn’t do any rounding, it just drops the fraction. E.g. TRUNC(3.6)=3 and TRUNC(-3.6)=-3
Another difference is that you can provide a number to specify the precision of the truncation, e.g. TRUNC(3.765, 1)=3.7 and TRUNC(-3.765, 1)=-3.7
If you leave the precision out it just drops the entire fraction.
ROUND
Syntax: =ROUND(number, num_digits)
Rounds a number to a specified number of digits. Specifying the precision is compulsory, so if you want to round to the nearest integer use 0 for num_digits.
Here are a few examples:
ROUND(3.76, 0)=4 and ROUND(-3.76, 0)=-4.
ROUND(3.76, 1)=3.8 and ROUND(-3.76, 1)=-3.8.
ROUND(3.725, 1)=3.7 and ROUND(-3.725, 1)=-3.7.
ROUNDUP
Syntax: =ROUNDUP(number, num_digits)
Rounds a number up to a specified number of digits.
Examples:
ROUNDUP(3.6, 0)=4 and ROUNDUP(-3.6, 0)=-4
ROUNDUP(3.2, 0)=4 and ROUNDUP(-3.2, 0)=-4
This is a confusing one. Rounding a negative number “up” means going from -3.2 to -4 even though the value is getting smaller.
ROUNDDOWN
Syntax: =ROUNDDOWN(number, num_digits)
Rounds a number down to a specified number of digits.
Examples:
ROUNDDOWN(3.6, 0)=3 and ROUNDDOWN(-3.6, 0)=-3
ROUNDDOWN(3.2, 0)=3 and ROUNDDOWN(-3.2, 0)=-3
Again a confusing one. Rounding a negative number “down” means going from -3.6 to -3 even though that means the value is getting larger.
I hope this helps. If you have a scenario that you can’t quite work out from the examples above, get in touch and I’ll see if I can shed some light on the problem.
