Discovered quirks
Every case below is a real, executed formula whose result did not match documented/expected behavior. This is the flagship content of Can I Spreadsheet?: cross-engine divergence that only shows up when you actually run the formula.
55 quirks found across 41 functions.
-
ARRAYTOTEXT LibreOffice Calc
=ARRAYTOTEXT({1,2;3,4})- Actual result
- #NAME?
- Documented / expected
- 1, 2, 3, 4
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Text
MISMATCH vs expected: expected '1, 2, 3, 4', got '#NAME?'
-
ARRAYTOTEXT LibreOffice Calc
=ARRAYTOTEXT({1,2,3})- Actual result
- #NAME?
- Documented / expected
- 1, 2, 3
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Text
MISMATCH vs expected: expected '1, 2, 3', got '#NAME?'
-
ARRAYTOTEXT LibreOffice Calc
=ARRAYTOTEXT({1,2,3},1)- Actual result
- #NAME?
- Documented / expected
- {1,2,3}
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Text
MISMATCH vs expected: expected '{1,2,3}', got '#NAME?'
-
ARRAYTOTEXT LibreOffice Calc
=ARRAYTOTEXT({"a","b"},1)- Actual result
- #NAME?
- Documented / expected
- {"a","b"}
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Text
MISMATCH vs expected: expected '{"a","b"}', got '#NAME?'
-
BYCOL LibreOffice Calc
=BYCOL(A1:B2,LAMBDA(c,SUM(c)))- Actual result
- {#NAME?, #NAME?}
- Documented / expected
- {4, 6}
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Logical
MISMATCH vs expected: value mismatch: expected 4, got '#NAME?'
-
BYROW LibreOffice Calc
=BYROW(A1:B2,LAMBDA(r,SUM(r)))- Actual result
- {#NAME?, #NAME?}
- Documented / expected
- {3, 7}
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Logical
MISMATCH vs expected: value mismatch: expected 3, got '#NAME?'
-
CHAR LibreOffice Calc
=CHAR(0)- Actual result
- _x0000_
- Documented / expected
- #VALUE!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Text
Microsoft docs: CHAR takes "A number between 1 and 255 specifying which character you want." Source: https://support.microsoft.com/en-us/office/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a; MISMATCH vs expected: expected '#VALUE!', got '_x0000_'
-
COUNT LibreOffice Calc
=COUNT(A1:A1)- Actual result
- 1
- Documented / expected
- 0
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
Per Microsoft's COUNT documentation, booleans in a referenced range are excluded from COUNT, unlike booleans typed directly as literal arguments; MISMATCH vs expected: expected 0, got 1
-
COUNT LibreOffice Calc
=COUNT(A1:A6)- Actual result
- 3
- Documented / expected
- 2
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
A3 and A6 are blank, A2 is text, A4 is a boolean cell; only A1=10 and A5=20 are counted; MISMATCH vs expected: expected 2, got 3
-
DATEDIF LibreOffice Calc
=DATEDIF(DATE(2024,1,10),DATE(2024,1,1),"D")- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Date and time
Microsoft docs state end<start raises #NUM!; record engines' ACTUAL error code here since this is a known point of cross-engine divergence; MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
DAY LibreOffice Calc
=DAY(1)- Actual result
- 31
- Documented / expected
- 1
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Date and time
Serial 1 is Jan 1, 1900 in the 1900 date system; MISMATCH vs expected: expected 1, got 31
-
ERROR.TYPE LibreOffice Calc
=ERROR.TYPE(SQRT(-1))- Actual result
- #N/A
- Documented / expected
- 6
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Information
MISMATCH vs expected: expected 6, got '#N/A'
-
ERROR.TYPE LibreOffice Calc
=ERROR.TYPE(OFFSET(A1,-1,0))- Actual result
- #N/A
- Documented / expected
- 4
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Information
MISMATCH vs expected: expected 4, got '#N/A'
-
FILTER LibreOffice Calc
=FILTER(A1:A3,B1:B3>100)- Actual result
- #N/A
- Documented / expected
- #CALC!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Lookup and reference
MISMATCH vs expected: expected '#CALC!', got '#N/A'
-
FLOOR LibreOffice Calc
=FLOOR(2.5,-2)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Compatibility
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
HLOOKUP LibreOffice Calc
=HLOOKUP("a",A1:C2,5,FALSE)- Actual result
- #VALUE!
- Documented / expected
- #REF!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Lookup and reference
MISMATCH vs expected: expected '#REF!', got '#VALUE!'
-
ISNUMBER LibreOffice Calc
=ISNUMBER(TRUE)- Actual result
- True
- Documented / expected
- False
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Information
ISNUMBER(TRUE) = FALSE; use ISLOGICAL to detect booleans instead.; MISMATCH vs expected: expected False, got True
-
LAMBDA LibreOffice Calc
=LAMBDA(x,x*2)(5)- Actual result
- #VALUE!
- Documented / expected
- 10
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Logical
MISMATCH vs expected: expected 10, got '#VALUE!'
-
LAMBDA LibreOffice Calc
=LAMBDA(x,y,x+y)(3,4)- Actual result
- #VALUE!
- Documented / expected
- 7
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Logical
MISMATCH vs expected: expected 7, got '#VALUE!'
-
LAMBDA LibreOffice Calc
=LET(f,LAMBDA(x,x^2),f(4))- Actual result
- #VALUE!
- Documented / expected
- 16
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Logical
MISMATCH vs expected: expected 16, got '#VALUE!'
-
LARGE LibreOffice Calc
=LARGE(A1:A5,6)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
Only 5 values exist; requesting the 6th-largest is out of range; MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
LARGE LibreOffice Calc
=LARGE(A1:A3,0)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
LN LibreOffice Calc
=LN(-5)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
LN LibreOffice Calc
=LN(0)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
Microsoft's LN docs specify the Number argument must be 'the positive real number for which you want the natural logarithm' -- https://support.microsoft.com/en-us/office/ln-function-81fe1ed7-dac9-4acd-ba1d-07a142c6118f -- 0 and negative numbers are outside that domain and both are well-established to raise #NUM!, matching every other Excel logarithm function; MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
LOG LibreOffice Calc
=LOG(-10)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
LOG LibreOffice Calc
=LOG(0)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
LOG10 LibreOffice Calc
=LOG10(-5)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
LOG10 LibreOffice Calc
=LOG10(0)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
MAKEARRAY LibreOffice Calc
=MAKEARRAY(2,2,LAMBDA(r,c,r*c))- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {{1, 2}, {2, 4}}
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Logical
MISMATCH vs expected: value mismatch: expected 1, got '#NAME?'
-
MAP LibreOffice Calc
=MAP(A1:A3,LAMBDA(x,x*2))- Actual result
- {#NAME?, #NAME?, #NAME?}
- Documented / expected
- {2, 4, 6}
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Logical
MISMATCH vs expected: value mismatch: expected 2, got '#NAME?'
-
MODE LibreOffice Calc
=MODE(A1:A4)- Actual result
- #VALUE!
- Documented / expected
- #N/A
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Compatibility
Microsoft's MODE docs state verbatim: 'If the data set contains no duplicate data points, MODE returns the #N/A error value' -- https://support.microsoft.com/en-us/office/mode-function-e45192ce-9122-4980-82ed-4bdc34973120; MISMATCH vs expected: expected '#N/A', got '#VALUE!'
-
MONTH LibreOffice Calc
=MONTH(1)- Actual result
- 12
- Documented / expected
- 1
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Date and time
Serial 1 is Jan 1, 1900 in the 1900 date system; MISMATCH vs expected: expected 1, got 12
-
MROUND LibreOffice Calc
=MROUND(5,-2)- Actual result
- 6
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
MISMATCH vs expected: expected '#NUM!', got 6
-
OFFSET LibreOffice Calc
=OFFSET(A1,-1,0)- Actual result
- #VALUE!
- Documented / expected
- #REF!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Lookup and reference
https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66 -- 'If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.'; MISMATCH vs expected: expected '#REF!', got '#VALUE!'
-
PERCENTILE.EXC LibreOffice Calc
=PERCENTILE.EXC(A1:A10,1)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
PERCENTILE.EXC LibreOffice Calc
=PERCENTILE.EXC(A1:A10,0)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
PERCENTILE.INC LibreOffice Calc
=PERCENTILE.INC(A1:A3,1.5)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
POWER LibreOffice Calc
=POWER(-8,1/3)- Actual result
- -2
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
Confirmed via Microsoft's own community support: entering =(-8)^(2/3) (equivalent power semantics to POWER) returns #NUM! in Excel -- https://answers.microsoft.com/en-us/msoffice/forum/all/excel-calculating-exponential-of-negative-number/5c67eae2-5682-4daf-b3d6-50b22e9919c2 -- note that -8^(1/3) is mathematically -2 as a real cube root, but Excel's POWER/^ implementation does not special-case rational exponents with odd integer denominators and returns #NUM! for any negative base with a non-integer exponent; MISMATCH vs expected: expected '#NUM!', got -2
-
QUARTILE.INC LibreOffice Calc
=QUARTILE.INC(A1:A3,5)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
REDUCE LibreOffice Calc
=REDUCE(0,A1:A3,LAMBDA(a,b,a+b))- Actual result
- #NAME?
- Documented / expected
- 6
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Logical
MISMATCH vs expected: expected 6, got '#NAME?'
-
SCAN LibreOffice Calc
=SCAN(0,A1:A3,LAMBDA(a,b,a+b))- Actual result
- {#NAME?, #NAME?, #NAME?}
- Documented / expected
- {1, 3, 6}
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Logical
MISMATCH vs expected: value mismatch: expected 1, got '#NAME?'
-
SMALL LibreOffice Calc
=SMALL(A1:A5,6)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
Only 5 values exist; requesting the 6th-smallest is out of range; MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
SMALL LibreOffice Calc
=SMALL(A1:A3,0)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Statistical
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
SQRT LibreOffice Calc
=SQRT(-16)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
Microsoft's SQRT docs state verbatim: 'If number is negative, SQRT returns the #NUM! error value' -- https://support.microsoft.com/en-US/Excel/functions/sqrt-function; MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
SUM LibreOffice Calc
=SUM(1,"2",3)- Actual result
- #VALUE!
- Documented / expected
- 6
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Math and trigonometry
Contrast with SUM_text_in_cell_reference_ignored: Excel coerces literal string arguments that look like numbers when they are typed directly into the formula, but does not do so for text found inside a range reference; MISMATCH vs expected: expected 6, got '#VALUE!'
-
TEXTAFTER LibreOffice Calc
=TEXTAFTER("abc","-","none")- Actual result
- #VALUE!
- Documented / expected
- none
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Text
MISMATCH vs expected: expected 'none', got '#VALUE!'
-
TEXTBEFORE LibreOffice Calc
=TEXTBEFORE("abc","-","none")- Actual result
- #VALUE!
- Documented / expected
- none
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Text
MISMATCH vs expected: expected 'none', got '#VALUE!'
-
TRIM LibreOffice Calc
=TRIM(CHAR(160)&"Hello"&CHAR(160))- Actual result
- �Hello�
- Documented / expected
- Hello
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Text
Microsoft docs state explicitly: "by itself, the TRIM function does not remove the nonbreaking space character (which has a decimal value of 160 and is commonly used in web pages as the HTML entity )." Source: https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9; MISMATCH vs expected: expected '\xa0Hello\xa0', got '�Hello�'
-
TYPE LibreOffice Calc
=TYPE(A1:A3)- Actual result
- 1
- Documented / expected
- 64
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Information
MISMATCH vs expected: expected 64, got 1
-
TYPE LibreOffice Calc
=TYPE(TRUE)- Actual result
- 1
- Documented / expected
- 4
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Information
MISMATCH vs expected: expected 4, got 1
-
UNICHAR LibreOffice Calc
=UNICHAR(0)- Actual result
- _x0000_
- Documented / expected
- #VALUE!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Text
MISMATCH vs expected: expected '#VALUE!', got '_x0000_'
-
VLOOKUP LibreOffice Calc
=VLOOKUP("a",A1:B3,5,FALSE)- Actual result
- #VALUE!
- Documented / expected
- #REF!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Lookup and reference
Microsoft docs specify #REF! for out-of-range col_index_num; record engines' ACTUAL error code here since this is a known point of cross-engine divergence; MISMATCH vs expected: expected '#REF!', got '#VALUE!'
-
WEEKDAY LibreOffice Calc
=WEEKDAY(DATE(2008,2,14),99)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Date and time
Microsoft docs: an invalid return_type raises #NUM!.; MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
YEAR LibreOffice Calc
=YEAR(1)- Actual result
- 1899
- Documented / expected
- 1900
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Date and time
Serial 1 is Jan 1, 1900 in the 1900 date system; MISMATCH vs expected: expected 1900, got 1899
-
YEARFRAC LibreOffice Calc
=YEARFRAC(DATE(2012,1,1),DATE(2012,7,30),9)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 25.8.7.3
- Category
- Date and time
Microsoft docs: 'invalid basis values (< 0 or > 4) return a #NUM! error.'; MISMATCH vs expected: expected '#NUM!', got '#VALUE!'