CEILING, FLOOR and MROUND in Excel
CEILING(number, significance) rounds a number up to the next multiple of significance, FLOOR(number, significance) rounds it down, and MROUND(number, multiple) rounds to the nearest multiple. Use them for pack sizes, price points and time blocks, where ROUND, which works in decimal places, does not fit.
Syntax
CEILING(number, significance)| Part | What it is |
|---|---|
number | The value to round. |
significance | The multiple to round to, for example 12 for cases of twelve or 0.05 for five cents. |
Examples
=CEILING(50, 12)Result: 60
50 units ordered in cases of 12 means 5 cases, 60 units.
=FLOOR(50, 12)Result: 48
The largest number of full cases that fits in 50.
=MROUND(52, 5)Result: 50
Nearest multiple of 5. 52.5 and up would give 55.
=CEILING(B2, 0.05)Result: 4.35 when B2 is 4.31
Round a price up to the next five cents.
=CEILING(B2/12, 1)Result: The number of cases to order for B2 units
Same as ROUNDUP(B2/12, 0).
Common mistakes
- ROUND(B2, -1) rounds to the nearest 10 only. For a multiple such as 12, 25 or 0.05 use MROUND, CEILING or FLOOR.
- #NUM! from MROUND or CEILING means the number and the multiple have different signs.
- MROUND can round a decimal multiple the unexpected way because of floating point, for example MROUND(6.05, 0.1). Round the input first if it matters.
- Using CEILING when the business rule is nearest, or MROUND when a partial case must still be ordered. Decide the direction first.
Practice CEILING, FLOOR and MROUND
These drills check your answer on live data, free, no account needed.
- Order in full casesThe warehouse only ships full cases, and the case size is in F1. Round each quantity needed up to the next full case and put the units to order in C2 and C3. A quantity that already fills whole cases stays the same.
- Round to centsWork out each price including the sales tax in F1, rounded to 2 decimals. Fill C2 and C3.
Questions
How do I round up to the nearest 5 in Excel?
=CEILING(A2, 5). For the nearest 5 in either direction use =MROUND(A2, 5), and to round down use =FLOOR(A2, 5).
What is the difference between CEILING and ROUNDUP?
ROUNDUP works in decimal places: ROUNDUP(4.31, 1) is 4.4. CEILING works in multiples: CEILING(4.31, 0.25) is 4.5.
What are CEILING.MATH and FLOOR.MATH?
Newer versions with an extra argument for how negative numbers are rounded. For positive numbers they give the same result as CEILING and FLOOR.
Related
- ROUNDHow to round in Excel with ROUND, ROUNDUP, ROUNDDOWN and MROUND: to 2 decimals, to the nearest hundred and nearest 5, and why formatting is not rounding.
- MODHow the Excel MOD function returns the remainder of a division, with examples for odd and even, every nth row, the decimal part of a number and the sign rule.
- ABSHow the Excel ABS function returns a number without its sign, with examples for the gap between two cells, a tolerance check and making negatives positive.