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. Google Sheets and LibreOffice values shown are executed output from our test harness (Sheets via Drive import on 2026-09-01; LibreOffice from the pinned builds named in each table); Excel values in these guides are Microsoft’s documented behavior for the desktop product — we do not run desktop Excel. Excel for the web is a separate application which we do execute (most recently 2026-09-01); its measured results are on the individual function pages rather than in these guide tables. For the shorter, catalog-style version of these findings across every tested function, see the quirks list, and for the cases where the three engines we execute disagree with each other rather than with the documentation, the silent divergences table.

• MAXA, MINA, AVERAGEA, VARPA vs MAX, MIN, AVERAGE, VAR.P

  The A-variants count text as 0 and TRUE as 1; the plain ones ignore them. Executed: LibreOffice implements all four A-variants faithfully, but its VAR.P returns 8.2222 on {4, 8, TRUE} where Microsoft documents 4 and Google Sheets returns 4 - a silent wrong number in all four builds tested.

  Functions: MAXA, MINA, AVERAGEA, VARPA, MAX, MIN, AVERAGE, VAR.P, VAR, VARA, VARP, VAR.S, ISNUMBER, TYPE

• ACOT of a negative number is off by pi in Google Sheets versus Excel and LibreOffice

  Executed evidence: =ROUND(ACOT(-2),9) is -0.463647609 in Google Sheets but 2.677945045 in Excel for the web and in all four LibreOffice builds. The two branches differ by exactly pi, 180 degrees, with no error anywhere. Dates, per-engine provenance and the portable fix.

  Functions: ACOT, ATAN, DEGREES, ATAN2

• AGGREGATE works in Excel & LibreOffice but is missing from Google Sheets

  AGGREGATE (SUM/AVERAGE/etc. that ignores errors and hidden rows) is in Excel and executes in LibreOffice, but Google Sheets returns #NAME? — it has no AGGREGATE. The Sheets workarounds with SUM(IFERROR) and SUBTOTAL.

  Functions: AGGREGATE, SUBTOTAL, IFERROR

• Which LibreOffice version for VSTACK, TEXTSPLIT, TAKE & DROP?

  VSTACK, HSTACK, TEXTSPLIT, TAKE and DROP return #NAME? in LibreOffice 24.2, 24.8 and 25.2 - our executed runs show all fourteen working only from 25.8.7.3. Full version map by wave.

  Functions: VSTACK, HSTACK, TEXTSPLIT, TAKE, DROP, CHOOSECOLS, CHOOSEROWS, TOCOL, TOROW, WRAPCOLS, WRAPROWS, TEXTAFTER, TEXTBEFORE, EXPAND, FILTER, LET, SEQUENCE, SORT, SORTBY, UNIQUE, XLOOKUP, XMATCH, RANDARRAY, ARRAYTOTEXT, BYCOL, BYROW, GROUPBY, MAKEARRAY, MAP, PIVOTBY, REDUCE, SCAN

• ARRAYFORMULA in Google Sheets: when you need it, and when it can't help

  Google Sheets uses only the first element of an array passed to a scalar function - our executed run returns 140 for =SUM(SUMIFS(C2:C6,A2:A6,{"North","South"})) against 220 in LibreOffice. ARRAYFORMULA fixes six of nine cases; for SUMIFS criteria it does not, and SUMPRODUCT is the portable form.

  Functions: SUMIFS, SUMIF, SUMPRODUCT, LARGE, LOOKUP, XLOOKUP, MID, SEQUENCE, TEXTJOIN, ARRAYFORMULA

• ATAN2(0,0) is #DIV/0! in Excel but returns 0 in LibreOffice

  ATAN2 of the origin has no defined angle: Excel documents #DIV/0! and Google Sheets returns it too, but LibreOffice (all four builds, executed) silently returns 0. Why the convention differs and how to guard the formula.

  Functions: ATAN2

