LibreOffice collapses 272 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 272 cases, has a function to run for 249 of them — the other 23 name a function Google does not document, so they come back #NAME? and say nothing about error codes. Of those 249 it returned the documented error code on 239. 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.

Where the 272 live

The substitutions are spread thin rather than concentrated: 43 functions contribute one case, 55 contribute two, 30 contribute three, and only seven contribute four or more (MDURATION with five; DURATION, HYPGEOM.DIST, PRICE, RECEIVED, YIELD and YIELDMAT with four each). That shape is the finding: this is not a handful of badly-behaved functions, it is one engine-wide convention.

One family does dominate by volume. The 26 bond and treasury functions account for 83 of the 272 on their own, because pricing functions document more out-of-domain conditions than anything else in the library — a basis outside 0–4, a frequency other than 1, 2 or 4, settlement on or after maturity, a non-positive price. They have their own write-up in bond and treasury functions after a migration, where 83 of 84 documented #NUM! cases arrive as #VALUE! and the one that does not is worse.

The three non-#NUM! groups are small enough to name in full. #REF! is still exactly three cases, in HLOOKUP, VLOOKUP and OFFSET. #DIV/0! is ten, in DOLLARDE, DOLLARFR, F.TEST (two), FTEST (two), MIRR, SKEW, SKEW.P and STEYX. #N/A is fifteen, in CHISQ.TEST (two), CHITEST (two), MODE, MODE.SNGL, STEYX, SUMX2MY2, SUMX2PY2, SUMXMY2, T.TEST, TTEST and the three FORECAST.ETS variants.

Executed: 41 of the 272, the ones you are most likely to hit

The full list of 272 is in the results files and on the individual function pages; the table below is the everyday subset — logarithms, roots, ranks, lookups and the common statistical and financial functions — and it was the whole of this page when the corpus was smaller. 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 desktop 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 ten rows where Google Sheets is not the documented answer either

Across the 249 substitution cases Google has a function for, ten came back as something other than the documented code. They are worth listing in full, because each one is a place where no two of the three columns agree:

Six of the ten are the same shape: Google declines to treat a documented exclusion as an error at all and returns a number. The two PERCENTRANK significance rows are covered alongside a larger finding about that function in PERCENTRANK significance: Excel truncates, the other two round. PROB is the one row where LibreOffice and Sheets agree with each other and both differ from the documentation.

=RRI(0,10000,11000) remains the cleanest three-way split. 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.

The other 23 of the 272 are cases where Google has no such function — the four ODD* bond functions, the four BESSEL* functions and the three FORECAST.ETS variants. Those return #NAME? in Sheets, which is a statement about coverage and not about error codes, and they are excluded from the 239-of-249 figure above rather than counted as agreement or as disagreement.

And the rows that go the other way

LibreOffice is not simply funnelling every failure into #VALUE!. Twenty-nine executed cases return a non-matching error that is not #VALUE! — but 22 of those are #NAME?, which means the formula never ran at all. Sixteen of the 22 are the complex-number functions IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH and IMTAN, which LibreOffice implements perfectly well but cannot read out of a .xlsx under the token Excel writes for them; that is a file-format problem rather than an error-code one, and it has its own page in your function exists, but your file cannot say so. The remaining six are BINOM.DIST.RANGE (three cases), PHONETIC, REGEXEXTRACT and VALUETOTEXT, which LibreOffice has under no spelling.

That leaves seven cases where LibreOffice really did evaluate the formula and really did pick a different code — and four of them invert the pattern on this page exactly:

The first four are one consistent counter-convention: where the documented failure is a division by zero, LibreOffice reports the domain error #NUM! — the mirror image of the 272 cases where a documented domain error arrives as the type error #VALUE!. Google Sheets returns the documented #DIV/0! on all four.

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 244 #NUM! cases in the count 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 135 functions in the count 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 findings on this site show why that is a bad bet: ISPMT, COT, DSTDEV and DVAR already go 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. 2,334 cases over 586 functions is a lot of ground but it is not every input, and a function with no error-condition case in our set contributes nothing to the 272 — its absence is not evidence of good behaviour. The figures describe the corpus as of 2026-09-01, the most recent execution date in it; they were 41 cases in 29 functions when this page was first written against a 954-case corpus, and the proportion has held almost exactly as the corpus grew (41 of 108 error-expected cases then, 272 of 531 now).

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 desktop 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 in the 41-case table ran on 2026-08-31 and are marked †; the other twenty-five ran on 2026-08-29. Across the full set of 272 the execution dates run from 2026-07-29 to 2026-09-01, and each function page carries its own.

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 one of the 272 substitutions, not merely on the rows shown. 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

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.