CEILING function
CEILING rounds a number up to a chosen multiple, such as the next 0.05 for pricing or the next 15 minutes for billing. It is a compatibility function; use CEILING.MATH in new workbooks.
Example
Read it as a sentence: round 2.3 up to the next multiple of 0.5, which gives 2.5.
A
B
C
D
1
2.5
2
3
Syntax
CEILING(number, significance)
-
number(required) - Value to round.
-
significance(required) - The multiple to round up to.
Returns: number rounded up to the nearest multiple of significance.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=CEILING(2.3,0.5) | 2.5 | Positive |
=CEILING(2.7,0.5) | 3 | Positive 2.7 |
=CEILING(-2.7,0.5) | -2.5 | Negative number, positive significance |
=CEILING(-2.7,-0.5) | -3 | Both negative |
=CEILING(2.7,0) | 0 | Significance 0 |
=CEILING(0,0.5) | 0 | Number 0 |
=CEILING(2.7,-0.5) | #NUM! | Positive number, negative significance |
=CEILING("abc",1) | #VALUE! | Text |
Watch out for
- Superseded by CEILING.MATH; use that in new workbooks.
- Positive number with negative significance returns #NUM! in this older function.
- Rounds negatives differently from CEILING.MATH.
Where it works
- Excel
- CEILING is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
- Google Sheets
- Same name, but behaves differently. Sheets treats the factor as optional and handles negative inputs a bit differently.
Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .