Rounding in Excel brings numbers to a simpler form: prices to cents, quantities to whole units, times to the hour. There are quite a few functions for rounding numbers, dates and times. Let’s go through all of them, and through the situations where you need to round in a particular direction, from the simplest to the most complex formulas.
One important point first. The Decrease Decimal button on the Home tab only changes how a number looks: the cell still stores all the decimals, and they are used in calculations. To actually round a value, you need a function or a procedure that changes it.
ROUND: rounding to a given number of digits
The basic function is ROUND. It takes the number and the number of digits and rounds by the usual rules: 5 and above goes up.
=ROUND(3.456,2)
returns 3.46. The number of digits can be zero (round to a whole number) or negative: =ROUND(1234,-2) returns 1200, rounding to hundreds.
Rounding to an integer: INT and TRUNC
For simple rounding to an integer, without the second argument, Excel has two functions: INT and TRUNC.
The INT function
INT rounds a number down to the nearest integer. The direction is always down:
=INT(3.7)
returns 3, and
=INT(-3.7)
returns −4.
The TRUNC function
TRUNC works a little differently. First, it has an optional second argument that sets how many decimals to keep. It’s zero by default, so with positive numbers the function works just like INT.
The second difference shows on negative numbers. TRUNC doesn’t round down, it simply cuts off the rest of the fraction, which for negative numbers means rounding up. So TRUNC(−3.7) returns −3, and TRUNC(−3.75,1) returns −3.7.
Rounding up and down
To round up or down, Excel has the ROUNDUP and ROUNDDOWN functions. They take two arguments: the number and the number of decimals to round to.
Rounding up
ROUNDUP always rounds away from zero. For example:
=ROUNDUP(3.456,2)
=ROUNDUP(-3.456,2)
return 3.46 and −3.46, rounding up to hundredths. Since rounding goes by absolute value, ROUNDUP actually rounds negative numbers down.
Rounding down
ROUNDDOWN, on the contrary, rounds toward zero:
=ROUNDDOWN(-3.456,2)
returns −3.45. The function behaves the opposite way: it rounds positive numbers down and negative numbers up, also by absolute value.
Rounding to a multiple
The functions below round numbers to the nearest multiple of a value. That’s useful for prices ending in .99, packaging by 6 or 12 units, time slots and many other tasks.
MROUND
MROUND rounds a number to the nearest multiple of the given value. It takes two arguments: the number and the multiple. For example, =MROUND(17,5) returns 15, and =MROUND(18,5) returns 20.
Both arguments must have the same sign, otherwise the formula returns #NUM!. =MROUND(-17,-5) works and returns −15.
CEILING and FLOOR
CEILING and FLOOR round a number up and down to the nearest multiple. Both take two required arguments: the number and the significance. Neither the significance nor the result has to be an integer:
=CEILING(10.1,2)
returns 12, and
=FLOOR(10.9,3.33)
returns 9.99.
CEILING and FLOOR with negative numbers
With negative numbers, the direction is not what the names suggest at first sight. CEILING(−10.1,2) rounds to −10, toward zero, and FLOOR(−10.1,2) rounds to −12, away from zero. In other words, CEILING always goes toward the larger number, and FLOOR toward the smaller one.
CEILING.MATH and FLOOR.MATH
The only difference of CEILING.MATH and FLOOR.MATH from their predecessors is the third argument, mode, which controls the direction for negative numbers. It doesn’t affect positive numbers.
If mode is 0 or omitted, the direction matches the name: CEILING.MATH(−10.1,2) returns −10, FLOOR.MATH(−10.1,2) returns −12. If mode is any non-zero number, negative numbers are rounded by absolute value: CEILING.MATH(−10.1,2,1) returns −12, and FLOOR.MATH(−10.1,2,1) returns −10. Also, the significance is optional here and is 1 by default.
Apparently, these functions were created because the behavior of the old CEILING and FLOOR is fixed and not always convenient.
Rounding dates and times
Sometimes you need to round a date or time to a certain value. For dates, it’s usually the start of a week or month, for times, hourly or minute intervals.
Round a date to the start of the month
Build the first day of the month with the DATE function, taking the year and month from the date and typing 1 as the day:
=DATE(YEAR(A1),MONTH(A1),1)
To the start of the next month
The start of the next month is the day after the end of this month, which the EOMONTH function returns:
=EOMONTH(A1,0)+1
The same function can round a date to the start of this month, because its second argument shifts the month back and forth. Put −1 instead of 0 to get the end of the previous month, and add one day:
=EOMONTH(A1,-1)+1
To the start of the week
To round a date down to Monday of its week, subtract the day number from the WEEKDAY function (with type 2, Monday is 1):
=A1-WEEKDAY(A1,2)+1
Rounding time to the hour
Sometimes you need to round a time to the start of the hour or to another interval. It’s convenient to set the interval in quotes in the usual time format and use MROUND, FLOOR, CEILING or their .MATH versions:
=MROUND(A1,"1:00")
=FLOOR(A1,"1:00")
The first one rounds to the nearest full hour, the second one to the start of the current hour. They return different values when more than 30 minutes have passed since the start of the hour.
In the same way, you can round a time to 15 or 5 minutes by setting the second argument to “0:15” or “0:05”.
Common mistakes
- The number looks rounded, but the sum is off by a cent. The cells are only formatted with fewer decimals, and the sum uses the full values. Round with a function, not with the format.
- MROUND returns #NUM!. The number and the multiple have different signs.
- ROUNDUP rounded a negative number “down”. It rounds by absolute value. To always round toward the larger number, use CEILING.MATH.
- The rounded time shows a strange number. The result is a fraction of a day: apply a time format to the cell.
Rounding data in place with !SEMTools
Sometimes you want to round data in place, without creating extra columns or writing formulas. And the data must really be rounded, not just shown with fewer digits. Unfortunately, Excel has no built-in procedures for this. That’s why I added them to my !SEMTools add-in, in the Math menu.
They’ve often come in handy in my work with numbers. Before rounding, I needed a dataset of random numbers, and a procedure from the Arithmetic operations section helped. I chose a range from 90 to 100, and then rounded to hundredths, tenths, integers and hundreds with different methods. Select the range, choose the method, done:
Round numbers up, down or to a multiple right in the source cells, without ROUND formulas or helper columns.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary
I hope you found what you were looking for on this page and were able to round your numbers the way you need. ROUND covers most cases, ROUNDUP and ROUNDDOWN set the direction, MROUND, CEILING and FLOOR round to multiples, and the .MATH versions give control over negative numbers. To round whole columns without formulas, use the !SEMTools procedures.
You may also be interested in other functions in the Excel functions reference.