Skip to content

Excel function · Math

Excel CEILING.MATH function

CEILING.MATH rounds a number up to the next multiple of the value you give: 130 units to 132 in cases of 12, 18.20 to 18.50 as a multiple of 0.50. It is available in Excel 2013 and later.

In Turkish Excel: TAVANAYUVARLA.MATEMATİK

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

English Excel=CEILING.MATH(number, [significance], [mode])
Turkish Excel=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

ArgumentRequiredDescription
numberRequiredThe number to round.
significanceOptionalThe multiple the result should be, e.g. 12 or 0.5. If omitted, 1.
modeOptionalAffects 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.

D2fx=CEILING.MATH(B2,C2)
Round a requirement up to whole cases: example table
ABCD
1ItemRequiredCase SizeTo Order
2Water (0.5 L)13012132
3Fruit Juice482448
4Lemonade20124216
5Iced Tea752080
  • 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).

C2fx=CEILING.MATH(B2,0.5)
How the mode argument affects negative amounts: example table
ABCD
1MovementAmountDefault (Towards Zero)Mode 1 (Away from Zero)
2Sale18.2018.5018.50
3Refund-12.30-12.00-12.50
4Sale7.758.008.00
5Refund-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/96 as the significance.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add an Order Qty column to the order list that rounds each required quantity up to a multiple of the case size

Convert to table

Bring 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.

Related functions

All math functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free