Repository Public
1"""Documented cases where LibreOffice differs from Excel: the fixture stores Excel's result.23Format: {fixture: {formula: (excel_value, note)}}. Every entry is backed by Microsoft's function4documentation (quoted in the note); none comes from running Excel.5"""6NUM = "#NUM!"7VALUE = "#VALUE!"89EXCEL = {10 "lo_rounding": {11 "=MROUND(-10,3)": (NUM, "MROUND: number and multiple must have the same sign, else #NUM!"),12 "=MROUND(5,-2)": (NUM, "MROUND: number and multiple must have the same sign, else #NUM!"),13 },14 "lo_math": {15 "=POWER(-8,0.3333333333333333)": (NUM, "negative base with fractional exponent is #NUM! in Excel; LibreOffice returns -2"),16 "=POWER(0,0)": (NUM, "0^0 is #NUM! in Excel, 1 in LibreOffice"),17 "=COMBINA(3,5)": (21, "COMBINA(n,k) = COMBIN(n+k-1,k) has no n >= k constraint in Excel"),18 },19 "lo_text_math": {20 "=ROMAN(499,TRUE)": ("CDXCIX", "form TRUE is the classic form (same as 0)"),21 "=ROMAN(499,FALSE)": ("ID", "form FALSE is the simplified form (same as 4)"),22 '=ARABIC("-X")': (-10, "ARABIC accepts a leading minus sign"),23 '=ARABIC(" XII ")': (12, "ARABIC ignores leading and trailing spaces"),24 },25 "lo_distributions": {26 "=LOGNORM.DIST(0,1,1,TRUE)": (NUM, "LOGNORM.DIST: x <= 0 is #NUM!"),27 "=EXPON.DIST(-1,1,TRUE)": (NUM, "EXPON.DIST: x < 0 is #NUM!"),28 },29 "lo_engineering": {30 "=DEC2HEX(-12.9)": ("FFFFFFFFF4", "DEC2HEX truncates non-integers (LibreOffice rounds this one down)"),31 "=DEC2HEX(-12.9,3)": ("FFFFFFFFF4", "DEC2HEX truncates non-integers (LibreOffice rounds this one down)"),32 "=BITAND(1.5,1)": (NUM, "BITAND: non-integer arguments are #NUM!"),33 "=BITOR(1.5,1)": (NUM, "BITOR: non-integer arguments are #NUM!"),34 "=BITXOR(1.5,1)": (NUM, "BITXOR: non-integer arguments are #NUM!"),35 "=BITLSHIFT(1,54)": (NUM, "BITLSHIFT: |shift_amount| > 53 is #NUM!"),36 "=BITRSHIFT(1,54)": (NUM, "BITRSHIFT: |shift_amount| > 53 is #NUM!"),37 "=BITLSHIFT(5.5,1)": (NUM, "BITLSHIFT: a non-integer number is #NUM!"),38 "=BITRSHIFT(5.5,1)": (NUM, "BITRSHIFT: a non-integer number is #NUM!"),39 },40 "lo_information": {41 "=ISNUMBER(TRUE)": (False, "logical values are not numbers in Excel"),42 "=TYPE(TRUE)": (4, "TYPE of a logical value is 4"),43 "=ERROR.TYPE(SQRT(-1))": (6, "SQRT(-1) is #NUM! in Excel (LibreOffice raises its own Err:502)"),44 "=ISODD(D!C4)": (False, "an empty cell counts as 0"),45 "=FORMULATEXT(H!A11)": ("=SUBTOTAL(9,A1:A10)", "argument separator of the en-US formula syntax"),46 "=N(TRUE)": (1, "N(TRUE) is 1"),47 },48 "lo_stats": {49 "=GEOMEAN(D!C1:C12)": (NUM, "GEOMEAN: any data point <= 0 is #NUM!"),50 "=GEOMEAN(D!G1:I7)": (NUM, "GEOMEAN: any data point <= 0 is #NUM!"),51 "=MODE(4,4,1,1)": (4, "with several modes Excel returns the first one encountered; LibreOffice the smallest"),52 },53 "lo_conditional": {54 '=COUNTIF(D!C1:C12,"*")': (3, "the wildcard * only matches text cells in Excel; LibreOffice also matches numbers"),55 '=SUMIF(D!C1:C12,"*",D!A1:A12)': (24.5, "the wildcard * only matches text cells in Excel"),56 '=COUNTIF(D!C1:C12,"<>*")': (9, "<>* matches every cell that is not text"),57 '=COUNTIF(D!C1:C12,7)': (1, "a numeric criterion also matches numeric text in Excel"),58 '=SUMIF(D!C1:C12,7,D!A1:A12)': (8, "a numeric criterion also matches numeric text in Excel"),59 '=COUNTIF(D!C1:C12,"<>7")': (11, "numeric text 7 equals the criterion 7"),60 '=SUMIF(D!C1:C12,"<>7",D!A1:A12)': (70.25, "numeric text 7 equals the criterion 7"),61 '=COUNTIF(D!O1:O12,5)': (2, "a numeric criterion also matches numeric text in Excel"),62 '=COUNTIF(D!O1:O12,10)': (1, "a numeric criterion also matches numeric text in Excel"),63 '=COUNTIF(D!O1:O12,"?*")': (10, "wildcards only match text cells in Excel"),64 },65 "lo_complex": {66 '=IMSUB("2j","1-2i")': (VALUE, "mixing the i and j suffixes is #VALUE! in Excel"),67 '=IMDIV("2j","1-2i")': (VALUE, "mixing the i and j suffixes is #VALUE! in Excel"),68 '=IMSUB("2j","-i")': (VALUE, "mixing the i and j suffixes is #VALUE! in Excel"),69 '=IMDIV("2j","-i")': (VALUE, "mixing the i and j suffixes is #VALUE! in Excel"),70 '=IMSUM("1+i","1+j")': (VALUE, "mixing the i and j suffixes is #VALUE! in Excel"),71 },72 "lo_trig": {73 "=ATAN2(0,0)": ("#DIV/0!", "ATAN2(0,0) is #DIV/0! in Excel, 0 in LibreOffice"),74 },75 "lo_database": {76 '=DSTDEV(D!P1:T9,"Salary",D!AF1:AF2)': ("#DIV/0!", "no matching record: the sample statistics divide by zero in Excel; LibreOffice returns 0"),77 '=DSTDEV(D!P1:T9,3,D!AF1:AF2)': ("#DIV/0!", "no matching record: the sample statistics divide by zero in Excel; LibreOffice returns 0"),78 '=DSTDEV(D!P1:T9,"bonus",D!AF1:AF2)': ("#DIV/0!", "no matching record: the sample statistics divide by zero in Excel; LibreOffice returns 0"),79 '=DVAR(D!P1:T9,"Salary",D!AF1:AF2)': ("#DIV/0!", "no matching record: the sample statistics divide by zero in Excel; LibreOffice returns 0"),80 '=DVAR(D!P1:T9,3,D!AF1:AF2)': ("#DIV/0!", "no matching record: the sample statistics divide by zero in Excel; LibreOffice returns 0"),81 '=DVAR(D!P1:T9,"bonus",D!AF1:AF2)': ("#DIV/0!", "no matching record: the sample statistics divide by zero in Excel; LibreOffice returns 0"),8283 '=DGET(D!P1:T9,"bonus",D!W1:X2)': (NUM, "DGET: more than one matching record is #NUM! (LibreOffice ignores the empty field)"),84 },85}86