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 (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =LN(0) | #NUM! | Not yet executed | #VALUE! |
| =LN(-5) | #NUM! | Not yet executed | #VALUE! |
| =LOG(0) | #NUM! | Not yet executed | #VALUE! |
| =LOG10(0) | #NUM! | Not yet executed | #VALUE! |
| =SQRT(-16) | #NUM! | Not yet executed | #VALUE! |
| =LARGE(A1:A5,6) | #NUM! | Not yet executed | #VALUE! |
| =SMALL(A1:A5,6) | #NUM! | Not yet executed | #VALUE! |
| =PERCENTILE.EXC(A1:A10,0) | #NUM! | Not yet executed | #VALUE! |
| =PERCENTILE.EXC(A1:A10,1) | #NUM! | Not yet executed | #VALUE! |
| =PERCENTILE.INC(A1:A3,1.5) | #NUM! | Not yet executed | #VALUE! |
| =QUARTILE.INC(A1:A3,5) | #NUM! | Not yet executed | #VALUE! |
| =WEEKDAY(DATE(2008,2,14),99) | #NUM! | Not yet executed | #VALUE! |
| =YEARFRAC(DATE(2012,1,1),DATE(2012,7,30),9) | #NUM! | Not yet executed | #VALUE! |
| =FLOOR(2.5,-2) | #NUM! | Not yet executed | #VALUE! |
| =DATEDIF(DATE(2024,1,10),DATE(2024,1,1),"D") | #NUM! | Not yet executed | #VALUE! |
| =MODE(A1:A4) | #N/A | Not yet executed | #VALUE! |
The Excel column is the documented-expected error from our test corpus; we did not run 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!).
Both engines agree 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 in both, 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 both engines.
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 is not yet run through our harness, so we
leave that column blank rather than guess.