SPB Git

Repository Public

overrides.py 5.5 KB 86 lines python

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
Files main
Menu

Theme