LibreOffice collapses 41 documented error conditions into #VALUE!

Excel has five everyday error codes and uses them to mean different things. #NUM! is a number that is out of the function’s domain. #DIV/0! is a divisor of zero. #REF! is a reference that points nowhere. #N/A is a lookup that found nothing. #VALUE! is the wrong type of thing entirely.

LibreOffice Calc has all five codes too, and returns them correctly most of the time. But there is a large, systematic class of conditions where it returns #VALUE! and the documentation says something else. We have now executed enough of the corpus to put a number on it.

Google Sheets, executed against the same cases, returned the documented error code on 40 of the 41. So this is not a two-against-one disagreement about what an error “should” be. On this particular axis LibreOffice is the outlier, and Sheets and Microsoft’s documentation agree with each other almost perfectly.

Executed: all 41 cases

Grouped by the error code Microsoft documents, then by function. The Excel column is Microsoft’s documented behaviour as recorded in our test corpus — it is the yardstick, and we do not run Excel. The Google Sheets column is executed output from dated Drive-import runs; rows marked † ran on 2026-08-31 and the rest on 2026-08-29. The LibreOffice column is Calc 25.8.7.3, and every row returned #VALUE! in the other three builds too.

The one row where Google Sheets is not the documented answer

=RRI(0,10000,11000) is a three-way split, and it is the only one in the table. RRI computes the equivalent interest rate for the growth of an investment; Microsoft documents #NUM! when nper is zero. Google Sheets returned #DIV/0! on 2026-08-31. LibreOffice 25.8.7.3 returned #VALUE!. Three engines, three different codes for the same degenerate input.

Sheets’ answer is arguably the more honest description of what happened — the rate formula divides by nper — but it is not the documented one, and a branch keyed on either code will misfire in one engine or the other. The companion case =RRI(96,0,11000), a present value of zero, does return the documented #NUM! in Sheets, so this is a per-condition difference rather than a whole function behaving differently.

And the two rows that go the other way

LibreOffice is not simply funnelling every failure into #VALUE!. Two executed cases in the corpus return a non-matching error that is not #VALUE!, and one of them inverts the pattern on this page exactly:

ISPMT with nper = 0 is documented as #DIV/0!, is #DIV/0! in Sheets, and is #NUM! in LibreOffice — the exact reverse of the substitution the rest of this page is about. (Microsoft’s ISPMT page documents no error condition at all; the #DIV/0! in that row is derived from the interest formula the page does publish, which divides by nper, and our corpus marks it as derived rather than quoted.) So “LibreOffice always says #VALUE!” is a useful rule of thumb and a false statement; you cannot predict the code, you can only look it up. The FILTER row is a different story again, about an error code that LibreOffice does not have at all, and it has its own write-up: FILTER with no results returns #CALC! in Excel and #N/A in LibreOffice.

Why it happens

Internally LibreOffice maps a broad class of out-of-domain and invalid-argument conditions onto a single error state — err:502, “invalid argument” — which surfaces to the sheet as #VALUE!. Excel and Google Sheets keep the domain failure (#NUM!), the divisor failure (#DIV/0!), the reference failure (#REF!) and the not-found failure (#N/A) as distinct outcomes.

All three engines agree that these formulas are invalid. They disagree only on which word to print, and every valid input to the same functions matches exactly — =LN(1), =SQRT(16), =LARGE(A1:A5,2), =VLOOKUP("a",A1:B3,2,FALSE), Microsoft’s own documented MIRR, VDB, NORM.DIST and PDURATION examples. It is strictly the error path that diverges. That is what makes this easy to miss and expensive to hit: a workbook’s happy path travels perfectly, and only the edge-case branches change meaning.

What actually breaks: error-branch logic

Nothing on this page changes a number. What changes is which branch of an error handler fires.

IFERROR and ISERROR are safe. They catch every error code, so they behave identically before and after migration. =IFERROR(LN(x),0) returns 0 whether the inner failure was #NUM! or #VALUE!. If a workbook uses nothing but these two, this whole page is trivia.

ERROR.TYPE is where it bites. Microsoft documents ERROR.TYPE as returning 1 for #NULL!, 2 for #DIV/0!, 3 for #VALUE!, 4 for #REF!, 5 for #NAME?, 6 for #NUM! and 7 for #N/A. A formula written =IF(ERROR.TYPE(x)=6,"out of range",...) is keyed on #NUM!. Run it in LibreOffice against any of the 32 #NUM! rows above and the error arrives as #VALUE! — type 3 — so the branch silently never fires and the formula falls through to whatever the ELSE was. The code numbers themselves have their own divergences, which we cover in ERROR.TYPE codes across engines.

