SPB Git

Repository Public

gen_arrays.py 18.1 KB 331 lines python

1"""Reference cases for the dynamic-array functions.23Every case carries an independent Python model result ("py"). Functions LibreOffice 26.8 implements4are additionally recalculated there and the two references are compared before the fixture is5written. The LAMBDA helpers (MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY) do not exist in6LibreOffice: their references are Python-only. So are mixed-type sorts, because LibreOffice treats7logical values as numbers while Excel orders numbers < text < FALSE < TRUE.8"""9import functools10import sys1112from lo_harness import Fixture, grid, report_disagreements1314NA = "#N/A"15ERR = "#VALUE!"      # any error (the Swift comparison only checks "is an error" for this kind)1617names = ["Ann", "bob", "Cid", "ann", "Dee", "Eve", "bob", "Fay", "Gus", "Hal"]18region = ["East", "West", "East", "North", "West", "East", "West", "South", "North", "East"]19units = [30, 12, 45, 30, 8, 45, 12, 60, 5, 30]20price = [2.5, 4.0, 1.25, 3.0, 9.5, 1.25, 4.5, 0.75, 12.0, 2.0]21T = [list(r) for r in zip(names, region, units, price)]                # A1:D1022M = [[1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12]]                      # H1:K323S = [[7], [3], [9], [3], [1]]                                          # F1:F5 numbers24G = [[1, None, 3], ["x", NA, None], [7, 8, "y"]]                       # M1:O3 with blanks and an error25X = [[3], ["b"], [True], ["A"], [1], [False], ["c"], [2]]              # Q1:Q8 mixed types2627fx = Fixture("arrays", "FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, TAKE, DROP, CHOOSEROWS, CHOOSECOLS, EXPAND, HSTACK, VSTACK, "28             "TOCOL, TOROW, WRAPROWS, WRAPCOLS, MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY — Python model, cross-checked with LibreOffice 26.8 where available")29for c, colname in enumerate("ABCD"):30    fx.column(colname, [r[c] for r in T])31fx.column("F", [r[0] for r in S])32for r, rowvals in enumerate(M):33    fx.row(r + 1, rowvals, 8)34for r, rowvals in enumerate(G):35    fx.row(r + 1, ["=NA()" if v == NA else v for v in rowvals], 13)36fx.column("Q", [r[0] for r in X])373839# ---- independent model --------------------------------------------------------------------40def col(m, i): return [[r[i]] for r in m]41def cols_of(m, idx): return [[r[i] for i in idx] for r in m]42def rows_of(m, idx): return [m[i] for i in idx]43def transpose(m): return [list(r) for r in zip(*m)]444546def rank(v):47    if v is None: return 448    if isinstance(v, bool): return 249    if isinstance(v, (int, float)): return 050    if isinstance(v, str) and v.startswith("#"): return 351    return 1525354def cmp_values(a, b, descending):55    ra, rb = rank(a), rank(b)56    if ra == 4 or rb == 4:57        return 0 if ra == rb else (1 if ra == 4 else -1)58    if ra != rb:59        c = -1 if ra < rb else 160    elif ra == 1:61        x, y = a.casefold(), b.casefold()62        c = (x > y) - (x < y)63    elif ra == 3:64        c = 065    else:66        c = (a > b) - (a < b)67    return -c if descending else c686970def sort_rows(m, keys):71    """keys: list of (column values, descending). Stable."""72    def compare(i, j):73        for vals, desc in keys:74            c = cmp_values(vals[i], vals[j], desc)75            if c: return c76        return (i > j) - (i < j)77    order = sorted(range(len(m)), key=functools.cmp_to_key(compare))78    return [m[i] for i in order]798081def py_sort(m, index=1, order=1, by_col=False):82    if by_col:83        return transpose(py_sort(transpose(m), index, order))84    idx = index if isinstance(index, list) else [index]85    ords = order if isinstance(order, list) else [order] * len(idx)86    return sort_rows(m, [([r[i - 1] for r in m], o == -1) for i, o in zip(idx, ords)])878889def py_sortby(m, *pairs):90    return sort_rows(m, [([r[0] for r in by], o == -1) for by, o in pairs])919293def py_unique(m, by_col=False, once=False):94    if by_col:95        return transpose(py_unique(transpose(m), False, once))96    def key(r): return tuple((type(v).__name__, v.casefold() if isinstance(v, str) else v) for v in r)97    counts, first = {}, []98    for r in m:99        k = key(r)100        if k not in counts: first.append(r)101        counts[k] = counts.get(k, 0) + 1102    out = [r for r in first if not once or counts[key(r)] == 1]103    return out104105106def py_take(m, rows=None, columns=None):107    def cut(seq, k):108        if k is None: return seq109        return seq[:k] if k > 0 else seq[k:]110    return [cut(r, columns) for r in cut(m, rows)]111112113def py_drop(m, rows=None, columns=None):114    def cut(seq, k):115        if not k: return seq116        return seq[k:] if k > 0 else seq[:k]117    return [cut(r, columns) for r in cut(m, rows)]118119120def py_expand(m, rows, columns=None, pad=NA):121    columns = columns or len(m[0])122    return [[m[r][c] if r < len(m) and c < len(m[0]) else pad for c in range(columns)] for r in range(rows)]123124125def py_hstack(*ms):126    h = max(len(m) for m in ms)127    return [sum(([m[r][c] if r < len(m) else NA for c in range(len(m[0]))] for m in ms), []) for r in range(h)]128129130def py_vstack(*ms):131    w = max(len(m[0]) for m in ms)132    return [r + [NA] * (w - len(r)) for m in ms for r in m]133134135def py_flat(m, ignore=0, by_column=False):136    seq = [v for r in (transpose(m) if by_column else m) for v in r]137    return [v for v in seq if not ((ignore & 1 and v is None) or (ignore & 2 and isinstance(v, str) and v.startswith("#")))]138139140def py_wraprows(vec, n, pad=NA):141    flat = [v for r in vec for v in r]142    lines = [flat[i:i + n] for i in range(0, len(flat), n)]143    lines[-1] += [pad] * (n - len(lines[-1]))144    return lines145146147def G_(m):148    """matrix → fixture grid (1×1 collapses to a scalar)."""149    if len(m) == 1 and len(m[0]) == 1:150        return m[0][0]151    return grid(len(m), len(m[0]), [v for r in m for v in r])152153154def add(formula, model, lo=True, **kw):155    fx.case(formula, py=G_(model) if isinstance(model, list) else model, lo=lo, **kw)156157158# ---- FILTER -------------------------------------------------------------------------------159for k in [0, 10, 12, 30, 44]:160    add("=FILTER(A1:D10,C1:C10>%d)" % k, [r for r in T if r[2] > k])161    add("=FILTER(A1:A10,C1:C10=%d,\"none\")" % k, [[r[0]] for r in T if r[2] == k] or "none")162add("=FILTER(C1:D10,(C1:C10>10)*(D1:D10<3))", [[r[2], r[3]] for r in T if r[2] > 10 and r[3] < 3])163add("=FILTER(A1:B10,(C1:C10>40)+(D1:D10>9))", [[r[0], r[1]] for r in T if r[2] > 40 or r[3] > 9])164add("=FILTER(H1:K3,H1:K1>2)", cols_of(M, [2, 3]))165add("=FILTER(H1:K3,{1,0,0,1})", cols_of(M, [0, 3]))166add("=FILTER(H1:K3,{TRUE;FALSE;TRUE})", rows_of(M, [0, 2]))167add("=FILTER(H1:K3,I1:I3>5)", rows_of(M, [1, 2]))168add("=FILTER(A1:D10,C1:C10>100)", ERR)169add("=FILTER(A1:D10,C1:C10>100,\"\")", "")170add("=FILTER(A1:D10,C1:C5>1)", ERR)171add("=FILTER(F1:F5,F1:F5<>3)", [[7], [9], [1]])172add("=FILTER({1,2,3;4,5,6},{TRUE;FALSE})", [[1, 2, 3]])173add("=FILTER(B1:B10,B1:B10=\"east\")", [["East"]] * 4)174175# ---- SORT ---------------------------------------------------------------------------------176for i in (1, 2, 3, 4):177    for o in (1, -1):178        add("=SORT(A1:D10,%d,%d)" % (i, o), py_sort(T, i, o))179add("=SORT(F1:F5)", py_sort(S)); add("=SORT(F1:F5,1,-1)", py_sort(S, 1, -1)); add("=SORT(F1:F5,,-1)", py_sort(S, 1, -1))180add("=SORT(C1:C10)", py_sort(col(T, 2))); add("=SORT(B1:B10,1,-1)", py_sort(col(T, 1), 1, -1))181add("=SORT(A1:D10,{2,3},{1,-1})", py_sort(T, [2, 3], [1, -1]))182add("=SORT(A1:D10,{3,4})", py_sort(T, [3, 4], [1, 1]))183add("=SORT(A1:D10,{3,1},{-1,1})", py_sort(T, [3, 1], [-1, 1]))184add("=SORT(B1:D10,{1,2,3},{-1,-1,1})", py_sort(cols_of(T, [1, 2, 3]), [1, 2, 3], [-1, -1, 1]))185add("=SORT(H1:K3,2,-1,TRUE)", py_sort(M, 2, -1, True)); add("=SORT({3,1,2;\"c\",\"a\",\"b\"},1,1,TRUE)", [[1, 2, 3], ["a", "b", "c"]])186add("=SORT({3,1,2;\"c\",\"a\",\"b\"},2,-1,TRUE)", [[3, 2, 1], ["c", "b", "a"]])187add("=SORT(A1:D10,5)", ERR); add("=SORT(A1:D10,0)", ERR); add("=SORT(A1:D10,1,2)", ERR)188add("=SORT(5)", 5, lo=False)             # LibreOffice rejects scalar arguments; Excel accepts them189# mixed types: Excel order numbers < text < FALSE < TRUE (LibreOffice differs → Python reference only)190add("=SORT(Q1:Q8)", py_sort(X), lo=False); add("=SORT(Q1:Q8,1,-1)", py_sort(X, 1, -1), lo=False)191add("=SORT(M1:O3,2)", py_sort(G, 2), lo=False); add("=SORT(M1:O3,3,-1)", py_sort(G, 3, -1), lo=False)192193# ---- SORTBY -------------------------------------------------------------------------------194add("=SORTBY(A1:A10,C1:C10)", py_sortby(col(T, 0), (col(T, 2), 1)))195add("=SORTBY(A1:A10,C1:C10,-1)", py_sortby(col(T, 0), (col(T, 2), -1)))196add("=SORTBY(A1:D10,B1:B10,1,C1:C10,-1)", py_sortby(T, (col(T, 1), 1), (col(T, 2), -1)))197add("=SORTBY(A1:D10,C1:C10,-1,D1:D10,1)", py_sortby(T, (col(T, 2), -1), (col(T, 3), 1)))198add("=SORTBY(A1:B10,D1:D10)", py_sortby(cols_of(T, [0, 1]), (col(T, 3), 1)))199add("=SORTBY(A1:B10,B1:B10,-1,A1:A10,-1)", py_sortby(cols_of(T, [0, 1]), (col(T, 1), -1), (col(T, 0), -1)))200add("=SORTBY(H1:K3,{3,1,4,2})", cols_of(M, [1, 3, 0, 2])); add("=SORTBY(H1:K3,{3;1;2},-1)", rows_of(M, [0, 2, 1]))201add("=SORTBY(A1:A10,C1:C9)", ERR); add("=SORTBY(A1:A10,C1:C10,3)", ERR)202add("=SORTBY(Q1:Q8,Q1:Q8,-1)", py_sort(X, 1, -1), lo=False)203204# ---- UNIQUE -------------------------------------------------------------------------------205add("=UNIQUE(B1:B10)", py_unique(col(T, 1))); add("=UNIQUE(A1:A10)", py_unique(col(T, 0))); add("=UNIQUE(C1:C10)", py_unique(col(T, 2)))206add("=UNIQUE(B1:C10)", py_unique(cols_of(T, [1, 2]))); add("=UNIQUE(A1:B10)", py_unique(cols_of(T, [0, 1])))207add("=UNIQUE(C1:C10,FALSE,TRUE)", py_unique(col(T, 2), False, True)); add("=UNIQUE(A1:A10,,TRUE)", py_unique(col(T, 0), False, True))208add("=UNIQUE(B1:B10,FALSE,TRUE)", py_unique(col(T, 1), False, True))209add("=UNIQUE({1,2,1,3,2},TRUE)", [[1, 2, 3]]); add("=UNIQUE({1,2,1,3,2},TRUE,TRUE)", [[3]]); add("=UNIQUE({1,2,1,3,2})", [[1, 2, 1, 3, 2]])210add("=UNIQUE({1,1;2,2;1,1;1,2})", [[1, 1], [2, 2], [1, 2]]); add("=UNIQUE({1;1;1},,TRUE)", ERR, lo=False)211add("=UNIQUE(F1:F5)", py_unique(S)); add("=UNIQUE({\"a\";\"A\";\"b\";1;\"1\"})", [["a"], ["b"], [1], ["1"]], lo=False)212add("=SORT(UNIQUE(B1:B10))", py_sort(py_unique(col(T, 1))))213214# ---- SEQUENCE -----------------------------------------------------------------------------215def py_seq(r, c=1, start=1, step=1): return [[start + (i * c + j) * step for j in range(c)] for i in range(r)]216for args, model in [("4", py_seq(4)), ("3,4", py_seq(3, 4)), ("2,3,10", py_seq(2, 3, 10)), ("2,3,10,5", py_seq(2, 3, 10, 5)), ("5,1,0,-2", py_seq(5, 1, 0, -2)),217                    ("1,6,0.5,0.25", py_seq(1, 6, 0.5, 0.25)), ("1", 1), ("3,,5", py_seq(3, 1, 5)), ("2,2,,3", py_seq(2, 2, 1, 3)), ("3.9,2.2", py_seq(3, 2))]:218    add("=SEQUENCE(%s)" % args, model)219add("=SEQUENCE(0)", ERR, lo=False); add("=SEQUENCE(-1)", ERR); add("=SEQUENCE(2,0)", ERR, lo=False)220221# ---- TAKE / DROP --------------------------------------------------------------------------222for r in (1, 2, 3, 5, -1, -2, -5):223    add("=TAKE(H1:K3,%d)" % r, py_take(M, r))224    for c in (1, 3, -2, 9):225        add("=TAKE(H1:K3,%d,%d)" % (r, c), py_take(M, r, c))226for c in (1, 2, -1, -3):227    add("=TAKE(H1:K3,,%d)" % c, py_take(M, None, c))228add("=TAKE(A1:D10,3)", py_take(T, 3)); add("=TAKE(A1:D10,-2,2)", py_take(T, -2, 2)); add("=TAKE(H1:K3,0)", ERR)229for r in (1, 2, -1, -2):230    add("=DROP(H1:K3,%d)" % r, py_drop(M, r))231    for c in (1, 3, -2):232        add("=DROP(H1:K3,%d,%d)" % (r, c), py_drop(M, r, c))233for c in (1, 2, -1, -3):234    add("=DROP(H1:K3,,%d)" % c, py_drop(M, None, c))235add("=DROP(A1:D10,7)", py_drop(T, 7)); add("=DROP(A1:D10,-8,-2)", py_drop(T, -8, -2)); add("=DROP(H1:K3,3)", ERR); add("=DROP(H1:K3,1,-4)", ERR)236add("=DROP(H1:K3,0)", M)237238# ---- CHOOSEROWS / CHOOSECOLS --------------------------------------------------------------239for idx in ([1], [3], [-1], [1, 3], [3, 1], [2, 2], [-1, -3], [1, -1, 2]):240    add("=CHOOSEROWS(H1:K3,%s)" % ",".join(map(str, idx)), rows_of(M, [i - 1 if i > 0 else 3 + i for i in idx]))241for idx in ([1], [4], [-1], [1, 3], [4, 1], [2, 2], [-1, -4], [1, -1, 2, 3]):242    add("=CHOOSECOLS(H1:K3,%s)" % ",".join(map(str, idx)), cols_of(M, [i - 1 if i > 0 else 4 + i for i in idx]))243add("=CHOOSEROWS(H1:K3,{1,3})", rows_of(M, [0, 2])); add("=CHOOSECOLS(H1:K3,{4;2})", cols_of(M, [3, 1]))244add("=CHOOSEROWS(H1:K3,4)", ERR); add("=CHOOSEROWS(H1:K3,0)", ERR); add("=CHOOSECOLS(H1:K3,-5)", ERR); add("=CHOOSECOLS(A1:D10,1,3)", cols_of(T, [0, 2]))245246# ---- EXPAND -------------------------------------------------------------------------------247add("=EXPAND(H1:K3,4)", py_expand(M, 4)); add("=EXPAND(H1:K3,4,6)", py_expand(M, 4, 6)); add("=EXPAND(H1:K3,3,5,0)", py_expand(M, 3, 5, 0))248add("=EXPAND(H1:K3,5,5,\"-\")", py_expand(M, 5, 5, "-")); add("=EXPAND(H1:K3,,6,\"x\")", py_expand(M, 3, 6, "x")); add("=EXPAND(H1:K3,3,4)", M)249add("=EXPAND(H1:K3,2)", ERR); add("=EXPAND(H1:K3,3,3)", ERR); add("=EXPAND(7,2,2)", [[7, NA], [NA, NA]], lo=False); add("=EXPAND(F1:F5,6,2,1)", py_expand(S, 6, 2, 1))250251# ---- HSTACK / VSTACK ----------------------------------------------------------------------252add("=HSTACK(A1:A3,C1:C3)", py_hstack(col(T, 0)[:3], col(T, 2)[:3])); add("=HSTACK(H1:I3,F1:F5)", py_hstack(cols_of(M, [0, 1]), S))253add("=HSTACK(1,2,3)", [[1, 2, 3]]); add("=HSTACK({1;2},\"x\",{3;4;5})", py_hstack([[1], [2]], [["x"]], [[3], [4], [5]]))254add("=HSTACK(H1:K1)", [M[0]]); add("=HSTACK(F1:F5,F1:F5)", py_hstack(S, S))255add("=VSTACK(H1:K1,H3:K3)", [M[0], M[2]]); add("=VSTACK(H1:K2,{1,2})", py_vstack(M[:2], [[1, 2]])); add("=VSTACK(1,2,3)", [[1], [2], [3]])256add("=VSTACK({1,2,3},\"x\",{4,5})", py_vstack([[1, 2, 3]], [["x"]], [[4, 5]])); add("=VSTACK(F1:F5,H1:H3)", py_vstack(S, col(M, 0)))257add("=VSTACK(A1:B2,C1:D2)", py_vstack(cols_of(T[:2], [0, 1]), cols_of(T[:2], [2, 3])))258259# ---- TOCOL / TOROW ------------------------------------------------------------------------260for ig in (0, 1, 2, 3):261    for bc in ("FALSE", "TRUE"):262        flat = py_flat(G, ig, bc == "TRUE")263        add("=TOCOL(M1:O3,%d,%s)" % (ig, bc), [[v] for v in flat])264        add("=TOROW(M1:O3,%d,%s)" % (ig, bc), [flat])265add("=TOCOL(H1:K3)", [[v] for v in py_flat(M)]); add("=TOCOL(H1:K3,,TRUE)", [[v] for v in py_flat(M, 0, True)])266add("=TOROW(H1:I3)", [py_flat(cols_of(M, [0, 1]))]); add("=TOROW(H1:I3,0,TRUE)", [py_flat(cols_of(M, [0, 1]), 0, True)]); add("=TOCOL(5)", 5, lo=False)267268# ---- WRAPROWS / WRAPCOLS ------------------------------------------------------------------269for n in (1, 2, 3, 4, 5, 7):270    add("=WRAPROWS(F1:F5,%d)" % n, py_wraprows(S, n)); add("=WRAPCOLS(F1:F5,%d)" % n, transpose(py_wraprows(S, n)))271    add("=WRAPROWS(H1:K1,%d,0)" % n, py_wraprows([[v] for v in M[0]], n, 0)); add("=WRAPCOLS(H1:K1,%d,\"-\")" % n, transpose(py_wraprows([[v] for v in M[0]], n, "-")))272add("=WRAPROWS(H1:K3,2)", ERR); add("=WRAPROWS(F1:F5,0)", ERR); add("=WRAPCOLS(H1:K3,2)", ERR)273274# ---- combinations -------------------------------------------------------------------------275add("=TAKE(SORT(A1:D10,3,-1),3)", py_take(py_sort(T, 3, -1), 3))276add("=SORT(FILTER(A1:D10,B1:B10=\"East\"),4)", py_sort([r for r in T if r[1] == "East"], 4))277add("=HSTACK(UNIQUE(B1:B10),SEQUENCE(5))", py_hstack(py_unique(col(T, 1)), py_seq(5)))278add("=CHOOSECOLS(SORTBY(A1:D10,D1:D10,-1),1,4)", cols_of(py_sortby(T, (col(T, 3), -1)), [0, 3]))279add("=TOCOL(TAKE(H1:K3,2,2))", [[1], [2], [5], [6]]); add("=SUM(TAKE(H1:K3,-1))", 42); add("=ROWS(UNIQUE(B1:B10))", 4)280add("=TRANSPOSE(SEQUENCE(2,3))", [[1, 4], [2, 5], [3, 6]]); add("=SUM(DROP(SEQUENCE(10),3))", 49)281282# ---- LAMBDA helpers (Python only) ---------------------------------------------------------283P = dict(lo=False)284add("=MAP(F1:F5,LAMBDA(x,x*2))", [[v[0] * 2] for v in S], **P)285add("=MAP(H1:K3,LAMBDA(x,x^2))", [[v * v for v in r] for r in M], **P)286add("=MAP(C1:C10,D1:D10,LAMBDA(u,p,u*p))", [[u * p] for u, p in zip(units, price)], **P)287add("=MAP(H1:K3,LAMBDA(x,IF(x>6,\"big\",\"small\")))", [["big" if v > 6 else "small" for v in r] for r in M], **P)288add("=MAP({1,2,3},{10,20,30},{100,200,300},LAMBDA(a,b,c,a+b+c))", [[111, 222, 333]], **P)289add("=MAP(5,LAMBDA(x,x+1))", 6, **P)290add("=MAP({1,2},LAMBDA(x,SEQUENCE(2)))", grid(1, 2, [ERR, ERR]), **P)291add("=MAP({1,2,3},LAMBDA(x,y,x+y))", ERR, **P)292add("=MAP({4,0,2},LAMBDA(x,8/x))", grid(1, 3, [2, "#DIV/0!", 4]), **P)293add("=REDUCE(0,F1:F5,LAMBDA(a,v,a+v))", sum(v[0] for v in S), **P)294add("=REDUCE(1,H1:K3,LAMBDA(a,v,a*v))", 479001600, **P)295add("=REDUCE(0,C1:C10,LAMBDA(a,v,IF(v>a,v,a)))", max(units), **P)296add("=REDUCE(\"\",A1:A3,LAMBDA(a,v,a&v))", "AnnbobCid", **P)297add("=REDUCE(100,{1,2,3},LAMBDA(a,v,a-v))", 94, **P)298add("=REDUCE(,{2,3,4},LAMBDA(a,v,a*v))", 24, **P)299add("=REDUCE(0,H1:K3,LAMBDA(a,v,a+IF(v>6,1,0)))", 6, **P)300add("=REDUCE(0,{1,2,3},LAMBDA(a,v,VSTACK(a,v)))", [[0], [1], [2], [3]], **P)301add("=SCAN(0,F1:F5,LAMBDA(a,v,a+v))", [[7], [10], [19], [22], [23]], **P)302add("=SCAN(1,{1,2,3,4,5},LAMBDA(a,v,a*v))", [[1, 2, 6, 24, 120]], **P)303add("=SCAN(0,H1:K3,LAMBDA(a,v,a+v))", [[1, 3, 6, 10], [15, 21, 28, 36], [45, 55, 66, 78]], **P)304add("=SCAN(,{2,3,4},LAMBDA(a,v,a*v))", [[2, 6, 24]], **P)305add("=SCAN(\"\",{\"a\",\"b\",\"c\"},LAMBDA(a,v,a&v))", [["a", "ab", "abc"]], **P)306add("=SCAN(0,C1:C10,LAMBDA(a,v,MAX(a,v)))", [[max(units[:i + 1])] for i in range(10)], **P)307add("=BYROW(H1:K3,LAMBDA(r,SUM(r)))", [[10], [26], [42]], **P)308add("=BYROW(H1:K3,LAMBDA(r,MAX(r)-MIN(r)))", [[3], [3], [3]], **P)309add("=BYROW(C1:D10,LAMBDA(r,INDEX(r,1,1)*INDEX(r,1,2)))", [[u * p] for u, p in zip(units, price)], **P)310add("=BYROW({1,2;3,4},LAMBDA(r,SUM(r)))", [[3], [7]], **P)311add("=BYROW(H1:K3,LAMBDA(r,r))", grid(3, 1, [ERR, ERR, ERR]), **P)312add("=BYROW(M1:O3,LAMBDA(r,COUNTA(r)))", [[2], [2], [3]], **P)313add("=BYCOL(H1:K3,LAMBDA(c,SUM(c)))", [[15, 18, 21, 24]], **P)314add("=BYCOL(H1:K3,LAMBDA(c,AVERAGE(c)))", [[5, 6, 7, 8]], **P)315add("=BYCOL(C1:D10,LAMBDA(c,MAX(c)))", [[60, 12.0]], **P)316add("=BYCOL({1,2;3,4},LAMBDA(c,COUNT(c)))", [[2, 2]], **P)317add("=MAKEARRAY(3,4,LAMBDA(r,c,r*10+c))", [[r * 10 + c for c in range(1, 5)] for r in range(1, 4)], **P)318add("=MAKEARRAY(2,2,LAMBDA(r,c,IF(r=c,1,0)))", [[1, 0], [0, 1]], **P)319add("=MAKEARRAY(1,1,LAMBDA(r,c,42))", 42, **P)320add("=MAKEARRAY(4,1,LAMBDA(r,c,INDEX(F1:F5,r)*2))", [[14], [6], [18], [6]], **P)321add("=MAKEARRAY(0,2,LAMBDA(r,c,1))", ERR, **P)322add("=SUM(MAP(SEQUENCE(10),LAMBDA(k,k*k)))", 385, **P)323add("=LET(f,LAMBDA(x,x*3),MAP({1,2,3},f))", [[3, 6, 9]], **P)324add("=BYROW(FILTER(H1:K3,H1:H3>1),LAMBDA(r,SUM(r)))", [[26], [42]], **P)325326doc = fx.write()327bad = report_disagreements(doc)328print("LibreOffice-checked: %d, Python-only: %d, disagreements: %d" % (329    sum(1 for c in doc["cases"] if c["source"] != "python"), sum(1 for c in doc["cases"] if c["source"] == "python"), len(bad)))330sys.exit(1 if bad else 0)331
Files main
Menu

Theme