CEILING, FLOOR and MROUND: rounding to a multiple in Excel vs LibreOffice Calc

Three functions in the Excel library round a number to a multiple of something rather than to a number of decimal places: CEILING rounds to a multiple in one direction, FLOOR in the other, and MROUND to the nearest one. They are not ROUND (which rounds to decimal places) and not INT or TRUNC (which drop to an integer) — they take a significance or multiple argument, and that second argument is where the cross-engine trouble lives. Every difference on this page appears only when a sign is negative or when the two arguments disagree in sign.

There are also modern replacements: CEILING.MATH and FLOOR.MATH, which take an explicit mode argument controlling the negative-number direction, and CEILING.PRECISE, which ignores the sign of the significance entirely. Microsoft lists plain CEILING and FLOOR in its Compatibility category; our function dataset records that same category for both. The short version of this page is that the compatibility-category pair is the pair your inherited workbooks are full of, and the .MATH/.PRECISE pair is the one that came through our runs clean.

What the legacy Mode argument is

Excel’s CEILING and FLOOR take two arguments, Number and Significance, and Microsoft’s documented rules for the negative cases are asymmetric. For CEILING: “If number is negative, and significance is negative, the value is rounded down, away from zero” (worked example CEILING(-2.5,-2) = -4), and “If number is negative, and significance is positive, the value is rounded up towards zero” (worked example CEILING(-2.5,2) = -2). For FLOOR the both-negative case goes the other way: Microsoft’s worked example is FLOOR(-2.5,-2) = -2, toward zero. That opposition is deliberate, not a bug, and it is the first thing people get wrong when they hand-check a migration.

LibreOffice Calc’s versions of the same two functions accept an optional third argument, Mode, which Excel’s legacy functions do not have at all. Mode only has an effect when Number and Significance are both negative; for every other input it is inert. LibreOffice’s own help states that for CEILING, when Mode is zero or omitted, negative numbers are rounded up toward zero, with the worked example CEILING(-45.67;-2;0) = -44; and that for FLOOR, when Mode is zero or omitted, negative numbers are rounded down away from zero, with the worked example FLOOR(-45.67;-2;0) = -46. Read side by side with Microsoft’s rules, the two help pages describe opposite defaults for identical two-argument formula text — in both directions, mirror-imaged. That is a documentation-level divergence, and it is what our two -45.67 test cases were written to catch.

Legacy CEILING: executed results

All six cases match. Every one of them returned the identical value in 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3.

Legacy FLOOR: executed results

Five of the six match. The mismatched-sign row is the one that forks, and it forks into a different error rather than a different number — the identical =FLOOR(2.5,-2) row also appears in our wider #NUM! vs #VALUE! writeup, where it sits alongside fifteen other formulas that change error identity the same way. All six FLOOR rows, including the #VALUE!, were identical in 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3.

The default-Mode divergence that did not reproduce

This is the part worth reading slowly, because it cuts against our own hypothesis and we are not going to bury it.

Our corpus contains two cases built specifically to expose the Mode default described above, both flagged in the test file as quirk candidates. In each, the expected value recorded in the test is Microsoft’s documented result, not LibreOffice’s:

• =CEILING(-45.67,-2), expected -46. The test’s note derives that from Microsoft’s both-negative rule: -45.67 / -2 = 22.835, ceiling 23, 23 × -2 = -46. The note then records that LibreOffice’s own help documents -44 for the same call, and states explicitly that if Calc returned -44, the resulting mismatch flag would be the intended signal of a real divergence and must not be “fixed” by editing the expectation.
• =FLOOR(-45.67,-2), expected -44, the mirror image: derived from Microsoft’s worked FLOOR(-2.5,-2) = -2 toward-zero example (22.835 truncated toward zero is 22, 22 × -2 = -44), against LibreOffice’s documented -46.

So the expected column for both cases is Excel’s documented behaviour, and the predicted divergence was that LibreOffice would disagree with it. It did not. Our harness read back -46 for the CEILING case and -44 for the FLOOR case — both matching the Excel-documented expectation, and both identical in 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3. On this measurement the two engines agree, and the apparent conflict between the two vendors’ help pages did not turn into a difference in computed values.

What we can and cannot conclude from that: our harness writes each formula into an .xlsx workbook with no cached result and has LibreOffice Calc recalculate it, so what these two rows establish is that a two-argument CEILING or FLOOR arriving from an Excel file computes the Excel-documented direction in Calc. We have not run the same formula typed directly into a native Calc document, so we make no claim about that path, and we make no claim about what an explicit third argument does — there is no Mode-supplying case in our corpus. If you are hand-porting formulas rather than opening a workbook, treat the help-page difference as unresolved and test it yourself.

MROUND: the silent one

Four rows agree; the mismatched-sign row does not, and it is the worst-behaved case in the whole family because there is no error to notice — Excel documents #NUM! and Calc hands back a plausible 6. All five rows were identical in 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3. We have written that case up on its own, with the arithmetic behind the 6 and the sign guards that make it safe, in MROUND returns a silent wrong number in LibreOffice, not an error — so we will not re-derive it here.

