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 (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =ERROR.TYPE(OFFSET(A1,-1,0)) | 4 | Not yet executed | #N/A |
| =ERROR.TYPE(SQRT(-1)) | 6 | Not yet executed | #N/A |
| =ERROR.TYPE(A1:A2 C1:C2) | 1 | Not yet executed | 1 |
| =ERROR.TYPE(1/0) | 2 | Not yet executed | 2 |
| =ERROR.TYPE("a"+1) | 3 | Not yet executed | 3 |
| =ERROR.TYPE(qwertyzz) | 5 | Not yet executed | 5 |
| =ERROR.TYPE(NA()) | 7 | Not yet executed | 7 |
| =ERROR.TYPE(5) | #N/A | Not yet executed | #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 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.
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 both engines, 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. Google Sheets is not yet run through our harness, so that column is left honest. 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.