The tutorial explains the uses of ROUND, ROUNDUP, ROUNDDOWN, FLOOR, CEILING, MROUND and other Excel rounding functions and provides formula examples to round decimal numbers to integers or to a certain number of decimal places, extract a fractional part, round to nearest 5, 10 or 100, and more.
In some situations when you don't need an exact answer, rounding is a useful skill to use. In plain English, to round a number is to eliminate the least significant digits, making it simpler but keeping close to the original value. In other words, rounding lets you get an approximate number with the desired level of accuracy.
In everyday life, rounding is commonly used to make numbers easier to estimate, communicate or work with. For instance, you can use rounding to make long decimal numbers shorter to report the results of complex calculations or round off currency values.
Many different ways of rounding exist, such as rounding to integer, rounding to a specified increment, rounding to simple fractions, and so on. And Microsoft Excel provides a handful of functions to handle different rounding types. Below, you will find a quick overview of the major round functions and well as formula examples that demonstrate how to use those functions on the real-life data in your worksheets.
If you want to round numbers in Excel solely for presentations purposes, you can change the cell's format by performing the following steps:
Important note! This method changes the display format without changing the actual value stored in a cell. If you refer to that cell in any formulas, the original non-round value will be used in all calculations.
Unlike formatting options that change only the display value, Excel round functions alter the actual value in a cell.
Below you will find a list of functions specially designed for performing different types of rounding in Excel.
ROUND is the major rounding function in Excel that rounds a numeric value to a specified number of digits.
Syntax: ROUND(number, num_digits)
Number - any real number you want to round. This can be a number, reference to a cell containing the number or a formula-driven value.
Num_digits - the number of digits to round the number to. You can supply a positive or negative value in this argument:
=ROUND(15.55,1) rounds 15.55 to 15.6.
=ROUND(15.55,-1) rounds 15.55 to the nearest 10 and returns 20 as the result.
=ROUND(15.55,0) rounds 15.55 to 16.
The Excel ROUND function follows the general math rules for rounding, where the number to the right of the rounding digit determines whether the number is rounded upwards or downwards.
Rounding digit is the last significant digit retained once the number is rounded, and it gets changed depending on whether the digit that follows it is greater or less than 5:
The following screenshot demonstrates a few ROUND formula examples:
The ROUNDUP function rounds the number upward (away from 0) to a specified number of digits.
Syntax: ROUNDUP(number, num_digits)
Number - the number to be rounded up.
Num_digits - the number of digits you want to round the number to. You can supply both positive and negative numbers in this argument, and it works like num_digits of the ROUND function discussed above, except that a number is always rounded upward.
The ROUNDDOWN function in Excel does the opposite of what ROUNDUP does, i.e. rounds a number down, toward zero.
Syntax: ROUNDDOWN(number, num_digits)
Number - the number to be rounded down.
Num_digits - the number of digits you want to round the number to. It works like the num_digits argument of the ROUND function, except that a number is always rounded downward.
The following screenshot demonstrates the ROUNDDOWN function in action.
The MROUND function in Excel rounds a given number up or down to the specified multiple.
Syntax: MROUND(number, multiple)
Number - the value you want to round.
Multiple - the multiple to which you want to round the number.
For example, the formula
=MROUND(7, 2) rounds 7 to the nearest multiple of 2 and returns 8 as the result.
Whether the last remaining digit is rounded up (away from 0) or down (towards 0) depends on the remainder from dividing the number argument by the multiple argument:
The MROUND function comes in handy, say, for rounding prices to the nearest nickel (5 cents) or a dime (10 cents) to avoid dealing with pennies as change.
And, it is really indispensable when it comes to rounding times to a desired interval. For example, to round time to the nearest 5 or 10 minutes, just supply "0:05" or "0:10" for the multiple, like this:
Note. The MROUND function returns the #NUM! error when its arguments have different signs. For example, both of the formulas
=MROUND(3, -2) and
=MROUND(-5, 2) result in the NUM error.
The FLOOR function in Excel is used to round a given number down, to the nearest multiple of a specified significance.
Syntax: FLOOR(number, significance)
Number - the number you want to round.
Significance - the multiple to which you wish to round the number.
=FLOOR(2.5, 2) rounds 2.5 down to the nearest multiple of 2, which is 2.
The Excel FLOOR function performs rounding based on the following rules:
Note. In Excel 2003 & 2007, the number and significance arguments must have the same sign, either positive or negative, otherwise an error is returned. In newer Excel versions, the FLOOR function has been improved, so in Excel 2010, 2013 and 2016 it can handle a negative number and positive significance.
The CEILING function in Excel rounds a given number up, to the nearest multiple of significance. It has the same syntax as the FLOOR function.
Syntax: CEILING(number, significance)
Number - the number you want to round.
Significance - the multiple to which you want to round the number.
For instance, the formula
=CEILING(2.5, 2) rounds 2.5 up to the nearest multiple of 2, which is 4.
The Excel CEILING function works based on the rounding rules similar to FLOOR's, except that it generally rounds up, away from 0.
The INT function rounds a number down to the nearest integer.
Of all Excel round functions, INT is probably the easiest one to use, because it requires only one argument.
Number - the number you want to round down to the nearest integer.
Positive numbers are rounded toward 0 while negative numbers are rounded away from 0. For example,
=INT(1.5) returns 1 and
=INT(-1.5) returns -2.
The TRUNC function truncates a given numeric value to a specified number of decimal places.
Syntax: TRUNC(number, [num_digits])
The Excel TRUNC function adheres to the following rounding rules:
=TRUNC(-2.4)returns -2, while
=INT(-2.4)returns -3 because it's the lower integer. For more info, please see Rounding to integer example.
The following screenshot demonstrates the TRUNC function in action:
These are two more functions provided by Excel for rounding a specified number to an integer.
ODD(number) rounds up to the nearest odd integer.
EVEN(number) rounds up to the nearest even integer.
The ODD and EVEN functions may prove useful when you are processing items that come in pairs.
=ODD(2.4) returns 3
=ODD(-2.4) returns -3
=EVEN(2.4) returns 4
=EVEN(-2.4) returns -4
As you see, there exist a variety of functions to round off numbers in Excel depending on the particular purpose. The following examples will hopefully give you some clues on how to use Excel rounding formulas based on your criteria.
Depending on the situation, you may want to round decimals up, down or based on math rounding rules:
ROUNDUP function - always rounds the decimal upward.
ROUNDDOWN function - always rounds the decimal downward.
ROUND - rounds up if the rounding digit is followed by the digit equal to or greater than 5, otherwise rounds down.
As an example, lets round the decimal numbers in column A to 2 decimal places. In the first argument (number), you enter a reference to the cell containing the number, and in the second argument (num_digits) you specify the number of decimal places you want to keep.
=ROUNDUP(A2, 2) - rounds the number in A2 upward, to two decimal places.
=ROUNDDOWN(A2, 2) - rounds the number in A2 downward, to two decimal places.
=ROUND(A2, 2) - rounds the number in A2 to 2 decimal places, upward or downward, depending on whether the 3rd decimal digit is greater or less than 5.
When it comes to rounding a negative number, the results returned by the Excel round functions, may seem to flout logic :)
When the ROUNDUP function applies to negative numbers, they are said to be rounded up, even though they actually decrease in value. For example, the result of
=ROUNDUP(-0.5, 0) is -1, as in row 7 in the screenshot below.
The ROUNDDOWN function is known to round numbers downward, though negative numbers may increase in value. For example, the formula
=ROUNDDOWN(-0.5, 0) returns 0, as in row 8 in the screenshot below.
In fact, the rounding logic with regard to negative numbers is very simple. Whenever you use the ROUND, ROUNDDOWN or ROUNDUP function in Excel on a negative number, that number is first converted to its absolute value (without the minus sign), then the rounding operation occurs, and then the negative sign is re-applied to the result.
If you want to extract a fractional part of a decimal number, you can use the TRUNC function to truncate the decimal places and then subtract that integer from the original decimal number.
=A2 - TRUNC(A2,0)
As demonstrated in the screenshot below, the formula in column B works perfectly both for positive and negative numbers. However, if you'd rather get an absolute value (decimal part without the minus sign), then wrap the formula in the ABS function:
=ABS(A2 - TRUNC(A2,0))
As is the case with rounding to a certain number of decimal places, there is a handful of functions for rounding a fractional number to an integer.
To round up to nearest integer, use an Excel ROUNDUP formula with num_digits set to 0. For example
=ROUNDUP(5.5, 0) rounds decimal 5.5 to 6.
To round down to nearest whole number, use either INT or ROUNDDOW with num_digits set to 0. For example both of the following formulas round 5.5 to 5:
For negative decimals, however, the INT and ROUNDDOWN functions yield different results - INT rounds negative decimals away from 0, while ROUNDDOWN toward 0:
=ROUNDOWN(-5.5, 0) returns -5.
=INT(-5.5) returns -6.
To remove the factional part without changing the integer part, use the TRUNC formula with the second argument (num_digits) omitted or set to 0. For example,
=TRUNC(5.5) truncates the decimal part (.5) and returns the integer part (5).
To round a decimal up to the nearest odd integer, use the ODD function:
=ODD(5.5) returns 7.
To round a decimal up to the nearest even integer, use the EVEN function:
=EVEN(5.5) returns 6.
Microsoft Excel provides 3 functions that let you round numbers to nearest half, more precisely to the nearest multiple of 0.5. Which one to use depends on your rounding criteria.
=MROUND(A2, 0.5). Whether MROUND rounds the number up or down depends on the remainder from dividing the number by multiple. If the remainder is equal to or greater than half the value of multiple, the number is rounded upward, otherwise downward.
As you see, the MROUND function can be used for rounding positive values only, when applied to negative numbers, it returns the #NUM error.
Rounding to nearest five, ten, hundred or thousand is done in the same manner as rounding to 0.5 discussed in the previous example.
Supposing that the number you want to round to closest 5 resides in cell A2, you can use on of the following formulas:
To round your numbers to nearest ten, supply 10 in the second argument of the rounding functions:
Rounding to a hundred is done in the same way, except that you enter 100 in the second argument:
To round a value in cell A2 to the nearest thousand, use of the following formulas:
The same techniques can be used for rounding numbers to other multiples. For example, you can round the prices to the nearest nickel (multiple of 0.05), lengths to the nearest inch (multiple of 1/12), or minutes to the nearest second (multiple of 1/60). Speaking of time, and do you know how to convert it to nearest hour or closest 5 or 10 minutes? If you don't, you will find the answers in the next section :)
There may be many situations when you need to round time values. And again, you can use different rounding functions depending on your purpose.
With times located in column A, you can use one of the following functions to round time to nearest hour:
In the ROUND, ROUNDUP and ROUNDDOWN formulas, you multiply the time value by 24 (number of hours in a day) to convert a serial number representing the time to hours. Then you use one of the rounding functions to round the decimal value to an integer, and then divide it by 24 to change the returned value back to the time format.
If your timestamps include date values, then use the INT or TRUNC function to extract dates (in the internal Excel system, dates and times are stored as serial numbers, the integer part representing a date and fractional part representing time). And then, use the formulas described above but subtract the date value. For example:
=MROUND(A1,"1:00") - INT(A1)
=MROUND(A1,"1:00") - TRUNC(A1)
The following screenshot demonstrates other formulas:
Note. For the results to display correctly, remember to apply the Time format to your cells.
In case you want to round times in your Excel sheet to five or ten minutes, or to the closest quarter-hour, you can use the same rounding techniques as demonstrated above, but replace "1 hour" with the desired number of minutes in the formulas.
For example, to round the time in A1 to the closest 10 minutes, use one of the following functions:
If you know (or can calculate) what portion of a day is constituted by a certain number of minutes, then you won't have problems using the ROUND, ROUNDUP and ROUNDOWN functions as well.
For example, knowing that 15 minutes, is 1/96th of a day, you can use one of the following formulas to round the time in A1 to the nearest quarter-hour.
This is how you perform rounding in Excel. Hopefully, now you know how, among all those round functions, chose the one best suited for your needs.
Table of contents