One footnote on the =MROUND(1.3,0.2) row: Microsoft’s worked example says 1.4, and our corpus records the expectation as the true nearest IEEE-754 double (1.4000000000000001, which is 0.2 × 7). Calc returned 1.4 and the case is scored as matching within our float tolerance. That is a binary-floating-point display artefact, not a cross-engine difference.

CEILING.MATH, FLOOR.MATH and CEILING.PRECISE: all clean

The modern variants replace the implicit legacy behaviour with an explicit third argument. In CEILING.MATH and FLOOR.MATH, Mode again affects only negative numbers, and any nonzero value reverses the default direction. Crucially the two defaults are mirror images of each other, which is the detail people misremember: CEILING.MATH defaults toward zero for negatives, FLOOR.MATH defaults away from zero.

Eight for eight, and every one of these values was identical in 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3 — including both explicit-mode rows. This is the strongest practical result on the page: the negative-number direction that the legacy pair leaves to an engine default is honoured identically in Calc once you state it in the formula.

CEILING.PRECISE goes further and ignores the significance sign altogether, always rounding toward positive infinity:

These three match, but the version claim here is narrower than everywhere else on this page: CEILING.PRECISE was added to our corpus later and appears only in our 25.8.7.3 result file. We make no claim about 24.2.0.3, 24.8.7.2 or 25.2.0.3 for it, because we did not run it there. Our function dataset records CEILING, CEILING.MATH, CEILING.PRECISE, FLOOR, FLOOR.MATH and MROUND as documented in Google Sheets’ function list; none of them has been run in Sheets by us, so that column says so throughout.

The error-code split matters for your guards

Two of the three divergences on this page are about error identity rather than arithmetic, and that has a specific consequence for defensive formulas. A blanket =IFERROR(FLOOR(a,b),fallback) keeps working after migration, because it catches any error and does not care which. A targeted guard does not: Microsoft documents ERROR.TYPE as returning 6 for #NUM! and 3 for #VALUE!, so a check written as =IF(ERROR.TYPE(x)=6, ...) around a FLOOR stops firing in Calc, where the same failure arrives as #VALUE!. And ERROR.TYPE has its own divergence on top of that — measured in ERROR.TYPE does not map every error to a code in LibreOffice — so error classifiers need checking as a class, not one formula at a time. The broader #NUM!-becomes-#VALUE! pattern is in #NUM! vs #VALUE!: Excel vs LibreOffice.

MROUND is the exception that breaks even the blanket guard: IFERROR cannot catch =MROUND(5,-2) in Calc because nothing fails. Sign mismatches there have to be tested for, not caught.

How to migrate safely

Move legacy calls to the explicit-mode variants. Where a formula rounds negative numbers, =CEILING(n,s) becomes =CEILING.MATH(n,s,mode) and =FLOOR(n,s) becomes =FLOOR.MATH(n,s,mode), with the mode written out rather than left to a default: 0 (or omitted) keeps each function’s own default direction, any nonzero value reverses it. Our runs show all eight .MATH cases, including both explicit-mode ones, computing the documented value in every LibreOffice build we tested — so this rewrite removes the argument about defaults instead of betting on it. Note that CEILING.MATH, FLOOR.MATH and CEILING.PRECISE are stored in .xlsx under the _xlfn. prefix, which is normal and is how our harness wrote them.

Guard sign mismatches yourself. The three cases where the engines actually part company are all mismatched-sign inputs. If the significance or multiple can be negative while the number is positive, or vice versa, decide what should happen rather than inheriting an engine’s answer: =MROUND(n, ABS(m) * SIGN(n)) forces agreement, and =IF(SIGN(n)<>SIGN(m), NA(), MROUND(n,m)) makes the bad input loud. The same shape works around FLOOR.

Do not branch on which error you got. Replace ERROR.TYPE equality checks with IFERROR or ISERROR wherever the specific error identity is not genuinely load-bearing, and where it is, test it in the target engine first.

Keep the positive cases in perspective. Nothing on this page suggests rewriting ordinary =CEILING(price, 0.05) pricing formulas: every all-positive case we ran matched, across four builds. The review list is negative arguments and mixed signs.

Honest limits

The Excel column throughout is Microsoft’s documented behaviour as recorded in our test corpus — including the two -45.67 values, which our corpus derives from Microsoft’s stated rules and worked examples rather than from a run. We did not run Excel, and nothing here is an Excel measurement. Google Sheets has not been run through our harness at all; where we say a function is documented in Sheets, that is a claim about our dataset’s record of Google’s function list, not an observed result. The LibreOffice column is executed output, and every version statement traces to result files from LibreOffice Calc 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3 — except CEILING.PRECISE, which we have run only in 25.8.7.3. Each case is recalculated from a workbook with no cached values, with canaries proving the recalculation happened; see our methodology.

Check before you migrate