Different Functions for Rounding in Excel

//

Linda

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.

Leave a comment

Subscribe to our YouTube Channel for free tips and tutorials!

Connect