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.
86 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- Category
- Text
MISMATCH vs expected: expected '{"a","b"}', got '#NAME?'
-
CHAR LibreOffice Calc
=CHAR(0)- Actual result
- _x0000_
- Documented / expected
- #VALUE!
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- Category
- Information
MISMATCH vs expected: expected 4, got '#N/A'
-
FILTER LibreOffice Calc
=FILTER(A1:B3,A1:A3>1)- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {{2, y}, {3, z}}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: value mismatch: expected 2, got '#NAME?'
-
FILTER LibreOffice Calc
=FILTER(A1:A3,B1:B3>100)- Actual result
- #NAME?
- Documented / expected
- #CALC!
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: expected '#CALC!', got '#NAME?'
-
FILTER LibreOffice Calc
=FILTER(A1:A3,B1:B3>100,"none")- Actual result
- #NAME?
- Documented / expected
- none
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: expected 'none', got '#NAME?'
-
FILTER LibreOffice Calc
=FILTER(A1:A5,B1:B5>2)- Actual result
- {#NAME?, #NAME?, #NAME?}
- Documented / expected
- {c, d, e}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: value mismatch: expected 'c', got '#NAME?'
-
FILTER LibreOffice Calc
=FILTER({1,2,3,4},{1,0,1,0})- Actual result
- {#NAME?, #NAME?}
- Documented / expected
- {1, 3}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: value mismatch: expected 1, got '#NAME?'
-
FLOOR LibreOffice Calc
=FLOOR(2.5,-2)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- Category
- Logical
MISMATCH vs expected: expected 7, got '#VALUE!'
-
LAMBDA LibreOffice Calc
=LET(f,LAMBDA(x,x^2),f(4))- Actual result
- #NAME?
- Documented / expected
- 16
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Logical
MISMATCH vs expected: expected 16, got '#NAME?'
-
LARGE LibreOffice Calc
=LARGE(A1:A5,6)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- Category
- Statistical
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
LET LibreOffice Calc
=LET(rng,{1,2,3},SUM(rng))- Actual result
- #NAME?
- Documented / expected
- 6
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Logical
MISMATCH vs expected: expected 6, got '#NAME?'
-
LET LibreOffice Calc
=LET(x,5,x*2)- Actual result
- #NAME?
- Documented / expected
- 10
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Logical
MISMATCH vs expected: expected 10, got '#NAME?'
-
LET LibreOffice Calc
=LET(a,2,b,a*3,a+b)- Actual result
- #NAME?
- Documented / expected
- 8
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Logical
MISMATCH vs expected: expected 8, got '#NAME?'
-
LET LibreOffice Calc
=LET(x,5,y,x*2)- Actual result
- #NAME?
- Documented / expected
- #VALUE!
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Logical
LET requires pairs plus a trailing calculation; here the last pair has no calc term following it in this deliberately malformed call; MISMATCH vs expected: expected '#VALUE!', got '#NAME?'
-
LN LibreOffice Calc
=LN(-5)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- Category
- Math and trigonometry
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
MODE LibreOffice Calc
=MODE(A1:A4)- Actual result
- #VALUE!
- Documented / expected
- #N/A
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- 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 24.2.7.2
- Category
- Statistical
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
SEQUENCE LibreOffice Calc
=SEQUENCE(5)- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {1, 2, 3, 4, 5}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Math and trigonometry
MISMATCH vs expected: value mismatch: expected 1, got '#NAME?'
-
SEQUENCE LibreOffice Calc
=SEQUENCE(3,1,5,-1)- Actual result
- {#NAME?, #NAME?, #NAME?}
- Documented / expected
- {5, 4, 3}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Math and trigonometry
MISMATCH vs expected: value mismatch: expected 5, got '#NAME?'
-
SEQUENCE LibreOffice Calc
=SEQUENCE(2,3)- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {{1, 2, 3}, {4, 5, 6}}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Math and trigonometry
MISMATCH vs expected: value mismatch: expected 1, got '#NAME?'
-
SEQUENCE LibreOffice Calc
=SEQUENCE(5,1,10,5)- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {10, 15, 20, 25, 30}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Math and trigonometry
MISMATCH vs expected: value mismatch: expected 10, got '#NAME?'
-
SMALL LibreOffice Calc
=SMALL(A1:A5,6)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- Category
- Statistical
MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
SORT LibreOffice Calc
=SORT(A1:B3,2,1)- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {{y, 10}, {z, 20}, {x, 30}}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: value mismatch: expected 'y', got '#NAME?'
-
SORT LibreOffice Calc
=SORT(A1:A5)- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {1, 1, 3, 4, 5}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: value mismatch: expected 1, got '#NAME?'
-
SORT LibreOffice Calc
=SORT(A1:A5,1,-1)- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {5, 4, 3, 1, 1}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: value mismatch: expected 5, got '#NAME?'
-
SORT LibreOffice Calc
=SORT(A1:A1)- Actual result
- #NAME?
- Documented / expected
- 0
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
Blank cell sorts as 0 in numeric context; no error expected; MISMATCH vs expected: expected 0, got '#NAME?'
-
SQRT LibreOffice Calc
=SQRT(-16)- Actual result
- #VALUE!
- Documented / expected
- #NUM!
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- 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("a-b-c","-")- Actual result
- #NAME?
- Documented / expected
- b-c
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: expected 'b-c', got '#NAME?'
-
TEXTAFTER LibreOffice Calc
=TEXTAFTER("a-b-c","-",2)- Actual result
- #NAME?
- Documented / expected
- c
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: expected 'c', got '#NAME?'
-
TEXTAFTER LibreOffice Calc
=TEXTAFTER("a-b-c","-",-1)- Actual result
- #NAME?
- Documented / expected
- c
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
-1 means the last delimiter; text after it is 'c'; MISMATCH vs expected: expected 'c', got '#NAME?'
-
TEXTAFTER LibreOffice Calc
=TEXTAFTER("abc","-","none")- Actual result
- #NAME?
- Documented / expected
- none
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: expected 'none', got '#NAME?'
-
TEXTBEFORE LibreOffice Calc
=TEXTBEFORE("a-b-c","-")- Actual result
- #NAME?
- Documented / expected
- a
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: expected 'a', got '#NAME?'
-
TEXTBEFORE LibreOffice Calc
=TEXTBEFORE("a-b-c","-",2)- Actual result
- #NAME?
- Documented / expected
- a-b
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: expected 'a-b', got '#NAME?'
-
TEXTBEFORE LibreOffice Calc
=TEXTBEFORE("a-b-c","-",-1)- Actual result
- #NAME?
- Documented / expected
- a-b
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
-1 means the last delimiter; text before it is 'a-b'; MISMATCH vs expected: expected 'a-b', got '#NAME?'
-
TEXTBEFORE LibreOffice Calc
=TEXTBEFORE("abc","-","none")- Actual result
- #NAME?
- Documented / expected
- none
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: expected 'none', got '#NAME?'
-
TEXTBEFORE LibreOffice Calc
=TEXTBEFORE("abc","-")- Actual result
- #NAME?
- Documented / expected
- #N/A
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: expected '#N/A', got '#NAME?'
-
TEXTSPLIT LibreOffice Calc
=TEXTSPLIT("a,b,c",",")- Actual result
- {#NAME?, #NAME?, #NAME?}
- Documented / expected
- {a, b, c}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: value mismatch: expected 'a', got '#NAME?'
-
TEXTSPLIT LibreOffice Calc
=TEXTSPLIT("a,,b",",",,TRUE)- Actual result
- {#NAME?, #NAME?}
- Documented / expected
- {a, b}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: value mismatch: expected 'a', got '#NAME?'
-
TEXTSPLIT LibreOffice Calc
=TEXTSPLIT("a,b,c;d",",",";",FALSE,0,"-")- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {{a, b, c}, {d, -, -}}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: value mismatch: expected 'a', got '#NAME?'
-
TEXTSPLIT LibreOffice Calc
=TEXTSPLIT("a,b;c,d",",",";")- Actual result
- {#NAME?, #NAME?, #NAME?, #NAME?}
- Documented / expected
- {{a, b}, {c, d}}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: value mismatch: expected 'a', got '#NAME?'
-
TRIM LibreOffice Calc
=TRIM(CHAR(160)&"Hello"&CHAR(160))- Actual result
- �Hello�
- Documented / expected
- Hello
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- Category
- Information
MISMATCH vs expected: expected 64, got 1
-
TYPE LibreOffice Calc
=TYPE(TRUE)- Actual result
- 1
- Documented / expected
- 4
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Information
MISMATCH vs expected: expected 4, got 1
-
UNICHAR LibreOffice Calc
=UNICHAR(0)- Actual result
- _x0000_
- Documented / expected
- #VALUE!
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Text
MISMATCH vs expected: expected '#VALUE!', got '_x0000_'
-
UNIQUE LibreOffice Calc
=UNIQUE(A1:A5)- Actual result
- {#NAME?, #NAME?, #NAME?}
- Documented / expected
- {1, 2, 3}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: value mismatch: expected 1, got '#NAME?'
-
UNIQUE LibreOffice Calc
=UNIQUE(A1:C2,TRUE)- Actual result
- {#NAME?, #NAME?}
- Documented / expected
- {1, 2}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
Column A and column C are identical (1,1); unique columns are {1,2} and {1,1} but by_col dedups the repeated column, leaving 2 columns: [1,2] and [1,1] -> flatten to first row [1,2]; MISMATCH vs expected: value mismatch: expected 1, got '#NAME?'
-
UNIQUE LibreOffice Calc
=UNIQUE(A1:A5,FALSE,TRUE)- Actual result
- {#NAME?}
- Documented / expected
- {3}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: value mismatch: expected 3, got '#NAME?'
-
UNIQUE LibreOffice Calc
=UNIQUE(A1:A3)- Actual result
- {#NAME?, #NAME?, #NAME?}
- Documented / expected
- {5, 6, 7}
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: value mismatch: expected 5, got '#NAME?'
-
VLOOKUP LibreOffice Calc
=VLOOKUP("a",A1:B3,5,FALSE)- Actual result
- #VALUE!
- Documented / expected
- #REF!
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- Category
- Date and time
Microsoft docs: an invalid return_type raises #NUM!.; MISMATCH vs expected: expected '#NUM!', got '#VALUE!'
-
XLOOKUP LibreOffice Calc
=XLOOKUP("a",A1:A1,B1:B1)- Actual result
- #NAME?
- Documented / expected
- #N/A
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
A1 and B1 are blank; lookup value 'a' is not found; MISMATCH vs expected: expected '#N/A', got '#NAME?'
-
XLOOKUP LibreOffice Calc
=XLOOKUP("b",A1:A3,B1:B3)- Actual result
- #NAME?
- Documented / expected
- 2
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: expected 2, got '#NAME?'
-
XLOOKUP LibreOffice Calc
=XLOOKUP(2.5,A1:A4,B1:B4,"none",-1)- Actual result
- #NAME?
- Documented / expected
- 20
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
2.5 has no exact match; next-smaller is 2 -> 20; MISMATCH vs expected: expected 20, got '#NAME?'
-
XLOOKUP LibreOffice Calc
=XLOOKUP("z",A1:A3,B1:B3,"missing")- Actual result
- #NAME?
- Documented / expected
- missing
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: expected 'missing', got '#NAME?'
-
XLOOKUP LibreOffice Calc
=XLOOKUP("z",A1:A3,B1:B3)- Actual result
- #NAME?
- Documented / expected
- #N/A
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
MISMATCH vs expected: expected '#N/A', got '#NAME?'
-
XLOOKUP LibreOffice Calc
=XLOOKUP("x",A1:A4,B1:B4,"none",0,-1)- Actual result
- #NAME?
- Documented / expected
- 3
- Engine
- LibreOffice Calc 24.2.7.2
- Category
- Lookup and reference
Two rows match 'x' (1 and 3); reverse search returns the later one; MISMATCH vs expected: expected 3, got '#NAME?'
-
YEAR LibreOffice Calc
=YEAR(1)- Actual result
- 1899
- Documented / expected
- 1900
- Engine
- LibreOffice Calc 24.2.7.2
- 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 24.2.7.2
- Category
- Date and time
Microsoft docs: 'invalid basis values (< 0 or > 4) return a #NUM! error.'; MISMATCH vs expected: expected '#NUM!', got '#VALUE!'