• Bond and treasury functions after a migration: what actually breaks

  Executed across 26 fixed-income functions and 161 cases: ODDFPRICE and ODDFYIELD are literal stubs in LibreOffice, TBILLPRICE prices a two-year bill, INTRATE's default basis loses a day, and MDURATION is wrong on actual/actual. 83 of 84 documented #NUM! cases arrive as #VALUE!. Google Sheets matched 127 of 161.

  Functions: ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, TBILLPRICE, TBILLEQ, TBILLYIELD, INTRATE, MDURATION, DURATION, ACCRINT, ACCRINTM, YIELD, YIELDDISC, YIELDMAT, PRICE, PRICEDISC, PRICEMAT, RECEIVED, DISC, COUPDAYS, COUPNCD, COUPNUM, DAYS360, YEARFRAC, ERROR.TYPE, IFERROR

• CEILING, FLOOR & MROUND: Excel vs LibreOffice rounding

  Rounding to a multiple with negative numbers: what the legacy CEILING/FLOOR Mode argument does, why the documented default divergence did not reproduce in four LibreOffice builds, and the #NUM! vs #VALUE! split.

  Functions: CEILING, CEILING.MATH, CEILING.PRECISE, FLOOR, FLOOR.MATH, MROUND, ROUND, INT, TRUNC

• 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_.

  Functions: CHAR, UNICHAR, CLEAN

• CHOOSE out of range returns #NUM! in Google Sheets, not #VALUE!

  An out-of-range index in CHOOSE errors everywhere, but Google Sheets returns #NUM! where Excel documents #VALUE! and LibreOffice executes #VALUE!. Why the error code forks and how to migrate.

  Functions: CHOOSE

• CONCAT takes exactly two arguments in Google Sheets

  Google Sheets' CONCAT accepts only two scalar values - our executed run returns #N/A for =CONCAT("a","b","c") and =CONCAT(A1:A3), which LibreOffice evaluates to abc and xyz. Use TEXTJOIN or CONCATENATE instead.

  Functions: CONCAT, CONCATENATE, TEXTJOIN, JOIN

• 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. Executed Google Sheets agrees with Excel. Why, and how to migrate safely.

  Functions: COUNT, COUNTIF

• DAY(1), MONTH(1) and YEAR(1): Excel disagrees with Sheets and LibreOffice

  Serial number 1 is Jan 1 1900 in Excel but Dec 31 1899 in both Google Sheets and LibreOffice (executed), so DAY/MONTH/YEAR of a raw serial silently disagree. Why, and how to migrate.

  Functions: DAY, MONTH, YEAR, DATE

• DATEDIF across Excel, Google Sheets and LibreOffice

  DATEDIF's "MD" bug is reproduced exactly in LibreOffice, so migrating will not fix it — but end-before-start returns #VALUE! instead of Excel's documented #NUM!.

  Functions: DATEDIF, YEARFRAC, DAYS, NETWORKDAYS

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

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

  Functions: DGET, MODE.SNGL, MODE, IFERROR, DCOUNT

• DOLLARDE and DOLLARFR: fractional bond prices across engines

  Executed: all eight valid DOLLARDE/DOLLARFR conversions match Microsoft's documented values in LibreOffice 25.8.7.3 and Google Sheets, including the documented truncation of a non-integer fraction. All four error cases return #VALUE! in LibreOffice where #DIV/0! or #NUM! is documented; Google Sheets returns the documented code.

  Functions: DOLLARDE, DOLLARFR, ERROR.TYPE, IFERROR, ISERROR, ISNUMBER, INT

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

  LibreOffice returns #N/A where Excel documents 4 and 6; executed Google Sheets returns 4 and 6 but cannot parse the intersection operator at all. Error-classification logic breaks on migration.

  Functions: ERROR.TYPE, OFFSET, SQRT, ISERROR, ISERR, ISNA, IFERROR, IFNA

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

  An empty FILTER is #CALC! in Excel but #N/A in LibreOffice, and TEXTAFTER's fallback still errors. Executed Google Sheets has no TEXTBEFORE/TEXTAFTER at all - it returns #NAME?.

  Functions: FILTER, TEXTAFTER, TEXTBEFORE, IFERROR, ISNA

