Repository Public
1"""Generates the LibreOffice-recalculated reference fixtures (see common.py)."""2import itertools34from common import libreoffice_fixture5from data import data_sheet, hidden_sheet67D = data_sheet()8H = hidden_sheet()9TIGHT = {"tol": 1e-13}101112def fmt(v):13 if isinstance(v, str):14 return '"' + v + '"'15 if isinstance(v, bool):16 return "TRUE" if v else "FALSE"17 return repr(v)181920def calls(name, arglists, **opts):21 return [(f"={name}({','.join(fmt(a) for a in args)})", opts) for args in arglists]222324def rounding():25 c = []26 nums = [2.5, -2.5, 1.005, 0.285, 2.675, 1.45, -1.45, 123.456, -123.456, 0.5, 1.5, 3.14159, 1e-7, 5.5e10, 0.1 + 0.2, 8.345, 1234567.891, 0.000123456, 99.995, -0.5]27 for n in nums:28 for d in [0, 1, 2, -1, 3]:29 for fn in ["ROUND", "ROUNDUP", "ROUNDDOWN", "TRUNC"]:30 c.append((f"={fn}({n!r},{d})", TIGHT))31 for n in [2.5, -2.5, 7.9, -7.9, 0, 1e10 + 0.5, -0.1, 3]:32 c += [(f"=INT({n!r})", TIGHT), (f"=TRUNC({n!r})", TIGHT), (f"=EVEN({n!r})", TIGHT), (f"=ODD({n!r})", TIGHT)]33 pairs = [(10, 3), (-10, 3), (1.3, 0.2), (6.05, 0.1), (5, -2), (-5, -2), (7.5, 5), (0, 5), (5, 0), (2.5, 1), (17, 2.5), (-17, -2.5), (1.1, 0.1), (0.7, 0.1), (123.456, 0.01)]34 for x, s in pairs:35 for fn in ["MROUND", "CEILING", "FLOOR", "CEILING.MATH", "FLOOR.MATH", "CEILING.PRECISE", "FLOOR.PRECISE", "ISO.CEILING"]:36 c.append((f"={fn}({x!r},{s!r})", {"tol": 1e-12}))37 for x in [-5.5, 5.5, -4.3, 4.3, -6, 6]:38 for fn in ["CEILING.MATH", "FLOOR.MATH"]:39 c += [(f"={fn}({x!r})", TIGHT), (f"={fn}({x!r},2,1)", TIGHT), (f"={fn}({x!r},2,0)", TIGHT), (f"={fn}({x!r},-2)", TIGHT)]40 for fn in ["CEILING.PRECISE", "FLOOR.PRECISE", "ISO.CEILING"]:41 c += [(f"={fn}({x!r})", TIGHT), (f"={fn}({x!r},-3)", TIGHT)]42 for x, s in [(-2.5, 2), (2.5, -2), (-2.5, -2), (2.5, 2), (-7, 3), (7, -3)]:43 c += [(f"=CEILING({x!r},{s!r})", TIGHT), (f"=FLOOR({x!r},{s!r})", TIGHT)]44 return c454647def math():48 c = []49 for n, d in [(7, 3), (-7, 3), (7, -3), (-7, -3), (5.5, 2), (-5.5, 2), (10, 2.5), (1e15, 7), (0, 3), (3, 0), (0.75, 0.25), (123456789.75, 0.25)]:50 c += [(f"=MOD({n!r},{d!r})", {"tol": 1e-12}), (f"=QUOTIENT({n!r},{d!r})", TIGHT)]51 c += calls("POWER", [(2, 10), (2, -2), (-8, 1 / 3), (0, 0), (0, -1), (9, 0.5), (-2, 3), (1.5, 2.5), (10, 300), (10, 400)], tol=1e-13)52 for fn in ["SQRT", "SQRTPI", "EXP", "LN", "LOG10", "SIGN", "FACT", "FACTDOUBLE"]:53 c += calls(fn, [(0,), (1,), (2.5,), (10,), (-1,), (25,), (0.001,), (170,), (171,), (7,), (8,)], tol=1e-13)54 c += calls("LOG", [(8, 2), (100,), (100, 10), (2, 8), (5, 1), (5, 0), (0, 5), (2.5, 2.5), (1000, 0.1), (86, 2.7182818)], tol=1e-13)55 c.append("=PI()")56 for fn in ["COMBIN", "COMBINA", "PERMUT", "PERMUTATIONA"]:57 c += calls(fn, [(10, 3), (5, 5), (5, 0), (52, 5), (100, 50), (6.9, 2.9), (3, 5), (-1, 2), (10, -1), (1, 1), (30, 15), (170, 85)], tol=1e-12)58 c += ["=GCD(12,18)", "=GCD(48,36,60)", "=GCD(7,13)", "=GCD(0,5)", "=GCD(0,0)", "=GCD(12.9,18.2)", "=GCD(-4,8)", "=GCD(D!E1:E12)",59 "=LCM(4,6)", "=LCM(3,5,7)", "=LCM(0,5)", "=LCM(12.9,18.2)", "=LCM(-4,8)", "=LCM(D!E1:E6)", "=LCM(1,1)",60 "=MULTINOMIAL(2,3,4)", "=MULTINOMIAL(1,1,1)", "=MULTINOMIAL(5)", "=MULTINOMIAL(D!E1:E5)", "=MULTINOMIAL(3,-1)", "=MULTINOMIAL(10,10,10)",61 "=PRODUCT(D!A1:A12)", "=PRODUCT(2,3,4)", "=PRODUCT(D!C1:C12)", "=PRODUCT(D!F1:F12,2)",62 "=SUMSQ(D!A1:A12)", "=SUMSQ(3,4)", "=SUMSQ(D!C1:C12)", "=SUMSQ(D!A1:A12,D!B1:B12)",63 "=SUMPRODUCT(D!A1:A12,D!B1:B12)", "=SUMPRODUCT(D!A1:A12)", "=SUMPRODUCT(D!A1:A12,D!B1:B12,D!F1:F12)", "=SUMPRODUCT(D!A1:A12,D!C1:C12)",64 "=SUMPRODUCT((D!A1:A12>5)*D!B1:B12)", "=SUMPRODUCT((D!D1:D12=\"apple\")*(D!A1:A12))", "=SUMPRODUCT(D!A1:A12,D!B1:B11)",65 "=SUMPRODUCT({1,2,3},{4,5,6})", "=SUMPRODUCT(D!G1:I3,D!G5:I7)", "=SUMPRODUCT(--(D!E1:E12>3))",66 "=SUMX2MY2(D!A1:A12,D!B1:B12)", "=SUMX2PY2(D!A1:A12,D!B1:B12)", "=SUMXMY2(D!A1:A12,D!B1:B12)",67 "=SUMX2MY2({1,2,3},{4,5,6})", "=SUMX2PY2({1,2,3},{4,5,6})", "=SUMXMY2({1,2,3},{4,5,6})", "=SUMX2MY2(D!A1:A5,D!B1:B6)",68 "=SERIESSUM(2,0,1,{1,2,3})", "=SERIESSUM(0.5,1,2,{1,-0.5,0.25,-0.125})", "=SERIESSUM(3,2,-1,D!N1:N5)", "=SERIESSUM(0.785398163,0,2,{1,-0.5,0.041666667,-0.001388889})",69 ]70 return [(x, {"tol": 1e-12}) if isinstance(x, str) else x for x in c]717273def trig():74 c = []75 one = ["SIN", "COS", "TAN", "ASIN", "ACOS", "ATAN", "SINH", "COSH", "TANH", "ASINH", "ACOSH", "ATANH", "SEC", "CSC", "COT", "ACOT",76 "SECH", "CSCH", "COTH", "ACOTH", "DEGREES", "RADIANS"]77 for fn in one:78 c += calls(fn, [(0,), (0.5,), (1,), (-0.5,), (2,), (-2,), (10,), (0.999,), (1.5,), (100,), (1e-5,)], tol=1e-12, abs=1e-15)79 c += calls("ATAN2", [(1, 1), (-1, 1), (1, -1), (-1, -1), (0, 1), (1, 0), (0, 0), (-1, 0), (0, -1), (3.5, -0.2)], tol=1e-13)80 return c818283def text_math():84 c = []85 for n in [1, 4, 9, 14, 40, 90, 400, 499, 999, 1990, 2024, 3999, 0, 45, 49, 99, 495, 1499, 3888, 4000, -1]:86 c.append(f"=ROMAN({n})")87 for form in [1, 2, 3, 4]:88 c.append(f"=ROMAN({n},{form})")89 c += ["=ROMAN(499,TRUE)", "=ROMAN(499,FALSE)", "=ROMAN(12.9)"]90 for s in ["MCMXC", "mmxxiv", "IV", "IIII", "XCIX", "IC", "", "-X", "ABC", "MMMCMXCIX", "VL", "LDVLIV", " XII "]:91 c.append(f'=ARABIC("{s}")')92 c += ["=BASE(255,16)", "=BASE(255,2)", "=BASE(255,2,12)", "=BASE(0,2)", "=BASE(35,36)", "=BASE(1295,36)", "=BASE(7,1)", "=BASE(7,37)", "=BASE(-1,2)",93 "=BASE(12.9,2)", "=BASE(100,10,5)", "=BASE(4503599627370496,2)",94 '=DECIMAL("FF",16)', '=DECIMAL("zz",36)', '=DECIMAL("111",2)', '=DECIMAL("12",2)', '=DECIMAL("0",2)', '=DECIMAL("7fffffff",16)', '=DECIMAL("ZAP",36)']95 return c969798def matrix():99 c = []100 for r, k in itertools.product(range(1, 4), range(1, 4)):101 c.append((f"=INDEX(MMULT(D!G1:I3,D!G5:I7),{r},{k})", {"tol": 1e-12}))102 c.append((f"=INDEX(MINVERSE(D!G1:I3),{r},{k})", {"tol": 1e-11, "abs": 1e-14}))103 c.append((f"=INDEX(MINVERSE(D!G5:I7),{r},{k})", {"tol": 1e-10, "abs": 1e-13}))104 c.append(f"=INDEX(MUNIT(3),{r},{k})")105 for r, k in itertools.product(range(1, 4), range(1, 3)):106 c.append((f"=INDEX(MMULT(D!G1:I3,D!G9:H11),{r},{k})", {"tol": 1e-12}))107 c += [("=MDETERM(D!G1:I3)", {"tol": 1e-12}), ("=MDETERM(D!G5:I7)", {"tol": 1e-11}), "=MDETERM({1,2;3,4})", "=MDETERM({5})",108 ("=MDETERM({1,2,3;4,5,6;7,8,9})", {"abs": 1e-12}), "=MDETERM(D!G9:H11)", "=MDETERM({4,3,2,1;1,5,2,2;0,1,6,1;3,0,1,7})",109 "=SUM(MMULT(D!G1:I3,MINVERSE(D!G1:I3)))", "=MMULT({1,2},{3;4})", "=SUM(MMULT({1;2},{3,4}))", "=SUM(MUNIT(5))", "=MUNIT(1)",110 "=INDEX(MMULT(D!G1:I3,D!G9:H10),1,1)", "=INDEX(MINVERSE(D!G9:H11),1,1)", "=INDEX(MINVERSE({1,2;2,4}),1,1)",111 ("=INDEX(MINVERSE({4,7;2,6}),2,1)", {"tol": 1e-12}), ("=SUMPRODUCT(MINVERSE({2,0;0,4}))", {"tol": 1e-12})]112 return c113114115def subtotal():116 c = []117 for fn in list(range(1, 12)) + list(range(101, 112)):118 c.append((f"=SUBTOTAL({fn},H!A1:A10)", {"tol": 1e-12}))119 c.append((f"=SUBTOTAL({fn},H!B1:B10)", {"tol": 1e-12}))120 c += [("=SUBTOTAL(9,H!A1:A11)", {}), ("=SUBTOTAL(109,H!A1:A11)", {}), ("=SUBTOTAL(9,H!A1:A5,H!A8:A10)", {}), ("=SUBTOTAL(1,H!A1:B10)", {"tol": 1e-12}),121 ("=SUBTOTAL(12,H!A1:A10)", {}), ("=SUBTOTAL(0,H!A1:A10)", {}), ("=SUBTOTAL(9,H!C1:C3)", {})]122 for fn in range(1, 14):123 for opt in [0, 1, 2, 3, 4, 5, 6, 7]:124 c.append((f"=AGGREGATE({fn},{opt},H!A1:A10)", {"tol": 1e-12}))125 for fn in range(1, 14):126 c.append((f"=AGGREGATE({fn},6,H!C1:C3)", {"tol": 1e-12}))127 c.append((f"=AGGREGATE({fn},4,H!C1:C3)", {"tol": 1e-12}))128 c.append((f"=AGGREGATE({fn},3,D!E1:E12)", {"tol": 1e-12}))129 for fn, k in [(14, 2), (15, 2), (16, 0.3), (17, 1), (18, 0.3), (19, 1), (14, 1), (15, 10), (16, 1), (17, 3), (18, 0.9), (19, 3)]:130 c.append((f"=AGGREGATE({fn},4,H!A1:A10,{k})", {"tol": 1e-12}))131 c.append((f"=AGGREGATE({fn},5,H!A1:A10,{k})", {"tol": 1e-12}))132 c.append((f"=AGGREGATE({fn},6,H!C1:C3,{k})", {"tol": 1e-12}))133 c += ["=AGGREGATE(9,4,H!A1:A11)", "=AGGREGATE(9,0,H!A1:A11)", "=AGGREGATE(20,4,H!A1:A10)", "=AGGREGATE(9,8,H!A1:A10)"]134 return c135136137def conditional():138 crits = ['">5"', '">=7.5"', '"<4"', '"<=4"', '"=4"', '"<>4"', "4", '"4"', '">"&D!A5', "D!A5", '"<0"', '">100"', '"=1E1"', '">=1e1"', '"10"']139 c = []140 for cr in crits:141 c += [f"=COUNTIF(D!A1:A12,{cr})", (f"=SUMIF(D!A1:A12,{cr})", {"tol": 1e-12}), (f"=SUMIF(D!A1:A12,{cr},D!B1:B12)", {"tol": 1e-12}),142 (f"=AVERAGEIF(D!A1:A12,{cr})", {"tol": 1e-12}), (f"=AVERAGEIF(D!A1:A12,{cr},D!B1:B12)", {"tol": 1e-12})]143 texts = ['"apple"', '"APPLE"', '"a*"', '"*an*"', '"?????"', '"<>apple"', '"*"', '"apple*"', '"<>*e"', '">banana"', '"<=banana"', '"b?nana"', '"=cherry"',144 '"*e"', '"<>"', '"="', '""', '"fig"', '"zzz"', '"<>*"', '"??"', '"a*e"', '"*p*p*"', '"<b"', '">=c"']145 for cr in texts:146 c += [f"=COUNTIF(D!D1:D12,{cr})", (f"=SUMIF(D!D1:D12,{cr},D!A1:A12)", {"tol": 1e-12}), (f"=AVERAGEIF(D!D1:D12,{cr},D!A1:A12)", {"tol": 1e-12})]147 mixed = ['">0"', '"<>5"', "5", '"5"', '"text"', '"<>"', '"="', '""', '"*"', '"7"', "7", '">=3.5"', '"<>text"', '"abc"', '"a*"', '"<0"', '"<>7"']148 for cr in mixed:149 c += [f"=COUNTIF(D!C1:C12,{cr})", (f"=SUMIF(D!C1:C12,{cr},D!A1:A12)", {"tol": 1e-12})]150 wild = ['"a~*b"', '"a~?b"', '"a?b"', '"a*b"', '"a~~b"', '"x~*"', '"*~**"', '"5"', "5", '"50%"', '"<>"', '""', '"="', '">=5"', '"10"', "10", '"*b"', '"A*"', '"~a*"', '"?*"']151 for cr in wild:152 c.append(f"=COUNTIF(D!O1:O12,{cr})")153 c += ["=COUNTIF(D!AH1:AH6,\">\"&45352)", "=COUNTIF(D!AH1:AH6,\">=\"&45352)", "=SUMIF(D!AH1:AH6,\"<\"&45397,D!AI1:AI6)",154 "=COUNTIF(D!AH1:AH6,45352)", "=SUMIF(D!AH1:AH6,\">45300\",D!AI1:AI6)", "=AVERAGEIF(D!AH1:AH6,\">=45383\",D!AI1:AI6)",155 "=COUNTIF(D!A:A,\">5\")", "=SUMIF(D!A:A,\">5\",D!B:B)", "=AVERAGEIF(D!A:A,\"<5\",D!B:B)", "=SUMIF(D!A1:A12,\">5\",D!B1)", "=SUMIF(D!A1:A12,\">5\",D!B1:B3)",156 "=COUNTIF(D!A1:B12,\">5\")", "=SUMIF(D!A1:B12,\">5\")", "=COUNTIF(D!G1:I7,\">2\")", "=COUNTIF(D!G1:I7,\"\")", "=COUNTIF(D!G1:I7,\"<>\")",157 "=AVERAGEIF(D!A1:A12,\">100\")", "=AVERAGEIF(D!D1:D12,\"zzz\",D!A1:A12)",158 "=COUNTBLANK(D!C1:C12)", "=COUNTBLANK(D!A1:A12)", "=COUNTBLANK(D!O1:O12)", "=COUNTBLANK(D!G1:I8)", "=COUNTBLANK(D!T1:T9)", "=COUNTBLANK(D!A1:C20)"]159 ifs = [160 ('D!A1:A12,">5"', ), ('D!A1:A12,">5",D!B1:B12,"<8"',), ('D!D1:D12,"apple",D!A1:A12,">3"',), ('D!D1:D12,"*an*",D!E1:E12,">=2"',),161 ('D!A1:A12,">5",D!B1:B12,"<8",D!E1:E12,"<>4"',), ('D!C1:C12,"",D!A1:A12,">5"',), ('D!C1:C12,"<>",D!D1:D12,"<>apple"',),162 ('D!C1:C12,"",D!D1:D12,"<>apple"',), ('D!A1:A12,">100"',), ('D!D1:D12,"a*",D!D1:D12,"*e"',), ('D!E1:E12,3',), ('D!E1:E12,">=3",D!E1:E12,"<=5"',),163 ('D!C1:C12,"<>5",D!D1:D12,"<>zzz"',), ('D!A:A,">5",D!B:B,"<8"',),164 ]165 for (args,) in ifs:166 c += [f"=COUNTIFS({args})", (f"=SUMIFS(D!F1:F12,{args})".replace("F1:F12,D!A:A", "F:F,D!A:A"), {"tol": 1e-12}),167 (f"=AVERAGEIFS(D!F1:F12,{args})".replace("F1:F12,D!A:A", "F:F,D!A:A"), {"tol": 1e-12}),168 (f"=MAXIFS(D!F1:F12,{args})".replace("F1:F12,D!A:A", "F:F,D!A:A"), {"tol": 1e-12}),169 (f"=MINIFS(D!F1:F12,{args})".replace("F1:F12,D!A:A", "F:F,D!A:A"), {"tol": 1e-12})]170 c += ["=COUNTIFS(D!A1:A12,\">5\",D!B1:B11,\"<8\")", "=SUMIFS(D!F1:F11,D!A1:A12,\">5\")", "=SUMIFS(D!C1:C12,D!A1:A12,\">0\")", "=MAXIFS(D!C1:C12,D!A1:A12,\">0\")",171 "=MINIFS(D!C1:C12,D!A1:A12,\">0\")", "=AVERAGEIFS(D!C1:C12,D!A1:A12,\">0\")"]172 return c173174175def stats():176 c = []177 ranges = ["D!A1:A12", "D!B1:B12", "D!E1:E12", "D!F1:F12", "D!C1:C12", "D!A1:B12", "D!G1:I7", "D!L1:L12"]178 fns = ["MEDIAN", "MODE", "MODE.SNGL", "STDEV", "STDEV.S", "STDEVP", "STDEV.P", "STDEVA", "STDEVPA", "VAR", "VAR.S", "VARP", "VAR.P", "VARA", "VARPA",179 "AVERAGEA", "MAXA", "MINA", "AVEDEV", "DEVSQ", "GEOMEAN", "HARMEAN", "SKEW", "SKEW.P", "KURT"]180 for fn in fns:181 for rg in ranges:182 c.append((f"={fn}({rg})", {"tol": 1e-12}))183 c += [(f"={fn}(1,2,4,8,16)", {"tol": 1e-12}), (f"={fn}(5)", {}), (f"={fn}(3,3)", {}), (f"={fn}(D!A1:A6,D!B1:B6,10)", {"tol": 1e-12}),184 (f"={fn}(D!D1:D12)", {})]185 c += ["=INDEX(MODE.MULT(D!E1:E12),1)", "=ROWS(MODE.MULT(D!E1:E12))", "=INDEX(MODE.MULT({1,2,2,3,3,4}),1)", "=INDEX(MODE.MULT({1,2,2,3,3,4}),2)",186 "=SUM(MODE.MULT({5,1,5,1,7,7}))", "=INDEX(MODE.MULT({5,1,5,1,7,7}),1)", "=MODE.MULT({1,2,3})", "=MODE(1,2,3)", "=MODE(4,4,1,1)", "=MODE({1,1,4,4})"]187 for p in [0, 0.1, 0.2, 0.25, 0.4, 0.5, 0.9, 0.999, 1, -0.1]:188 c.append((f"=TRIMMEAN(D!A1:A12,{p})", {"tol": 1e-12}))189 c.append((f"=TRIMMEAN(D!E1:E11,{p})", {"tol": 1e-12}))190 for k in [1, 2, 5, 12, 13, 0]:191 c += [f"=LARGE(D!A1:A12,{k})", f"=SMALL(D!A1:A12,{k})", f"=LARGE(D!C1:C12,{k})", f"=SMALL(D!E1:E12,{k})"]192 for k in [0, 0.1, 0.25, 0.3, 0.5, 0.75, 0.9, 0.95, 1, 1.1, -0.1, 0.05, 0.99, 1 / 3]:193 for fn in ["PERCENTILE", "PERCENTILE.INC", "PERCENTILE.EXC"]:194 c += [(f"={fn}(D!A1:A12,{k!r})", {"tol": 1e-12}), (f"={fn}(D!E1:E12,{k!r})", {"tol": 1e-12}), (f"={fn}(D!F1:F5,{k!r})", {"tol": 1e-12})]195 for q in [0, 1, 2, 3, 4, 5, -1, 1.7]:196 for fn in ["QUARTILE", "QUARTILE.INC", "QUARTILE.EXC"]:197 c += [(f"={fn}(D!A1:A12,{q})", {"tol": 1e-12}), (f"={fn}(D!E1:E12,{q})", {"tol": 1e-12}), (f"={fn}(D!F1:F7,{q})", {"tol": 1e-12})]198 for x in [3, 9, 5, 1, 100, 2, 7]:199 for fn in ["RANK", "RANK.EQ", "RANK.AVG"]:200 c += [f"={fn}({x},D!E1:E12)", f"={fn}({x},D!E1:E12,1)", f"={fn}({x},D!E1:E12,0)", f"={fn}({x},D!A1:A12,1)"]201 for x in [1, 2, 3, 3.3, 4.5, 9, 9.5, 0.5, 5, 6, 8.99]:202 for fn in ["PERCENTRANK", "PERCENTRANK.INC", "PERCENTRANK.EXC"]:203 # high significance only: LibreOffice rounds to significant digits where Excel truncates decimals204 c += [(f"={fn}(D!E1:E12,{x},14)", {"tol": 1e-11}), (f"={fn}(D!A1:A12,{x},14)", {"tol": 1e-11}), (f"={fn}(D!F1:F12,{x},14)", {"tol": 1e-11})]205 for z in [(42, 40, 1.5), (1, 2, 0), (1, 2, -1), (-3.5, 0, 2)]:206 c.append((f"=STANDARDIZE({z[0]},{z[1]},{z[2]})", {"tol": 1e-13}))207 for x in [0, 0.5, -0.75, 0.999, 1, -1, 2]:208 c += [(f"=FISHER({x})", {"tol": 1e-13}), (f"=FISHERINV({x})", {"tol": 1e-13})]209 for i in range(1, 6):210 c += [f"=INDEX(FREQUENCY(D!A1:A12,{{2;5;8;11}}),{i})", f"=INDEX(FREQUENCY(D!E1:E12,D!N1:N4),{i})", f"=INDEX(FREQUENCY(D!A1:B12,{{0;10;5;20}}),{i})"]211 c += ["=SUM(FREQUENCY(D!C1:C12,{0;5;10}))", "=ROWS(FREQUENCY(D!A1:A12,{2;5;8;11}))",212 ("=PROB(D!N1:N5,D!M1:M5,2,4)", {"tol": 1e-12}), ("=PROB(D!N1:N5,D!M1:M5,3)", {"tol": 1e-12}), ("=PROB(D!N1:N5,D!M1:M5,0,10)", {"tol": 1e-12}),213 ("=PROB(D!N1:N5,D!M1:M5,6,7)", {}), ("=PROB(D!N1:N5,D!M1:M4,2,4)", {}), ("=PROB(D!N1:N5,D!A1:A5,2,4)", {}), ("=PROB({1,2,3},{0.2,0.3,0.5},2,3)", {"tol": 1e-12})]214 return c215216217def paired():218 c = []219 fns = ["CORREL", "PEARSON", "RSQ", "COVAR", "COVARIANCE.P", "COVARIANCE.S", "SLOPE", "INTERCEPT", "STEYX"]220 for fn in fns:221 c += [(f"={fn}(D!B1:B12,D!A1:A12)", {"tol": 1e-11}), (f"={fn}(D!A1:A12,D!B1:B12)", {"tol": 1e-11}), (f"={fn}(D!F1:F12,D!K1:K12)", {"tol": 1e-11}),222 (f"={fn}(D!C1:C12,D!A1:A12)", {"tol": 1e-11}), (f"={fn}(D!A1:A5,D!B1:B6)", {}), (f"={fn}({{1,2,3,4}},{{2,4,6,8.5}})", {"tol": 1e-11}),223 (f"={fn}({{1,2}},{{3,5}})", {"tol": 1e-11}), (f"={fn}({{1}},{{3}})", {}), (f"={fn}({{1,2,3}},{{5,5,5}})", {}), (f"={fn}(D!L1:L12,D!F1:F12)", {"tol": 1e-11})]224 for x in [0, 5, 20, -3.5]:225 for fn in ["FORECAST", "FORECAST.LINEAR"]:226 c += [(f"={fn}({x},D!B1:B12,D!A1:A12)", {"tol": 1e-11}), (f"={fn}({x},D!F1:F12,D!K1:K12)", {"tol": 1e-11}), (f"={fn}({x},D!C1:C12,D!A1:A12)", {"tol": 1e-11})]227 c += ["=FORECAST(1,{1,2,3},{5,5,5})", "=FORECAST(1,D!A1:A5,D!B1:B6)"]228 return c229230231def regression():232 c = []233 t = {"tol": 1e-9, "abs": 1e-11}234 specs = [("D!B1:B12", "D!A1:A12", 2), ("D!B1:B12", "D!A1:A12,FALSE", 2), ("D!B1:B12", "", 2), ("D!F1:F12", "D!K1:K12", 2)]235 for y, x, cols in specs:236 sep = "," if x else ""237 for r in range(1, 6):238 for k in range(1, cols + 1):239 fx = x if x else ""240 if x.endswith(",FALSE"):241 c.append((f"=INDEX(LINEST({y},{fx},TRUE),{r},{k})", t))242 elif x:243 c.append((f"=INDEX(LINEST({y},{fx},TRUE,TRUE),{r},{k})", t))244 else:245 c.append((f"=INDEX(LINEST({y},,TRUE,TRUE),{r},{k})", t))246 for k in range(1, cols + 1):247 c.append((f"=INDEX(LINEST({y}{sep}{x}),1,{k})", t))248 # two regressors (columns A and K), with and without constant249 for const in ["TRUE", "FALSE"]:250 for r in range(1, 6):251 for k in range(1, 4):252 c.append((f"=INDEX(LINEST(D!B1:B12,D!J20:K31,{const},TRUE),{r},{k})", t))253 c.append((f"=INDEX(LOGEST(D!L1:L12,D!J20:K31,{const},TRUE),{r},{k})", t))254 for r in range(1, 6):255 for k in range(1, 3):256 c.append((f"=INDEX(LOGEST(D!L1:L12,D!F1:F12,TRUE,TRUE),{r},{k})", t))257 c.append((f"=INDEX(LOGEST(D!L1:L12,D!F1:F12,FALSE,TRUE),{r},{k})", t))258 c += [("=INDEX(LOGEST(D!L1:L12),1,1)", t), ("=INDEX(LOGEST(D!L1:L12),1,2)", t), ("=INDEX(LINEST({1,2,4,8},{1,2,3,4}),1,1)", t),259 ("=INDEX(LINEST({1,2,4,8},{1,2,3,4}),1,2)", t), ("=INDEX(LINEST({1,2,4,8},{1,2,3,4;2,1,5,3},TRUE,TRUE),1,1)", t),260 ("=INDEX(LINEST({1,2,4,8},{1,2,3,4;2,1,5,3},TRUE,TRUE),1,3)", t), ("=INDEX(LINEST({1,2,4,8},{1,2,3,4;2,1,5,3},TRUE,TRUE),3,1)", t)]261 for i in range(1, 13, 3):262 c += [(f"=INDEX(TREND(D!B1:B12,D!A1:A12),{i})", t), (f"=INDEX(TREND(D!B1:B12),{i})", t), (f"=INDEX(GROWTH(D!L1:L12,D!F1:F12),{i})", t),263 (f"=INDEX(GROWTH(D!L1:L12),{i})", t), (f"=INDEX(TREND(D!B1:B12,D!J20:K31),{i})", t), (f"=INDEX(TREND(D!B1:B12,D!A1:A12,,FALSE),{i})", t)]264 for i in range(1, 4):265 c += [(f"=INDEX(TREND(D!B1:B12,D!A1:A12,{{20;25;30}}),{i})", t), (f"=INDEX(GROWTH(D!L1:L12,D!F1:F12,{{12;13;14}}),{i})", t),266 (f"=INDEX(TREND(D!B1:B12,D!J20:K31,{{1,2;3,4;5,6}}),{i})", t), (f"=INDEX(GROWTH(D!L1:L12,D!F1:F12,{{12;13;14}},FALSE),{i})", t),267 (f"=INDEX(TREND({{1,2,4,8}},{{1,2,3,4}},{{5,6,7}}),1,{i})", t)]268 return c269270271def information():272 c = []273 vals = ["D!C1", "D!C2", "D!C3", "D!C4", "D!C6", "1/0", "NA()", '"abc"', "12", "TRUE", '""', "D!ZZ99"]274 for fn in ["ISBLANK", "ISERROR", "ISERR", "ISNA", "ISNUMBER", "ISTEXT", "ISNONTEXT", "ISLOGICAL", "N", "TYPE"]:275 for v in vals:276 c.append(f"={fn}({v})")277 for v in ["1/0", "NA()", '1+"x"', "SQRT(-1)", "5", '"text"', "D!ZZ99", "UNKNOWNFN()", "INDIRECT(\"x y\")"]:278 c.append((f"=ERROR.TYPE({v})", {}))279 for v in [0, 1, 2, -1, -2, 2.5, 3.9, -3.9, 1e10]:280 c += [f"=ISEVEN({v})", f"=ISODD({v})"]281 c += ["=TYPE({1,2,3})", "=ISEVEN(\"x\")", "=ISODD(D!C4)", "=ISREF(D!A1)", "=ISREF(5)", "=ISREF(D!A1:B2)", "=ISREF(\"D!A1\")", "=ISFORMULA(H!A11)", "=ISFORMULA(H!A1)",282 "=FORMULATEXT(H!A11)", "=FORMULATEXT(H!A1)", "=SHEET(H!A1)", "=SHEET(D!A1)", "=SHEETS()", "=SHEET()", "=N(D!A1)", "=N(\"7\")",283 "=CELL(\"row\",D!C7)", "=CELL(\"col\",D!C7)", "=CELL(\"contents\",D!A2)", "=CELL(\"type\",D!A2)", "=CELL(\"type\",D!C2)", "=CELL(\"type\",D!C4)", "=CELL(\"contents\",D!D1)"]284 return c285286287def engineering():288 c = []289 bins = ["1100100", "1111111111", "1000000000", "0", "111", "0000000101", "11111111111", "102", "1111111110", "10"]290 octs = ["144", "7777777777", "4000000000", "0", "17", "3777777777", "7777777000", "8", "77777777777", "7777777400"]291 hexs = ["FF", "FFFFFFFFFF", "8000000000", "0", "1f", "7FFFFFFFFF", "FFFFFFFE00", "G1", "FFFFFFFFFFF", "A5A5", "1FF", "200", "FFFFFFFC00"]292 for b in bins:293 c += [f'=BIN2DEC("{b}")', f'=BIN2HEX("{b}")', f'=BIN2OCT("{b}")', f'=BIN2HEX("{b}",8)', f'=BIN2OCT("{b}",2)']294 for o in octs:295 c += [f'=OCT2DEC("{o}")', f'=OCT2HEX("{o}")', f'=OCT2BIN("{o}")', f'=OCT2HEX("{o}",6)', f'=OCT2BIN("{o}",10)']296 for h in hexs:297 c += [f'=HEX2DEC("{h}")', f'=HEX2OCT("{h}")', f'=HEX2BIN("{h}")', f'=HEX2OCT("{h}",10)', f'=HEX2BIN("{h}",9)']298 decs = [0, 1, 100, -1, -100, 511, 512, -512, -513, 255, 536870911, 536870912, -536870912, -536870913, 549755813887, 549755813888, -549755813888, -549755813889, 12.9, -12.9]299 for d in decs:300 c += [f"=DEC2BIN({d})", f"=DEC2OCT({d})", f"=DEC2HEX({d})", f"=DEC2BIN({d},10)", f"=DEC2OCT({d},4)", f"=DEC2HEX({d},3)"]301 c += ["=BIN2DEC(1100100)", "=HEX2DEC(100)", "=OCT2DEC(777)", "=DEC2BIN(5,0)", "=DEC2HEX(255,1)", "=DEC2BIN(5,3.9)", "=DEC2BIN(5,-1)", '=DEC2BIN("x")', "=DEC2HEX(255,11)"]302 for a, b in [(5, 3), (13, 25), (0, 0), (255, 256), (281474976710655, 1), (281474976710656, 1), (-1, 1), (1.5, 1), (1023, 1024), (123456789, 987654321)]:303 c += [f"=BITAND({a},{b})", f"=BITOR({a},{b})", f"=BITXOR({a},{b})"]304 for a, s in [(4, 2), (1, 47), (13, -2), (1024, 3), (1, 54), (255, 0), (7, 1), (140737488355327, 1), (5.5, 1)]:305 c += [f"=BITLSHIFT({a},{s})", f"=BITRSHIFT({a},{s})"]306 c += ["=DELTA(5,5)", "=DELTA(5,4)", "=DELTA(0)", "=DELTA(1)", "=DELTA(0.5,0.5)", '=DELTA("a",1)', "=GESTEP(5,4)", "=GESTEP(5,5)", "=GESTEP(-1)", "=GESTEP(0)", "=GESTEP(3.9,4)"]307 for x in [0, 0.5, 1, -1, 2.5, 5, -0.1]:308 c += [(f"=ERF({x})", {"tol": 1e-13}), (f"=ERF.PRECISE({x})", {"tol": 1e-13}), (f"=ERFC({x})", {"tol": 1e-13}), (f"=ERFC.PRECISE({x})", {"tol": 1e-13}), (f"=ERF({x},1.5)", {"tol": 1e-12, "abs": 1e-15})]309 return c310311312def convert():313 """LibreOffice still carries the rounded conversion factors of Excel 2003 for many units (lbm, HP,314 BTU, eV, ly, parsec, atm…) and maps some names differently (flb is a foot-poundal there). Only the315 groups whose LibreOffice factors are the exact definitions are used here; the complete table is316 checked against scipy.constants in the scipy fixture."""317 groups = {318 "dist": ["m", "mi", "Nmi", "in", "ft", "yd", "ang", "Pica", "km", "cm", "um", "survey_mi"],319 "time": ["yr", "day", "d", "hr", "mn", "min", "sec", "s", "ms", "ks"],320 "temp": ["C", "cel", "F", "fah", "K", "kel", "Rank", "Reau"],321 "vol": ["tsp", "tspm", "tbs", "oz", "cup", "pt", "us_pt", "uk_pt", "qt", "uk_qt", "gal", "uk_gal", "l", "L", "lt", "ang3", "barrel", "ft3", "in3",322 "m3", "mi3", "yd3", "Nmi3", "Pica3", "ml", "cm3", "km3"],323 "area": ["uk_acre", "us_acre", "ang2", "ar", "ft2", "ha", "in2", "m2", "Morgen", "mi2", "Nmi2", "Pica2", "yd2", "km2", "cm2"],324 "info": ["bit", "byte", "kbit", "kbyte", "kibyte", "Mibyte", "Gibit"],325 "speed": ["kn", "m/h", "m/hr", "m/s", "m/sec", "mph", "km/h", "km/hr"],326 "misc": ["Pa", "kPa", "hPa"],327 "misc2": ["N", "dyn", "dy", "kN"],328 "misc3": ["J", "e", "Wh", "wh", "kJ", "kWh"],329 "misc4": ["W", "w", "kW", "MW"],330 "misc5": ["T", "ga", "mT"],331 "misc6": ["g", "kg", "mg"],332 }333 c = []334 for units in groups.values():335 base = units[0]336 for u in units:337 c.append((f'=CONVERT(2.5,"{u}","{base}")', {"tol": 1e-12}))338 c.append((f'=CONVERT(2.5,"{base}","{u}")', {"tol": 1e-12}))339 c += [('=CONVERT(100,"C","F")', {"tol": 1e-12}), ('=CONVERT(-40,"F","C")', {"tol": 1e-12}), ('=CONVERT(0,"K","C")', {"tol": 1e-12}), ('=CONVERT(300,"Rank","K")', {"tol": 1e-12}),340 ('=CONVERT(80,"Reau","F")', {"tol": 1e-12}), ('=CONVERT(1,"mK","K")', {"tol": 1e-12}), '=CONVERT(1,"m","g")', '=CONVERT(1,"xyz","m")', '=CONVERT(1,"kft","m")',341 '=CONVERT(1,"M","m")', '=CONVERT("a","m","ft")', ('=CONVERT(1,"dam","m")', {}), ('=CONVERT(1,"day","hr")', {}), ('=CONVERT(68,"F","K")', {"tol": 1e-12}),342 ('=CONVERT(1,"mi","km")', {"tol": 1e-13}), ('=CONVERT(1,"gal","l")', {"tol": 1e-13})]343 return c344345346def complex_numbers():347 zs = ['"3+4i"', '"-2.5-1.5i"', '"i"', '"-i"', '"5"', '"2j"', '"1.5e2+2e-1i"', "7", '"0"', '"1+i"', '"-3"', '"0.5-0.25j"', '"abc"', '"3+4k"']348 c = []349 for z in zs:350 for fn in ["IMREAL", "IMAGINARY", "IMABS", "IMARGUMENT"]:351 c.append((f"={fn}({z})", {"tol": 1e-12}))352 for fn in ["IMCONJUGATE", "IMSQRT", "IMEXP", "IMLN", "IMLOG10", "IMLOG2", "IMSIN", "IMCOS"]:353 c.append((f"={fn}({z})", {"kind": "complex", "tol": 1e-11, "abs": 1e-13}))354 for p in [2, 3, 0.5, -1, 0]:355 c.append((f"=IMPOWER({z},{p})", {"kind": "complex", "tol": 1e-11, "abs": 1e-13}))356 for a, b in itertools.product(zs[:6] + ['"1+i"'], ['"1-2i"', '"3"', '"-i"', '"0"']):357 for fn in ["IMSUB", "IMDIV"]:358 c.append((f"={fn}({a},{b})", {"kind": "complex", "tol": 1e-11, "abs": 1e-13}))359 for fn in ["IMSUM", "IMPRODUCT"]:360 if "j" in a:361 continue362 c.append((f"={fn}({a},{b})", {"kind": "complex", "tol": 1e-11, "abs": 1e-13}))363 c.append((f"={fn}({a},{b},\"2+2i\")", {"kind": "complex", "tol": 1e-11, "abs": 1e-13}))364 c += ["=COMPLEX(3,4)", "=COMPLEX(3,-4)", "=COMPLEX(0,1)", "=COMPLEX(1,0)", "=COMPLEX(0,0)", '=COMPLEX(3,4,"j")', '=COMPLEX(3,4,"k")', "=COMPLEX(-1.5,2.25)", "=COMPLEX(0,-1)",365 "=COMPLEX(1E-20,3)", "=COMPLEX(0.1,0.2)", '=IMSUM("1+i","1+j")', '=IMSUB("1+j","1+j")', '=IMPRODUCT("2j","3j")']366 for i in range(len(c)):367 if isinstance(c[i], str):368 c[i] = (c[i], {"kind": "complex"} if c[i].startswith("=COMPLEX") else {})369 return c370371372def database():373 c = []374 crits = ["D!V1:V2", "D!W1:X2", "D!Y1:Z3", "D!AA1:AB2", "D!AC1:AC2", "D!AD1:AD2", "D!AE1:AE2", "D!AF1:AF2"]375 fns = ["DSUM", "DAVERAGE", "DCOUNT", "DCOUNTA", "DMAX", "DMIN", "DPRODUCT", "DSTDEV", "DSTDEVP", "DVAR", "DVARP", "DGET"]376 for fn in fns:377 for cr in crits:378 for field in ['"Salary"', "3", '"bonus"', '"Name"']:379 if field == '"Name"' and fn not in ("DCOUNT", "DCOUNTA", "DGET"):380 continue # LibreOffice returns 0 for statistics of a text field, Excel an error381 c.append((f"={fn}(D!P1:T9,{field},{cr})", {"tol": 1e-12}))382 c += ['=DSUM(D!P1:T9,"Nope",D!V1:V2)', "=DSUM(D!P1:T9,9,D!V1:V2)", "=DSUM(D!P1:T9,0,D!V1:V2)", '=DCOUNT(D!P1:T9,,D!V1:V2)', '=DCOUNTA(D!P1:T9,,D!W1:X2)']383 return [(x, {}) if isinstance(x, str) else x for x in c]384385386def distributions():387 """Cross-check of the distribution functions against LibreOffice (loose tolerance: LibreOffice's388 own algorithms are only accurate to about 1e-10 in places; the tight check is the scipy fixture)."""389 t = {"tol": 1e-8, "abs": 1e-300}390 c = []391 c += calls("NORM.DIST", [(1, 0, 1, True), (1, 0, 1, False), (42, 40, 1.5, True), (-3, 0, 1, True), (0, 0, 0, True), (1, 2, -1, True)], **t)392 c += calls("NORMDIST", [(1, 0, 1, True), (42, 40, 1.5, False)], **t)393 c += calls("NORM.INV", [(0.9, 40, 1.5), (0.001, 0, 1), (0, 0, 1), (1, 0, 1), (0.5, 3, 2)], **t) + calls("NORMINV", [(0.9, 40, 1.5)], **t)394 c += calls("NORM.S.DIST", [(1.333, True), (1.333, False), (-8, True)], **t) + calls("NORMSDIST", [(1.333,), (-2,)], **t)395 c += calls("NORM.S.INV", [(0.9,), (0.01,), (1e-10,), (0,), (1,)], **t) + calls("NORMSINV", [(0.75,)], **t)396 c += calls("PHI", [(0,), (0.75,), (-3,)], **t) + calls("GAUSS", [(2,), (-1,), (0,)], **t)397 c += calls("LOGNORM.DIST", [(4, 3.5, 1.2, True), (4, 3.5, 1.2, False), (0, 1, 1, True), (1, 1, 0, True)], **t) + calls("LOGNORMDIST", [(4, 3.5, 1.2)], **t)398 c += calls("LOGNORM.INV", [(0.039084, 3.5, 1.2), (0.5, 0, 1)], **t) + calls("LOGINV", [(0.3, 1, 2)], **t)399 c += calls("T.DIST", [(1.5, 7, True), (1.5, 7, False), (-2, 3, True), (0, 5, False), (1, 0, True), (60, 1, True), (8, 3.9, True)], **t)400 c += calls("T.DIST.2T", [(1.96, 60), (0, 5), (-1, 5), (2.5, 1)], **t) + calls("T.DIST.RT", [(1.96, 60), (-1.96, 60), (0, 2)], **t)401 c += calls("TDIST", [(1.96, 60, 2), (1.96, 60, 1), (-1, 5, 1), (1, 5, 3)], **t)402 c += calls("T.INV", [(0.75, 2), (0.05, 10), (0.5, 4), (0, 3), (1, 3), (0.999, 30)], **t) + calls("T.INV.2T", [(0.05, 60), (1, 5), (0.546449, 60), (0, 5)], **t) + calls("TINV", [(0.1, 12)], **t)403 c += calls("CHISQ.DIST", [(0.5, 1, True), (2, 3, False), (10, 4, True), (-1, 2, True), (3, 0, True), (7.5, 2.9, True)], **t)404 c += calls("CHISQ.DIST.RT", [(18.307, 10), (0, 3), (3, 1)], **t) + calls("CHIDIST", [(18.307, 10)], **t)405 c += calls("CHISQ.INV", [(0.93, 1), (0.6, 2), (0, 3), (0.001, 20)], **t) + calls("CHISQ.INV.RT", [(0.050001, 10), (1, 3), (0.5, 7)], **t) + calls("CHIINV", [(0.05, 10)], **t)406 c += calls("F.DIST", [(15.2069, 6, 4, True), (15.2069, 6, 4, False), (0, 2, 3, True), (1, 1, 1, False), (2.5, 10, 20, True), (-1, 2, 2, True)], **t)407 c += calls("F.DIST.RT", [(15.2069, 6, 4), (0.5, 3, 7)], **t) + calls("FDIST", [(15.2069, 6, 4)], **t)408 c += calls("F.INV", [(0.01, 6, 4), (0.9, 3, 12)], **t) + calls("F.INV.RT", [(0.01, 6, 4), (1, 3, 3), (0.25, 8, 5)], **t) + calls("FINV", [(0.01, 6, 4)], **t)409 c += calls("EXPON.DIST", [(0.2, 10, True), (0.2, 10, False), (0, 1, False), (-1, 1, True), (1, 0, True)], **t) + calls("EXPONDIST", [(0.2, 10, True)], **t)410 c += calls("GAMMA.DIST", [(10.00001131, 9, 2, False), (10.00001131, 9, 2, True), (0, 1, 2, False), (2, 0.5, 1, False), (3, 2.5, 0.5, True), (-1, 1, 1, True)], **t)411 c += calls("GAMMADIST", [(10, 9, 2, True)], **t) + calls("GAMMA.INV", [(0.068094, 9, 2), (0.5, 1, 1), (0, 3, 2), (0.99, 0.5, 3)], **t) + calls("GAMMAINV", [(0.3, 4, 5)], **t)412 c += calls("GAMMA", [(2.5,), (5,), (-3.75,), (0,), (-2,), (0.5,), (171,), (172,)], **t) + calls("GAMMALN", [(4,), (0.5,), (100,), (0,), (-1,)], **t) + calls("GAMMALN.PRECISE", [(4.5,), (1e-5,)], **t)413 c += calls("BETA.DIST", [(2, 8, 10, True, 1, 3), (2, 8, 10, False, 1, 3), (0.4, 2, 3, True), (0.4, 2, 3, False), (0, 1, 3, False), (1, 2, 1, False), (1.5, 2, 3, True), (0.5, 0.5, 0.5, True)], **t)414 c += calls("BETADIST", [(2, 8, 10, 1, 3), (0.3, 2, 5)], **t) + calls("BETA.INV", [(0.685470581, 8, 10, 1, 3), (0.5, 2, 2), (0.01, 0.5, 3)], **t) + calls("BETAINV", [(0.9, 3, 4)], **t)415 c += calls("WEIBULL.DIST", [(105, 20, 100, True), (105, 20, 100, False), (0, 1, 1, False), (2, 1.5, 3, True)], **t) + calls("WEIBULL", [(105, 20, 100, True)], **t)416 c += calls("BINOM.DIST", [(6, 10, 0.5, False), (6, 10, 0.5, True), (0, 10, 0.1, False), (10, 10, 0.9, True), (11, 10, 0.5, True), (3, 20, 0, True), (50, 100, 0.5, True), (6.9, 10.9, 0.5, False)], **t)417 c += calls("BINOMDIST", [(6, 10, 0.5, False)], **t) + calls("BINOM.DIST.RANGE", [(60, 0.75, 48), (60, 0.75, 45, 50), (10, 0.5, 0, 10)], **t)418 c += calls("BINOM.INV", [(6, 0.5, 0.75), (100, 0.3, 0.5), (10, 0.5, 0.001), (10, 0.5, 0.9999), (20, 0.1, 0.5)], **t) + calls("CRITBINOM", [(6, 0.5, 0.75)], **t)419 c += calls("POISSON.DIST", [(2, 5, True), (2, 5, False), (0, 0.5, False), (20, 4.5, True), (-1, 2, True), (3.9, 2, False)], **t) + calls("POISSON", [(2, 5, True)], **t)420 c += calls("HYPGEOM.DIST", [(1, 4, 8, 20, True), (1, 4, 8, 20, False), (0, 4, 8, 20, False), (4, 4, 8, 20, True), (5, 4, 8, 20, True), (10, 30, 25, 100, True)], **t)421 c += calls("HYPGEOMDIST", [(1, 4, 8, 20)], **t)422 c += calls("NEGBINOM.DIST", [(10, 5, 0.25, True), (10, 5, 0.25, False), (0, 1, 0.5, False), (3, 2, 0.7, True)], **t) + calls("NEGBINOMDIST", [(10, 5, 0.25)], **t)423 c += calls("CONFIDENCE", [(0.05, 2.5, 50)], **t) + calls("CONFIDENCE.NORM", [(0.05, 2.5, 50), (0.1, 1, 1), (0, 1, 5), (0.05, 0, 5)], **t) + calls("CONFIDENCE.T", [(0.05, 1, 50), (0.1, 2, 2), (0.05, 1, 1)], **t)424 tests = ["=Z.TEST(D!A1:A12,4)", "=Z.TEST(D!A1:A12,6,3)", "=ZTEST(D!A1:A12,8)", "=Z.TEST(D!F1:F12,5.5,2.2)"]425 for tails in [1, 2]:426 for typ in [1, 2, 3]:427 tests += [f"=T.TEST(D!A1:A12,D!B1:B12,{tails},{typ})", f"=T.TEST(D!A1:A12,D!F1:F8,{tails},{typ})", f"=TTEST(D!E1:E12,D!K1:K12,{tails},{typ})"]428 tests += ["=F.TEST(D!A1:A12,D!B1:B12)", "=F.TEST(D!F1:F12,D!A1:A7)", "=FTEST(D!E1:E12,D!K1:K12)", "=CHISQ.TEST(D!AK1:AL3,D!AM1:AN3)", "=CHITEST(D!AK1:AL3,D!AM1:AN3)",429 "=CHISQ.TEST(D!AK1:AK3,D!AM1:AM3)", "=CHISQ.TEST(D!AK1:AL1,D!AM1:AN1)", "=T.TEST(D!A1:A12,D!B1:B12,3,1)", "=T.TEST(D!A1:A12,D!B1:B12,2,4)"]430 c += [(f, dict(t)) for f in tests]431 return c432433434if __name__ == "__main__":435 # Regressors side by side for multi-variable LINEST: J20:K31 = (A, K)436 for i in range(12):437 D["cells"][f"J{20 + i}"] = D["cells"][f"A{1 + i}"]438 D["cells"][f"K{20 + i}"] = D["cells"][f"K{1 + i}"]439 import overrides440 sets = [("lo_rounding", rounding), ("lo_math", math), ("lo_trig", trig), ("lo_text_math", text_math), ("lo_matrix", matrix), ("lo_subtotal", subtotal),441 ("lo_conditional", conditional), ("lo_stats", stats), ("lo_paired", paired), ("lo_regression", regression), ("lo_information", information),442 ("lo_engineering", engineering), ("lo_convert", convert), ("lo_complex", complex_numbers), ("lo_database", database), ("lo_distributions", distributions)]443 total = 0444 for name, fn in sets:445 total += len(libreoffice_fixture(name, [D, H], fn(), overrides.EXCEL.get(name, {})))446 print("total", total)447