Excel Math Functions
How to Use Excel's Better-Known Rounding Functions: INT, ROUNDUP,
ROUNDDOWN, & ROUND
Excel's four better-known functions for rounding numbers are: INT, ROUND, ROUNDUP, and ROUNDDOWN. Here's how to understand and use them.
by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
Excel has one way to round up or down from the first generation. In
recent generations it has also offered a second method. And it also
offers ROUND, which uses a different rounding method.
In this article, I'll describe these four functions. In a
future article, I'll discuss Excel's other three rounding
functions, which perform a slight different purpose:
CEILING.MATH, which was introduced in Excel
|Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more.
First, let's define what we're talking about. Depending on
the function, Excel uses three
types of rounding:
Math: Rounding down moves towards zero; rounding up moves away
Order: Rounding down moves from higher numbers to
rounding up moves from lower numbers to higher. (This is my
term. If you sort a column of data in descending order,
rounding down moves down the column and rounding up moves up the
Precise: Rounding down moves down to the
nearest integer, regardless of the sign of the number. Rounding up moves
up to the nearest integer, regardless of the sign of the number.
("Precise" is a Microsoft term, by the way.)
Those definitions produce the same results for positive numbers, but
different results for negative numbers.
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
remove decimals from a fractional number. To illustrate:
=INT(123.9) = 123
However, INT actually is more sophisticated than that. INT
rounds a number down using the Order rounding method. That is, it rounds a positive number down, towards
zero, and a negative number down, away from zero.
=INT(5.1) = 5
=INT(-5.1) = -6
Therefore, it's easy to use INT to round a number up using the
Math method. Just switch its sign;
find the INT; then switch the sign of the result.
For example, here's how to round 123.4 using the Math
rounding method (away
=-INT(-123.4) = 124
And here's how to round -123.4 down using the Math
=-INT(--123.4) = -123
The ROUNDUP function offers more power to control your results. It
uses the Math rounding method.
takes this form:
=ROUNDUP(5.9,0) = 6
=ROUNDUP(-5.9,0) = -6
Again, ROUNDUP rounds away from zero.
The zeros values for the num_digits argument in these formulas 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
The ROUNDDOWN function works like ROUNDUP, but in the
opposite direction. It also uses the Math rounding
It takes this form:
=ROUNDDOWN(5.9,0) = 5
Compare these results to the ROUNDUP version above.
Compare ROUNDUP and ROUNDDOWN to ROUND
The ROUND function looks quite similar:
But it uses the Precise rounding method. That is, it rounds up or down depending on the digits that follow
the num_digits argument you specify, without regard to the sign
of the number. For example:
=ROUND(5.9,0) = 6
=ROUND(-5.9,0) = -6
=ROUND(5.4,0) = 5
=ROUND(-5.4,0) = -5
You get similar results when you change the num_digits
value, whatever the sign of the number:
=ROUND(12345.0629,3) = 12345.063
=ROUND(-12345.0629,3) = -12345.063
=ROUND(12345.0629,2) = 12345.06
=ROUND(-12345.0629,2) = -12345.06
=ROUND(12345.0629,1) = 12345.1
=ROUND(-12345.0629,1) = -12345.1
=ROUND(12345.0629,-1) = 12350
=ROUND(-12345.0629,-1) = -12350
=ROUND(12345.0629,-3) = 12000
=ROUND(-12345.0629,-3) = -12000
Now, with four functions to choose from, you should have
complete control over your rounding options!
Take Your Next Steps
If you're looking for additional help with
this topic, I can help you in three ways. To learn
Excel Training, Coaching, and Consulting.