• FVSCHEDULE: LibreOffice drops text in the rate schedule

  Microsoft documents any non-numeric value in FVSCHEDULE's schedule as #VALUE!. Executed, LibreOffice 25.8.7.3 returns 1.09 for =FVSCHEDULE(1,A1:A2) with A2 = "x" - a compounding period silently dropped - while Google Sheets returns #VALUE! as documented.

  Functions: FVSCHEDULE, MROUND, POWER, CHAR, UNICHAR, ISNUMBER, IFERROR, SUMPRODUCT, AMORDEGRC, TBILLPRICE, FORECAST.ETS.CONFINT

• Google-only functions: what ports to Excel and LibreOffice, and what does not

  Executed: 47 functions Google documents and neither Microsoft nor LibreOffice does, 189 cases, 183 of them #NAME? in LibreOffice on all four pinned builds after five- and nine-spelling probes. QUERY and ARRAYFORMULA do not port; the operator functions do exactly; REGEXMATCH, REGEXTEST and REGEX are three different functions with three regex flavours.

  Functions: QUERY, ARRAYFORMULA, SPLIT, JOIN, FLATTEN, SORTN, ARRAY_CONSTRAIN, COUNTUNIQUE, ISBETWEEN, ISEMAIL, ISURL, ISDATE, EPOCHTODATE, MARGINOFERROR, AVERAGE.WEIGHTED, TO_DATE, TO_TEXT, TO_PERCENT, TO_DOLLARS, TO_PURE_NUMBER, ADD, MINUS, MULTIPLY, DIVIDE, POW, EQ, NE, GT, GTE, LT, LTE, UMINUS, UPLUS, UNARY_PERCENT, REGEXMATCH, REGEXEXTRACT, REGEXREPLACE, REGEXTEST, REGEX, GOOGLEFINANCE, GOOGLETRANSLATE, IMPORTDATA, IMPORTFEED, IMPORTHTML, IMPORTRANGE, IMPORTXML, SPARKLINE, AI, TEXTSPLIT, TEXTJOIN, TOCOL, CONFIDENCE.T, QUOTIENT, DOLLAR

• IFERROR doesn't catch INDIRECT's #REF! in LibreOffice

  =IFERROR(INDIRECT("'Nope'!B2"),"missing") returns #REF! instead of the fallback in every LibreOffice Calc build we ran - 24.2.0.3 through 25.8.7.3. Executed Google Sheets returns the fallback.

  Functions: IFERROR, INDIRECT, ISERROR, ISREF, IFNA

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

  In Excel a logical value is not a number, so ISNUMBER(TRUE) is FALSE - executed Google Sheets agrees. LibreOffice returns TRUE. The silent divergence and a safe rewrite.

  Functions: ISNUMBER, ISLOGICAL

• 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.

  Functions: LAMBDA, MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY, ARRAYTOTEXT

• LENB and CJK text: 4 in Sheets and LibreOffice, 2 in Excel for the web

  Microsoft's archived LEN/LENB page says LENB counts 2 bytes per character only under a DBCS default language, otherwise 1. Executed under a non-DBCS locale, =LENB("日本") returned 4 in Google Sheets and all four LibreOffice builds - but 2, the documented answer, in Excel for the web. Desktop Excel stays documentation only.

  Functions: LENB, LEN, T, LEFT, MID, LEFTB, RIGHTB, MIDB, FINDB, SEARCHB, REPLACEB

