Home > Excel for Business > 

How to Round Up with Excel's
INT and ROUNDUP Functions

July, 2006

Ronald,

Basically, you want to round a number up.

Excel has offered one way to round up from the first generation. In recent generations it has also offered a second method.

Using INT

The old way has been to use the INT function. Generally, if we Excel users think about INT at all, we think of it as the function we use to eliminate decimals in a calculation. To illustrate:

=INT(123.4) = 123

However, INT is more sophisticated than that. Strictly speaking, INT rounds a number down. That is, it rounds a positive number down, towards zero, and a negative number down, away from zero.

Therefore, it's easy to round a number up. Just switch its sign; find the INT; then switch the sign of the result.

For example, here's how to round 123.4 upwards:

=-INT(-123.4) = 124

And here's one formula you could use in your specific application:

=-INT(-301/300)

Here, INT(-301/200) returns -2 units of 300, and the minus sign after the equal sign turns the value to a positive 2 to units of 300.
 

Using ROUNDUP

The ROUNDUP function offers more power to control your results. It takes this form:

=ROUNDUP(number, num_digits)

First, here's how it would work in your application:

=ROUNDUP(301/300,0) = 2
=ROUNDUP(-301/300,0) = -2

That is, ROUNDUP rounds away from zero.

The zeros in the two formulas above tell Excel to return its results using zero decimal places. A positive number specifies the number of digits to the right of the decimal point; a negative number specifies the number of zeros to the left of the decimal. To illustrate:

=ROUNDUP(12345.0123,3)  =  12345.013
=ROUNDUP(12345.0123,1)  =  12345.1
=ROUNDUP(12345.0123,-1)  =  12350
=ROUNDUP(12345.0123,-3)  =  13000

To be clear, contrast these results with those produced by the ROUND function, which you're probably more familiar with:

=ROUND(12345.0123,3)  =  12345.012
=ROUND(12345.0123,1)  =  12345
=ROUND(12345.0123,-1)  =  12350
=ROUND(12345.0123,-3)  =  12000

Hope this helps,

Charley Kyd
July, 2006