CEILING.MATH Function (LibreOffice Calc)

• Introduced: LibreOffice 5.0, explicitly stated in official CEILING.MATH Help.
• Availability: Supported in LibreOffice 26.8.1.1, confirmed by the versioned function registry. This is not a final supported version.
• Scope: Desktop Calc; formula names and separators depend on language/settings. Other spreadsheet products need separate compatibility checks.
• Checked: October 14, 2026. Documentation/source evidence; saved 25.2.3.2 native observations and supplementary development-build XLSX tests are scoped separately below.

Validation scope: The saved native observations in this guide were produced in LibreOffice Calc 25.2.3.2 on Linux on October 13, 2026. This revision verified the corresponding saved ODS values against the result ledgers; it did not rerun that historical installation. Supplementary numerical cases were recalculated through imported XLSX files in LibreOfficeDev 26.8.0.0.alpha0 (build 2c87e51eeaa2b413ff4ae097b2705eea1995d8e5). That development-build/import check is separate from direct formula entry and from the stable-release support evidence.

Its companion function is FLOOR.MATH, which moves positive numbers down to a step rather than up.

CEILING.MATH(Number [; Significance [; Mode]])

Number is required; Significance and Mode are optional, in that order. In a typical English-language Calc setup, type =CEILING.MATH(3.45;-3) using the semicolon separator. This produces 6, not an error, because CEILING.MATH ignores the sign of its Significance argument. This exact formula and result were confirmed in LibreOffice Calc 25.2.3.2. See official Ceiling functions Help, CEILING.MATH section, syntax/notes and examples (checked 2026-10-13).

There is no fourth argument. Do not confuse Mode with a request to round to a number of decimal places: use a Significance such as 0.05 for nickel increments or 0.25 for quarter increments.

For a positive number, CEILING.MATH selects the nearest allowed multiple at or above the input. For a negative number, the direction is set by Mode: the default shifts upward toward zero; nonzero mode shifts downward away from zero. The significant step is always ABS(Significance) in the documented behavior.

For nonzero step s = ABS(Significance), one independent mathematical description is:

• Number >= 0 or Mode = 0: s × ceil(Number / s).
• Number < 0 and Mode <> 0: s × floor(Number / s).

The word “ceil” above is the mathematical ceiling operation, not a recommendation to replace CEILING.MATH with the legacy Calc CEILING function. For values already at an exact multiple, no increase is needed.

Tested comparison of opposite signs and modes

Verification: all listed formulas were entered natively and matched independent Decimal arithmetic on 2026-10-13. The official Help independently gives the first five behaviors; the rest are from the saved native test records. The sequence also demonstrates why “round up” does not always mean “increase the absolute value.”

A service team needs 17.2 metres of material but can order only in 5-metre increments. To calculate the required quantity:

=CEILING.MATH(17.2;5)

Result: 20. The result includes enough material without under-ordering; the extra 2.8 metres is the difference between 20 and 17.2. This follows the function’s rounding rule and is also covered by a native test with the same numerical arguments nested inside IF:

=IF(1=1;CEILING.MATH(17.2;5);999)

Result: 20. The second formula demonstrates nesting rather than adding a new rounding rule. For a worksheet, put a required amount in A2 and pack increment in B2 and use =CEILING.MATH(A2;B2). If the increment may be empty or zero, validate it before calculation.

Cell-reference example: negative balance and sign-sensitive adjustments

Enter -45.67 in J2 and 2 in K2. Then:

=CEILING.MATH(J2;K2)
=CEILING.MATH(J2;K2;1)

The first returns -44 (toward zero); the second returns -46 (away from zero). Both cell-reference formulas were executed natively. Which direction is appropriate depends on the financial or operational policy; the function itself does not decide whether a more conservative balance is desirable.

Fractional thresholds

=CEILING.MATH(3.45;0.5) returns 3.5. Negative significance gives the same result: =CEILING.MATH(3.45;-0.5) also returns 3.5. This is useful for batch-size or pricing rules that operate in half units. Do not assume a visible decimal is stored exactly as a binary float; check final formatting and needed precision.