• Every error code LibreOffice reports as #VALUE!

  Across our 2,334-case executed corpus, 272 cases in 135 functions return #VALUE! in LibreOffice Calc 25.8.7.3 where Microsoft documents #NUM! (244), #N/A (15), #DIV/0! (10) or #REF! (3). Google Sheets returns the documented code on 239 of the 249 it has a function for. Identical in all four LibreOffice builds tested.

  Functions: DATEDIF, DGET, DOLLARDE, DOLLARFR, FLOOR, HLOOKUP, LARGE, LN, LOG, LOG10, MIRR, MODE, MODE.SNGL, NORM.DIST, NORM.INV, NORM.S.INV, OFFSET, PDURATION, PERCENTILE.EXC, PERCENTILE.INC, QUARTILE.INC, RRI, SKEW, SMALL, SQRT, VDB, VLOOKUP, WEEKDAY, YEARFRAC, ISPMT, ERROR.TYPE, IFERROR, ISERROR, ISNA, IFNA, COT, DSTDEV, DVAR, ZTEST, Z.TEST, CHISQ.TEST, CHITEST, PROB, FDIST, PERCENTRANK.INC, PERCENTRANK.EXC, MDURATION, DURATION, HYPGEOM.DIST, PRICE, RECEIVED, YIELD, YIELDMAT, F.TEST, FTEST, SKEW.P, STEYX, SUMX2MY2, SUMX2PY2, SUMXMY2, T.TEST, TTEST, FILTER

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

  Excel documents MROUND(5,-2) as #NUM! and executed Google Sheets returns #NUM! too; LibreOffice silently returns 6 instead. Why a silent wrong number is worse than an error.

  Functions: MROUND

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

  Domain errors Microsoft documents as #NUM! - LN(0), SQRT(-16), out-of-range LARGE - return #VALUE! in LibreOffice. Google Sheets returns #NUM! on all 16, executed: LibreOffice is alone.

  Functions: LN, LOG, LOG10, SQRT, LARGE, SMALL, PERCENTILE.EXC, PERCENTILE.INC, QUARTILE.INC, WEEKDAY, YEARFRAC, FLOOR, DATEDIF, MODE, ERROR.TYPE, ISNA, ISERROR, IFERROR

• NUMBERVALUE works in Excel & LibreOffice but is missing from Google Sheets

  NUMBERVALUE (text-to-number with explicit decimal and group separators) is in Excel and executes in LibreOffice, but Google Sheets returns #NAME? — it has no NUMBERVALUE. The Sheets workaround with SUBSTITUTE + VALUE for locale-formatted numbers.

  Functions: NUMBERVALUE, VALUE, SUBSTITUTE

• 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.

  Functions: OFFSET, IFERROR, INDEX

• PERCENTRANK significance: Excel truncates, Sheets and LibreOffice round

  Executed: PERCENTRANK.INC(A2:A11,4) returns 0.556 in LibreOffice 25.8.7.3 and Google Sheets, and 0.555 in Excel for the web, where Microsoft publishes 0.555, and at significance 1 PERCENTRANK.EXC returns 0.4 against a published 0.3. The ranking arithmetic matches everywhere; only the significance step differs, and only on repeating decimals.

  Functions: PERCENTRANK, PERCENTRANK.INC, PERCENTRANK.EXC, PERCENTILE.INC, QUARTILE.INC, ROUND, ERROR.TYPE, IFERROR, ISERROR

• POWER(-8,1/3) is documented as #NUM! but returns about -2 in every engine we run

  Executed evidence: =POWER(-8,1/3) returns -1.9999999999999998 in Excel for the web (2026-09-01), -2 in Google Sheets (2026-08-29) and -2 in all four LibreOffice builds, while Microsoft documents #NUM! for a negative base with a non-integer exponent. The real divergence is =POWER(0,0): 1 in Sheets and LibreOffice, #NUM! in the web build.

  Functions: POWER

