Formula behavior guides

Executed-data writeups of specific cases where Excel, Google Sheets, and LibreOffice Calc give different results for the exact same formula — found by actually running the formula, not by comparing documentation pages. LibreOffice values shown are executed output from our test harness; Excel and Google Sheets values are each vendor’s documented behavior unless a guide says otherwise. For the shorter, catalog-style version of these findings across every tested function, see the quirks list.

• CHAR(0) and UNICHAR(0): Excel #VALUE! vs a NUL character in LibreOffice

  Excel documents CHAR(0) and UNICHAR(0) as #VALUE!. LibreOffice Calc returns a NUL control character instead — the value our harness read back as the literal text _x0000_.

• COUNT counts booleans differently in LibreOffice vs Excel

  The same =COUNT(range) returns a larger number in LibreOffice than in Excel when the range holds a TRUE/FALSE cell. Why it happens and how to migrate safely.

• DAY(1), MONTH(1) and YEAR(1) give different dates in LibreOffice vs Excel

  Serial number 1 is Jan 1 1900 in Excel but Dec 31 1899 under LibreOffice's default null date, so DAY/MONTH/YEAR of a raw serial silently disagree. Why, and how to migrate.

• DGET and MODE.SNGL: #NUM! and #N/A in Excel, #VALUE! in LibreOffice

  DGET with two matching rows is documented as #NUM! in Excel and Google Sheets; MODE.SNGL with no repeats is #N/A in both. LibreOffice Calc returns #VALUE! for each.

• ERROR.TYPE: LibreOffice returns #N/A where Excel returns 4 and 6

  Five of Excel's seven ERROR.TYPE codes match in LibreOffice Calc. The #REF! and #NUM! cases return #N/A instead of 4 and 6 — so error-classification logic breaks on migration.

• FILTER no match: Excel #CALC! vs LibreOffice #N/A

  An empty FILTER is #CALC! in Excel but #N/A in LibreOffice, and TEXTAFTER's if_not_found fallback still errors with #VALUE!. Modern functions that change error identity.

• ISNUMBER(TRUE) is FALSE in Excel but TRUE in LibreOffice

  In Excel a logical value is not a number, so ISNUMBER(TRUE) is FALSE. LibreOffice returns TRUE. The silent divergence, the documented reason, and a safe rewrite.

• LAMBDA, MAP & SCAN: Excel vs LibreOffice

  Excel evaluates LAMBDA(x,x*2)(5) as 10; LibreOffice returns #VALUE! for inline lambdas and #NAME? for MAP, SCAN and REDUCE. What breaks and how to rewrite it.

• MROUND(5,-2): Excel #NUM! vs LibreOffice 6

  Excel documents MROUND(5,-2) as #NUM! because the signs disagree; LibreOffice silently returns 6 instead. Why a silent wrong number is worse than an error.

• #NUM! vs #VALUE!: Excel vs LibreOffice

  Domain errors Microsoft documents as #NUM! — LN(0), SQRT(-16), out-of-range LARGE — return #VALUE! in LibreOffice, silently changing error-handling logic.

• OFFSET off the sheet edge: Excel #REF! vs LibreOffice #VALUE!

  OFFSET(A1,-1,0) runs off the top of the sheet: Microsoft documents #REF!, LibreOffice Calc returns #VALUE!. Ordinary OFFSET usage agrees — only the out-of-bounds case forks.

• POWER(-8,1/3) errors in Excel but returns -2 in LibreOffice

  Excel returns #NUM! for POWER(-8,1/3); LibreOffice quietly returns the real cube root -2. The reason for the fork and how to migrate so both engines agree.

• SUM(1,"2",3) returns 6 in Excel but #VALUE! in LibreOffice

  Excel adds a number typed as text when you put it straight into SUM's arguments; LibreOffice returns #VALUE! for the identical formula. Why they differ and how to migrate.

• TYPE(TRUE): Excel 4 vs LibreOffice 1

  Excel documents TYPE(TRUE) as 4 and TYPE(A1:A3) as 64; LibreOffice returns 1 for both. The booleans-are-numbers theme breaks logic that branches on TYPE codes.

• VLOOKUP bad column index: Excel #REF! vs LibreOffice #VALUE!

  Microsoft documents an out-of-range col_index_num as #REF!; LibreOffice Calc returns #VALUE! instead. The lookup still fails — but every guard written around #REF! stops matching.