ERROR.TYPE does not map every error to a code in LibreOffice
ERROR.TYPE exists to turn an error into a number you can branch on: 1 for
#NULL!, 2 for #DIV/0!, 3 for #VALUE!,
4 for #REF!, 5 for #NAME?, 6 for
#NUM!, 7 for #N/A. Five of those seven behave identically in
LibreOffice Calc. Two do not: the cases Microsoft documents as 4 and 6
return #N/A in LibreOffice — the same thing ERROR.TYPE returns when
you hand it something that is not an error at all.
The surprise
A dashboard that classifies failures with ERROR.TYPE does not merely mis-label the
#REF! and #NUM! classes in LibreOffice — it stops classifying them.
ERROR.TYPE hands back #N/A, which is itself an error, so it propagates
through whatever arithmetic or lookup you wrapped around it. A classifier turns into a new error
source.
Executed results
The #NULL! row uses A1:A2 = 1, 2 and C1:C2 = 3, 4, so the
space intersection of two non-overlapping ranges is a genuine #NULL!.
| Formula | Excel, desktop (documented) | Google Sheets (executed 2026-08-29) | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =ERROR.TYPE(OFFSET(A1,-1,0)) | 4 | 4 | #N/A |
| =ERROR.TYPE(SQRT(-1)) | 6 | 6 | #N/A |
| =ERROR.TYPE(A1:A2 C1:C2) | 1 | #ERROR! | 1 |
| =ERROR.TYPE(1/0) | 2 | 2 | 2 |
| =ERROR.TYPE("a"+1) | 3 | 3 | 3 |
| =ERROR.TYPE(qwertyzz) | 5 | 5 | 5 |
| =ERROR.TYPE(NA()) | 7 | 7 | 7 |
| =ERROR.TYPE(5) | #N/A | #N/A | #N/A |
The Excel column is the documented code mapping recorded in our test corpus from Microsoft's
ERROR.TYPE reference page; we did not run desktop Excel. The LibreOffice column is what our
harness computed in LibreOffice Calc 25.8.7.3. Note the last row: #N/A is the documented
answer for a non-error argument, which is exactly why the two divergent rows are so awkward —
in LibreOffice, “this is a #REF!” and “this is not an error”
come back indistinguishable.
Excel for the web is a separate application from the desktop product, and it is the third engine
we execute. Recalculated on OneDrive on 2026-09-01 it reproduced the documented mapping on all eight
rows — 1, 2, 3, 4, 5,
6, 7 and #N/A — so the two codes LibreOffice cannot
return, and the #NULL! row Sheets answers with #ERROR!, are both measured
facts in a Microsoft engine now rather than documentation alone.
Consistent across LibreOffice versions
Both divergent cases returned #N/A in 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3, and
the five matching codes returned 1, 2, 3, 5 and
7 in every build. Stable behaviour across every version we have executed.
Why it happens
The two divergent rows are downstream of a difference we measured separately. In LibreOffice,
=OFFSET(A1,-1,0) is not a #REF! at all — our executed run returns
#VALUE! — and =SQRT(-1) is not #NUM! either; standalone
=SQRT(-16) returns #VALUE! in our run. So the errors that Excel would label
4 and 6 do not exist here in the first place. What is still surprising is
that ERROR.TYPE does not then report code 3 for them, given that
=ERROR.TYPE("a"+1) — a plain #VALUE! — does return
3. The observable conclusion from our data is that LibreOffice tracks these failures as
distinct internal conditions that display as #VALUE! but are not mapped onto any of the
seven Excel codes, so ERROR.TYPE falls through to #N/A. We have not
inspected LibreOffice's source to confirm the mechanism; the behaviour is what our harness measured.
The two upstream differences have their own guides:
OFFSET past the sheet edge
and #NUM! vs #VALUE! domain errors.
How to migrate safely
Treat ERROR.TYPE as non-portable and replace it with the predicates that are.
ISERROR, ISERR, ISNA, IFERROR and
IFNA all matched their documented behaviour in our LibreOffice 25.8.7.3 run, including
the subtle ones: =ISERR(NA()) returned FALSE and
=IFNA(10/0,"na") correctly passed the #DIV/0! through rather than catching
it. A branch of the shape =IF(ISNA(x),"missing",IF(ISERROR(x),"broken",x)) carries the
same meaning in every engine in the table above, where =IF(ERROR.TYPE(x)=4,...) does
not.
If you must keep ERROR.TYPE, at minimum wrap it —
=IFERROR(ERROR.TYPE(x),0) — so the unmapped cases degrade to a sentinel instead of
injecting a fresh #N/A into the surrounding formula. And do not read a low code count as
reassurance after migrating: a report that shows zero #REF!-class failures in
LibreOffice may simply be unable to see them.
Honest limits
The Excel codes are Microsoft's documented mapping as recorded in our test corpus, not values we
executed in Excel. The Google Sheets column is executed output from a Drive import on 2026-08-29. Sheets returns
Excel’s documented code on seven of the eight rows — including the 4 and
6 that LibreOffice misses — and diverges only on the intersection row, where it
cannot parse A1:A2 C1:C2 at all and answers #ERROR!.
The LibreOffice column is executed output from LibreOffice Calc 25.8.7.3, reproduced in our
24.2.0.3, 24.8.7.2 and 25.2.0.3 runs.
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.