MROUND returns a silent wrong number in LibreOffice, not an error
Microsoft documents that MROUND returns #NUM! when its number and its
multiple have opposite signs: =MROUND(5,-2) is an error in Excel. LibreOffice Calc does
not error — it returns 6. That is the dangerous kind of divergence, because it is not an
error you will notice; it is a plausible-looking wrong number sitting quietly in a cell.
The surprise
An out-of-spec MROUND that Excel flags with #NUM! is computed anyway by
LibreOffice, which returns 6. No error, no warning — just a value that disagrees with
Excel. A silent wrong number survives a spreadsheet review far more easily than a red error cell.
Executed results
| Formula | Excel (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =MROUND(5,-2) | #NUM! | Not yet executed | 6 |
| =MROUND(10,3) | 9 | Not yet executed | 9 |
| =MROUND(-10,-3) | -9 | Not yet executed | -9 |
The #NUM! is the documented-expected value in our test corpus; we did not run Excel.
The 6 is what our harness computed by recalculating the workbook in LibreOffice Calc
25.8.7.3. The two same-sign rows agree in both engines — only the mismatched-sign case forks, and it
forks into a number rather than an error.
Consistent across LibreOffice versions
We ran =MROUND(5,-2) through 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3. Every build
returned 6. This is stable, long-standing behaviour, which makes it exactly the sort of
silent difference an audit needs to surface before a workbook is trusted in a new engine.
Why it happens
Excel treats opposite signs between number and multiple as an invalid request and stops with
#NUM!. LibreOffice skips that guard and evaluates the rounding arithmetic directly:
rounding 5 to the nearest multiple of -2 works out to
ROUND(5 / -2) × -2 = ROUND(-2.5) × -2 = -3 × -2 = 6. The math is
internally consistent; it just answers a question Excel refuses to answer at all.
How to migrate safely
Because the divergence is a value and not an error, IFERROR will not save you here —
there is no error to catch. Guard the sign relationship yourself so both engines produce the same
result, or reject the input explicitly:
=MROUND(number, ABS(multiple) * SIGN(number)) forces the multiple to share the
number's sign before rounding.
=IF(SIGN(number)<>SIGN(multiple), NA(), MROUND(number, multiple)) makes the
mismatch loud instead of silent.
Most importantly, treat any MROUND with a variable-sign multiple as a review item
when migrating: it is a headline example of the silent-value class of bug, where the spreadsheet
recalculates without complaint and the total is simply wrong.
Honest limits
The Excel result is Microsoft's documented behaviour, not a value we executed in Excel. Google Sheets is not yet run through our harness, so that column is left honest.