Round a number up to the next multiple
=CEILING(number, significance)
CEILING is useful for rounding numbers up to a specified multiple, for example cents to the next dollar.
CEILING defines 'the number to round' and the 'multiple to round up to'.
CEILING and numbers.
In the examples below, the CEILING function is used to round GDP per capita figures up to the next multiple (whether 100, 10 or 5).
Use the CEILING function to round a number up to the next multiple.
Syntax
=CEILING(number, significance)
The CEILING function consists of 2 arguments:
- number. Required. The value to be rounded up.
- significance. Required. The multiple to be rounded up to.
Try it now!
- Enter your formulas into the grey cells.
Scope
CEILING defines 'the number to round' and the 'multiple to round up to'.
|
=CEILING(16726,100) || result is 16800. The number 16726 is rounded up to the next 100. =CEILING(16726,25) || result is 16750. The number 16726 is rounded up to the next 25. |
|
=CEILING(944-963,-5) || result is -20. The result of 944 less 963 (-19) is rounded down to nearest multiple of 5 (-20). If the multiple had been positive 5, the result would have been -15. =CEILING(944.41-963.49,-0.1) || result is -19.1. The result of 944.41 less 963.49 (-19.08) is rounded down to nearest multiple of 0.1 (-19.1). |
|
=CEILING(E7,3) || result is the value from cell E7 rounded up to the nearest multiple of 3. If cell E7 contains a 4, result is 6. If cell E7 contains a 6, result is 6. If cell E7 contains a non-numeric value, #VALUE! is returned. |
|
=CEILING("12:01:00 PM","0:15") || result is 12:15:00 PM, with the time being rounded up to the nearest 15 minutes. |
Caveats
- CEILING is a compatibility function, replaced by CEILING.MATH and CEILING.PRECISE.
- CEILING requires a numeric value. Non-numeric values will return #VALUE!.
- If number is an exact multiple of significance, no rounding occurs.
- If number and significance are positive, number is rounded up away from zero.
- If number and significance are negative, number is rounded down away from zero.
- If number is negative and significance is positive, number is rounded up towards zero.
- If number is positive and significance is negative, #NUM! is returned.
Common issues
- #VALUE!: number is not a numeric value.
- #NUM!: number is positive and significance is negative.
Related Functions
- FLOOR: round down to the next multiple.
- MROUND: round to the nearest multiple.
- ROUND: round normally to a specified number of digits.
- ROUNDDOWN: round down towards zero.
- ROUNDUP: round up away from zero.
Functions Category