Math function
Excel CEILING function
CEILING rounds a number up to the next multiple you choose. =CEILING(38,15) is 45. Use it to bill in 15-minute blocks, order in whole packs, or round prices up to the next 50 cents.
=CEILING(number, significance)Arguments
number- The number to round up.
significance- The multiple to round up to, such as 15, 0.5 or "0:15".
CEILING examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | |
|---|---|---|---|
| 1 | Job | Minutes | Rate per hour |
| 2 | Call-out | 38 | 90 |
| 3 | Repair | 52 | 90 |
| 4 | Consult | 15 | 120 |
Download this sheet as an Excel practice file (.xlsx)
Result: 45
Bills 38 minutes as 45, the next 15-minute block.
Result: 67.5
The charge for those three blocks at $90 an hour: 67.5.
Result: 15
A number already on a multiple stays put: 15 minutes is one block.
Result: #NUM!
A positive number with a negative significance returns #NUM!.
How to use CEILING
- Click the cell for the result.
- Type =CEILING( and the number or cell.
- Add a comma and the multiple to round up to.
- Press Enter and fill down.
Common CEILING mistakes
Getting #NUM!
A positive number needs a positive significance. CEILING(52,-15) is #NUM!; use CEILING(52,15).
Negative numbers round toward zero
CEILING(-38,15) returns -30, because up means toward zero for negatives. CEILING.MATH(-38,15,1) rounds away from zero to -45.
Using it when you want the nearest block
CEILING always goes up, so 31 minutes becomes 45. Use MROUND for the nearest multiple.
CEILING FAQ
How do I round up to the nearest 5 in Excel?
Use =CEILING(A2,5). 12 becomes 15, and 15 stays 15.
What is the difference between CEILING and ROUNDUP?
ROUNDUP rounds up to a number of decimal places, so ROUNDUP(12.31,1) is 12.4. CEILING rounds up to a multiple, so CEILING(12.31,0.25) is 12.5.
How do I round time up to the next 15 minutes in Excel?
Use =CEILING(A2,"0:15") and format the result as h:mm. 9:07 becomes 9:15.
Related functions
FLOORRounds down to a multipleMROUNDRounds to the nearest multipleROUNDUPRounds away from zeroROUNDRounds to digits
Updated