What it does
The second argument (significance) is the multiple to round to; without it, it is 1 and the number goes up to the next whole number. =CEILING.MATH(130,12) gives 132 and =CEILING.MATH(18.2,0.5) gives 18.5. A number that is already a multiple stays as it is.
For negative numbers the default direction is towards zero: -12.3 becomes -12. If the third argument (mode) is anything other than zero, negative numbers are rounded away from zero, to -13. Mode has no effect on positive numbers.
The older CEILING function is kept for compatibility; it requires the multiple, and for negative numbers the direction depends on the sign of that multiple. To round down use FLOOR.MATH, to round to the nearest multiple use MROUND, and to round up by digits use ROUNDUP.
Syntax
=CEILING.MATH(number, [significance], [mode])=TAVANAYUVARLA.MATEMATİK(sayı; [anlam]; [mod])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number | Required | The number to round. |
significance | Optional | The multiple the result should be, e.g. 12 or 0.5. If omitted, 1. |
mode | Optional | Affects negative numbers only. 0 or omitted: towards zero (-12.3 → -12). Any other number: away from zero (-12.3 → -13). |
Examples
1Round a requirement up to whole cases
The products are sold by the case; the quantity to order is the requirement rounded up to the next multiple of the case size. Column C is given as the significance.
=CEILING.MATH(B2,C2)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Required | Case Size | To Order |
| 2 | Water (0.5 L) | 130 | 12 | 132 |
| 3 | Fruit Juice | 48 | 24 | 48 |
| 4 | Lemonade | 201 | 24 | 216 |
| 5 | Iced Tea | 75 | 20 | 80 |
- D2 (filled down to D5)
=CEILING.MATH(B2,C2)
The requirement of 130 bottles is topped up to the next multiple of 12: 132. The fruit juice requirement is already a multiple of 24, so it stays the same: 48.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2How the mode argument affects negative amounts
The amounts are rounded up to a multiple of 0.50. Column C has no mode; column D uses mode 1. The difference shows only for negative amounts (refunds).
=CEILING.MATH(B2,0.5)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Movement | Amount | Default (Towards Zero) | Mode 1 (Away from Zero) |
| 2 | Sale | 18.20 | 18.50 | 18.50 |
| 3 | Refund | -12.30 | -12.00 | -12.50 |
| 4 | Sale | 7.75 | 8.00 | 8.00 |
| 5 | Refund | -4.60 | -4.50 | -5.00 |
- C2 (filled down to C5)
=CEILING.MATH(B2,0.5) - D2 (filled down to D5)
=CEILING.MATH(B2,0.5,1)
On the sale rows the two columns agree: 18.50 on the first row. For refunds the default direction is towards zero, giving -12.00 for the first refund; with mode 1, the same refund becomes -12.50.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Negative result moves towards zero
Cause: A negative amount was rounded and the result moved towards zero (-12 for -12.3) when a larger magnitude was expected.
Fix: Set the third argument to 1:
=CEILING.MATH(B2,1,1).- #VALUE!
Cause: One of the arguments is text that cannot be read as a number.
Fix: Make sure the cells really hold numbers.
- #NAME?
Cause: The file was opened in a version without CEILING.MATH (Excel 2010 or earlier).
Fix: In those versions use CEILING:
=CEILING(B2,12).
Good to know
- To price shipping in half-kilo steps, round the weight up to a multiple of 0.5:
=CEILING.MATH(B2,0.5). - To round times up to the next quarter hour, use
1/96as the significance.
In Nexell
Get Nexell to do it from a sentence
Add an Order Qty column to the order list that rounds each required quantity up to a multiple of the case size
Convert to tableBring your order list into the tool and type this. Nexell adds the column with real Excel formulas and shows it in the preview; in the downloaded file, the order quantity changes when the requirement does.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between CEILING.MATH and ROUNDUP?
ROUNDUP works by digits (decimals, tens, hundreds). CEILING.MATH rounds up to a multiple you choose: 12, 0.5 or 250. With negative numbers, ROUNDUP moves away from zero, while CEILING.MATH moves towards zero unless you set the mode.