• SORT descending in Google Sheets: is_ascending vs sort_order

  Excel's SORT takes a numeric sort_order (1/-1); Google Sheets' third argument is a boolean is_ascending. Executed, =SORT(A2:A4,1,-1) returns 10, 20, 50 in Google Sheets against 50, 20, 10 in LibreOffice 25.8.7.3 - ascending, with no error anywhere. The fix is FALSE.

  Functions: SORT, SORTBY, SORTN, IMSQRT, FORECAST.ETS.CONFINT, RAND.NV, RANDBETWEEN.NV

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

  Excel adds a number typed as text inside SUM's arguments and executed Google Sheets returns 6 too; LibreOffice returns #VALUE! for the identical formula. Why they differ and how to migrate.

  Functions: SUM, VALUE

• TRIM strips tabs and line breaks in Google Sheets but keeps them in Excel and LibreOffice

  Executed evidence: =TRIM(CHAR(9)&"Hello") keeps the tab in Excel for the web and in all four LibreOffice builds, but Google Sheets strips it (and line feeds too). Non-breaking spaces survive TRIM everywhere. The portable fix, with dates and per-engine provenance.

  Functions: TRIM, CHAR, CLEAN, SUBSTITUTE, UNICHAR

• 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.

  Functions: TYPE, ISLOGICAL, ISNUMBER, ISTEXT, ISERROR

• When the documentation is wrong: 29 vendor doc defects found by execution

  Independent derivation across 586 executed functions found 29 places where a vendor's own page is contradicted by its own inputs, its own table, or the live engine: 23 Microsoft, 5 Google, 1 LibreOffice. Includes T.INV.2T's doubly-wrong Remark, DISC's stale figure, ISDATE's page against the live engine, and RAWSUBTRACT's help against LibreOffice's own result.

  Functions: SUMX2MY2, T.INV.2T, TINV, DISC, SEC, SECH, TBILLEQ, TDIST, ISPMT, MAXA, MINA, VARPA, DPRODUCT, DSTDEV, DSTDEVP, BESSELI, BESSELJ, BESSELK, CHISQ.INV, DBCS, JIS, GAUSS, AMORLINC, ODDLPRICE, ODDLYIELD, FORECAST.ETS.STAT, TRIMRANGE, ISDATE, EPOCHTODATE, DIVIDE, AVERAGE.WEIGHTED, TO_PURE_NUMBER, RAWSUBTRACT, CEILING, QUOTIENT

• 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.

  Functions: VLOOKUP, HLOOKUP, IFERROR, ISERROR, INDEX, MATCH, XLOOKUP

• Your function exists but your file cannot say so: _xlfn storage tokens

  Executed: eight IM* functions LibreOffice implements return #NAME? on all four builds because it cannot read Excel's _xlfn. token for them, while COT and CSC only work under that same prefix. Eleven LibreOffice aliases are erased by its own exporter, and a token change between 24.2 and 24.8 makes a 24.8-written file open as #NAME? in 24.2.

  Functions: IMCOT, IMCSC, IMSEC, IMSECH, IMSINH, IMTAN, IMCOSH, IMCSCH, COT, COTH, CSC, CSCH, SEC, SECH, T.INV.2T, BAHTTEXT, MUNIT, PHI, POISSON.DIST, ISO.CEILING, ROT13, ISLEAPYEAR, DAYSINMONTH, DAYSINYEAR, MONTHS, WEEKS, WEEKSINYEAR, YEARS, EASTERSUNDAY, ERRORTYPE, CONVERT_OOO, RAND.NV, RANDBETWEEN.NV, CURRENT, DDE, XLOOKUP, FILTER, SORT

• openpyxl and _xlfn.XLOOKUP: why your XLOOKUP opens as #NAME?

  openpyxl writes formula text verbatim, so =XLOOKUP(...) is stored in the .xlsx under its plain name. Executed 2026-09-12: that cell is #NAME? in Excel for the web and in all four pinned LibreOffice builds, while =_xlfn.XLOOKUP(...) returns 20 in Excel for the web and in LibreOffice 24.8, 25.2 and 25.8.

  Functions: XLOOKUP, VLOOKUP, XMATCH, FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, IFS, SWITCH, TEXTJOIN, CONCAT, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, LAMBDA