ISNA and IFNA are the sharpest case. Both MODE rows are documented as #N/A — the “no repeated value” condition — and both come back as #VALUE! in LibreOffice. A guard written =IF(ISNA(MODE(range)),"no mode",MODE(range)) works in Excel and Sheets and stops working in Calc, where the #VALUE! sails straight past ISNA and lands in the user’s face. Same for =IFNA(VLOOKUP(...),"not found") over the out-of-range-column case, which is documented #REF! and executes as #VALUE!.

Conditional formatting and data validation inherit the problem, because they are usually built on the same ISERROR/ISNA/ERROR.TYPE vocabulary. A red-highlight rule keyed on #N/A simply stops highlighting.

How to migrate safely

Search for the discriminating functions, not for the failing ones. The 29 functions in the table are not the risk — they fail in every engine. The risk is wherever a formula asks which error it got. Grep the workbook for ERROR.TYPE, ISNA(, IFNA(, and ISERR( (which excludes #N/A and so is also code-sensitive). Those hits are the migration work; everything else is noise.

Rewrite discriminating guards as broad ones where the distinction does not earn its keep. Most ISNA wrappers exist to mean “if this did not work, show a dash”, not to distinguish a missing lookup from a bad argument. IFERROR expresses that and is portable.

Where the distinction genuinely matters, test the input instead of the error. If a model really needs to tell “out of range” apart from “wrong type”, check the condition before the call — =IF(k>COUNT(range),"out of range",LARGE(range,k)) — rather than inspecting the wreckage afterwards. That is portable by construction, and it is also the only approach that survives an engine changing its mind later.

Do not build on the substitution. It would be tempting to key a LibreOffice-targeted workbook on ERROR.TYPE(x)=3 now that you know the code. Two of the cases on this site show why that is a bad bet: ISPMT already goes the other way, and =SORT(A1:A1) over an empty cell returned 0, then #VALUE!, then 0 again across three consecutive LibreOffice releases. Error identity is the least stable thing in a spreadsheet engine.

Related pages on this site

This page is the systematic count. Several individual slices of it have their own write-ups with more detail on the function in question:

• Excel’s #NUM! errors arrive as #VALUE! in LibreOffice — the first sixteen cases we found, covering the logarithm, square-root and rank functions.
• VLOOKUP and HLOOKUP with an out-of-range index — two of the three #REF! rows.
• OFFSET off the edge of the sheet — the third.
• DGET and MODE.SNGL — the multiple-match and no-repeats conditions.
• DATEDIF across engines — the reversed-date row, plus DATEDIF’s other quirks.
• CEILING, FLOOR and MROUND — the mismatched-sign row.
• DOLLARDE and DOLLARFR — four of the rows above, in a bond-pricing context.
• IFERROR around INDIRECT to a missing sheet — the same theme from the wrapper’s side.

Honest limits

The counts on this page are counts of our corpus, not of Excel. 954 cases over 299 functions is a lot of ground but it is not every function and it is nowhere near every input. A function with no error-condition case in our set contributes nothing to the 41, and its absence is not evidence of good behaviour.

The Excel column is Microsoft’s documented behaviour as recorded per case in our test corpus, and the ERROR.TYPE code numbers quoted above are documented too. We do not run Excel, and no value in that column is a measurement. A handful of the documented error values in our corpus are marked as derived rather than quoted — where Microsoft’s page documents no error condition at all and the error follows from the rules the page does state — and each such case says so in its own note.

The Google Sheets column is executed output from dated runs, because Sheets has no version number to pin. A formula-only .xlsx carrying no cached results goes to Google Drive, is recalculated by Google’s engine, and comes back as .xlsx for readback, with a deterministic =1111+2222 canary in every sheet proving the recalculation happened. Sixteen of the rows above ran on 2026-08-31 and are marked †; the other twenty-five ran on 2026-08-29.

The LibreOffice column is executed output from Calc 25.8.7.3, with 24.2.0.3, 24.8.7.2 and 25.2.0.3 agreeing on every row. Those four builds are the only ones we tested, so “stable across versions” means stable across those four.

Finally, the err:502 explanation is our reading of why the engine behaves this way, not something we measured. What we measured is the code each formula returned. See our methodology for how recalculation is proven rather than assumed, the LibreOffice version support page for the cross-release picture, and the spreadsheet error reference for what each code means.

Check before you migrate