TYPE reports booleans and arrays differently in LibreOffice
TYPE reports a code for the kind of value in a cell — 1 for a number, 2 for text, 4
for a logical, 16 for an error, 64 for an array. Two of those codes disagree between engines. Excel
documents =TYPE(TRUE) as 4; LibreOffice Calc returns 1. Excel
documents =TYPE(A1:A3) as 64; LibreOffice returns 1. Any logic
that branches on those codes changes meaning after migration.
The surprise
LibreOffice types a boolean as a plain number (code 1) rather than a logical
(code 4), and types a multi-cell range by the value it resolves to rather than as an
array (code 1 instead of 64). Both are facets of the same theme:
in LibreOffice a boolean simply is the number it equals.
Executed results
| Formula | Excel (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =TYPE(TRUE) | 4 | Not yet executed | 1 |
| =TYPE(A1:A3) | 64 | Not yet executed | 1 |
| =TYPE(A1) with A1=5 | 1 | Not yet executed | 1 |
| =TYPE(A1) with A1="abc" | 2 | Not yet executed | 2 |
| =TYPE(1/0) | 16 | Not yet executed | 16 |
The Excel column holds the documented codes from our test corpus; we did not run Excel. The LibreOffice column is what our harness computed in LibreOffice Calc 25.8.7.3. Numbers, text and errors classify identically in both engines — only the boolean and the array-reference cases diverge.
Consistent across LibreOffice versions
We ran these through 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3. =TYPE(TRUE) returned
1 and =TYPE(A1:A3) returned 1 in every build. This is stable
behaviour, not a single-release quirk.
Why it happens
This is the “booleans are numbers” theme in another guise. Internally LibreOffice
stores TRUE as the number 1, so TYPE classifies it as a number
and returns code 1. For a range reference, LibreOffice resolves the argument to a single
value (the relevant cell, here a number) before TYPE sees it, so it reports 1
rather than Excel's dedicated array code 64. If this treatment of booleans-as-numbers is
biting you elsewhere, see our companion guides on
ISNUMBER and booleans
and on
COUNT and booleans.
How to migrate safely
Do not use TYPE(x)=4 to detect a boolean — it never matches in LibreOffice. Use
=ISLOGICAL(x), which is purpose-built for the test and behaves consistently. Likewise do
not rely on TYPE(range)=64 to detect that an argument is an array; that check is not
portable. In general, prefer the dedicated IS... predicates
(ISNUMBER, ISTEXT, ISLOGICAL, ISERROR) over
switching on a numeric TYPE code when a workbook must run in both engines.
Honest limits
The Excel codes are Microsoft's documented behaviour, not values we executed in Excel. Google Sheets is not yet run through our harness, so that column is left honest.