Excel's #NUM! errors arrive as #VALUE! in LibreOffice
Microsoft documents a whole family of domain errors — the logarithm of zero, the square root of
a negative, an out-of-range rank — as #NUM!. Run the identical formulas in LibreOffice
Calc and they come back as #VALUE! instead. The formula still fails, but the error
identity changes, and any formula that branches on which error it got will quietly change
meaning after migration.
The surprise
LibreOffice funnels most invalid-argument and out-of-domain conditions into a single
#VALUE!, where Excel reserves #NUM! for numeric domain problems. A broad
IFERROR wrapper keeps working, but a targeted check like
ERROR.TYPE(x)=6 (Excel's code for #NUM!) stops firing, because the error
now arrives as #VALUE! (documented code 3).
Executed results
| Formula | Excel, desktop (documented) | Google Sheets (executed 2026-08-29) | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =LN(0) | #NUM! | #NUM! | #VALUE! |
| =LN(-5) | #NUM! | #NUM! | #VALUE! |
| =LOG(0) | #NUM! | #NUM! | #VALUE! |
| =LOG10(0) | #NUM! | #NUM! | #VALUE! |
| =SQRT(-16) | #NUM! | #NUM! | #VALUE! |
| =LARGE(A1:A5,6) | #NUM! | #NUM! | #VALUE! |
| =SMALL(A1:A5,6) | #NUM! | #NUM! | #VALUE! |
| =PERCENTILE.EXC(A1:A10,0) | #NUM! | #NUM! | #VALUE! |
| =PERCENTILE.EXC(A1:A10,1) | #NUM! | #NUM! | #VALUE! |
| =PERCENTILE.INC(A1:A3,1.5) | #NUM! | #NUM! | #VALUE! |
| =QUARTILE.INC(A1:A3,5) | #NUM! | #NUM! | #VALUE! |
| =WEEKDAY(DATE(2008,2,14),99) | #NUM! | #NUM! | #VALUE! |
| =YEARFRAC(DATE(2012,1,1),DATE(2012,7,30),9) | #NUM! | #NUM! | #VALUE! |
| =FLOOR(2.5,-2) | #NUM! | #NUM! | #VALUE! |
| =DATEDIF(DATE(2024,1,10),DATE(2024,1,1),"D") | #NUM! | #NUM! | #VALUE! |
| =MODE(A1:A4) | #N/A | #N/A | #VALUE! |
The Excel column is the documented-expected error from our test corpus; we did not run desktop Excel.
The LibreOffice column is what our harness computed in LibreOffice Calc 25.8.7.3. MODE is
the odd one out: Microsoft documents a no-duplicates MODE as #N/A, not
#NUM!, yet LibreOffice still collapses it to #VALUE! — so the same
migration hazard applies from a different starting error.
Consistent across LibreOffice versions
Every row above returned #VALUE! in all four builds we tested — 24.2.0.3, 24.8.7.2,
25.2.0.3 and 25.8.7.3. This is not a regression in one release; it is a stable design difference in
how Calc reports invalid arguments.
Why it happens
Internally LibreOffice maps out-of-domain math and invalid arguments to a single error condition
(err:502, “invalid argument”), which surfaces as #VALUE!. Excel instead
distinguishes a numeric domain failure (#NUM!) from a type failure (#VALUE!).
Every engine agrees the formula is invalid; they disagree only on the label. The valid rows in these
same functions — =LN(1), =SQRT(16), =LARGE(A1:A5,2) — match
exactly everywhere, so it is strictly the error path that diverges.
How to migrate safely
A broad wrapper is portable: =IFERROR(LN(x),0) catches #NUM! and
#VALUE! alike, so it behaves the same after migration. The trap is error-type
discrimination. A formula written as =IF(ERROR.TYPE(x)=6,"out of range",x) keys on
#NUM!; in LibreOffice the error is #VALUE! (documented type 3), so the
branch silently never fires. The MODE case is sharper still: a guard built on
=ISNA(MODE(range)) catches Excel's #N/A but not LibreOffice's
#VALUE!, while =ISERROR(...) catches both. Prefer IFERROR and
ISERROR over ERROR.TYPE- or ISNA-specific branching whenever a
workbook has to run in more than one engine.
Honest limits
The Excel errors and the ERROR.TYPE codes cited here are Microsoft's documented
behaviour, not values we executed in Excel. Google Sheets was executed on 2026-08-29 via Drive import and returned Excel’s documented
error on every one of the sixteen rows. Excel for the web — a separate application from the desktop
product, and the third engine we execute — returned the documented error on all sixteen rows
too when we recalculated the corpus on OneDrive on 2026-09-01: fifteen #NUM!s and the
#N/A for =MODE(A1:A4). That makes LibreOffice the sole outlier in this
table, measured against two executed engines and the documentation rather than inferred.
Check before you migrate
A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01). Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.