Expression inputs rather than constants

=CEILING.MATH(9.5/2;2)

Result: 6 (9.5 ÷ 2 = 4.75, then up to the next multiple of 2). The numerical result was natively executed. In spreadsheet models, use a calculation as Number when you need to round a derived quantity rather than a raw input.

Validate step sizes explicitly

For A2 as a quantity and B2 as a configuration step, this proposed validation pattern prevents a zero step from silently producing a zero output:

=IF(B2=0;NA();CEILING.MATH(A2;B2))

This specific validation wrapper was not separately executed in the native test workbook. NA() intentionally signals an invalid setup instead of guessing a usable increment. If blank cells, text or negative quantities carry a special business meaning, add validation suited to that policy before rounding.

CEILING.MATH compared with CEILING and CEILING.PRECISE

=CEILING.MATH(-45.67;2;1) returns -46 in the tested version; the mode is decisive. The CEILING.PRECISE function has no such mode and normally rounds negative values toward zero, regardless of the significance sign. CEILING.XCL exists for older Excel interoperability and follows its own mixed-sign behavior. The documented version/export and sign conventions appear on the official Ceiling functions Help page; these other functions were not exhaustively cross-tested here.

The formulas below use English function names, a decimal point, and semicolons (;) between arguments. Calc’s display depends on the language and Tools → Options → LibreOffice Calc → Formula settings; function names and argument separators can be changed. If your installation uses translated names or commas, adjust input to match your configuration rather than changing the mathematics. The saved validation workbook was executed with Calc 25.2.3.2 on Linux, with expressions entered through the native UNO formula interface. LibreOffice Formula Options.

The inputs are numbers, not strings. For cell-based use, place the value in J2 and the step in K2, then substitute those addresses for numerical literals. Currency amounts are numbers with currency formatting applied afterward; avoid rounding already rounded results repeatedly in a chain of formulas.

• Omitting an optional argument differs from passing zero. Omit the optional arguments to use their documented defaults. Avoid passing an empty text cell unintentionally.
• Step size: use a meaningful nonzero significance for business calculations. In Calc 25.2.3.2, a zero significance returned 0 for the tested positive and negative examples; this can silently conceal a missing configuration value. A safer pattern is =IF(K2=0;NA(); CEILING.MATH(A2;K2) ) when zero is invalid in your workflow.
• Text in a numeric field: the native tests with literal text such as "abc" returned #VALUE! (internal error 519). Check that imported numeric cells are actually numeric and that your locale’s decimal separator is correct.
• Too many arguments: a tested four-argument call returned Err:504. Use one required argument plus at most two optional arguments.
• Binary floating-point precision: apparently exact decimal steps can exhibit tiny numerical differences. For currency, choose the correct increment and use formatting or a final ROUND(...;2) when needed for output; do not interpret extra displayed digits as new precision.
• Large input and rounding magnitude: choose Significance in the same units as Number (for example, metres with metres, currency units with currency units). A time is stored as a fraction of a day, so a 15-minute step is 15/1440, not 15.
• Nested formulas and references: Calc successfully evaluated both in the native tests. If a copied formula gives a surprising result, inspect the underlying cell values, not only their displayed formatting.
• Negative values: select Mode intentionally; never apply positive-only intuition to refunds, deficits or balances.

The Document Foundation’s official Calc Help documents this .MATH function as available since LibreOffice 5.0 and designed for Microsoft Excel 2013-or-later formula interoperability. The Help explicitly says it is not part of OpenDocument 1.3 OpenFormula and identifies a COM.MICROSOFT compatibility namespace. A function being saved in an ODS workbook is not proof that every other ODF implementation calculates it. Current LibreOffice Help pages may document features that differ by installation, locale or version. The native results supplied here establish behavior only for the tested LibreOffice Calc 25.2.3.2/Linux environment, not for Excel, Google Sheets, OpenOffice, mobile editions or future releases. For cross-software exchange, save a copy, reopen in the actual target program, and compare sign, step and mode behavior against expected values.

• CEILING.MATH syntax, version history and examples
• Release-pinned function registration
• Calc formula language and separators