SPB Git

Repository Public

EdgeCaseTests.swift 37.4 KB 576 lines swift

1import Foundation2import Testing3import SheetCore4@testable import FunctionsNumeric56/// Hand-written cases: Excel error codes, coercion rules, logical values (which LibreOffice cannot7/// vouch for), arrays, blanks and whole-column references.8@Suite struct ErrorCodeTests {9    static let cases: [(String, CellError)] = [10        ("=SQRT(-1)", .num), ("=LN(0)", .num), ("=LOG10(-5)", .num), ("=LOG(5,1)", .div0), ("=LOG(5,-2)", .num), ("=FACT(-1)", .num), ("=FACT(171)", .num),11        ("=FACTDOUBLE(-2)", .num), ("=COMBIN(3,5)", .num), ("=COMBIN(-1,2)", .num), ("=PERMUT(3,5)", .num), ("=ASIN(2)", .num), ("=ACOS(-1.5)", .num),12        ("=ACOSH(0.5)", .num), ("=ATANH(1)", .num), ("=ACOTH(0.5)", .num), ("=COT(0)", .div0), ("=CSC(0)", .div0), ("=COTH(0)", .div0), ("=CSCH(0)", .div0),13        ("=ATAN2(0,0)", .div0), ("=MOD(5,0)", .div0), ("=QUOTIENT(5,0)", .div0), ("=POWER(0,0)", .num), ("=POWER(0,-1)", .div0), ("=POWER(-8,1/3)", .num),14        ("=EXP(1000)", .num), ("=MROUND(-10,3)", .num), ("=FLOOR(5,0)", .div0), ("=ROMAN(4000)", .value), ("=ROMAN(-1)", .value), ("=ROMAN(5,5)", .value),15        ("=ARABIC(\"ABC\")", .value), ("=BASE(7,1)", .num), ("=BASE(-1,2)", .num), ("=DECIMAL(\"12\",2)", .num), ("=GCD(-4,8)", .num), ("=LCM(-4,8)", .num),16        ("=MULTINOMIAL(3,-1)", .num), ("=MMULT({1,2},{1,2})", .value), ("=MMULT({1,\"a\"},{1;2})", .value), ("=MINVERSE({1,2;2,4})", .num),17        ("=MINVERSE({1,2,3;4,5,6})", .value), ("=MDETERM({1,2,3;4,5,6})", .value), ("=MUNIT(0)", .value), ("=SUMPRODUCT({1,2,3},{1,2})", .value),18        ("=SUMX2MY2({1,2,3},{1,2})", .na), ("=RANDBETWEEN(5,1)", .num), ("=SUBTOTAL(12,A1:A3)", .value), ("=SUBTOTAL(9,{1,2,3})", .value),19        ("=AGGREGATE(20,4,A1:A3)", .value), ("=AGGREGATE(9,8,A1:A3)", .value), ("=AGGREGATE(14,4,A1:A3)", .value),20        ("=AVERAGEIF({1,2,3},\">5\")", .div0), ("=AVERAGEIFS({1,2,3},{1,2,3},\">5\")", .div0), ("=SUMIFS({1,2,3},{1,2},\">0\")", .value), ("=COUNTIFS({1,2,3},\">0\",{1,2},\">0\")", .value),21        ("=MEDIAN(\"a\")", .value), ("=MODE(1,2,3)", .na), ("=MODE.MULT(1,2,3)", .na), ("=STDEV(5)", .div0), ("=VAR.S(5)", .div0), ("=STDEV.P(\"x\")", .value),22        ("=SKEW(1,2)", .div0), ("=KURT(1,2,3)", .div0), ("=SKEW(3,3,3)", .div0), ("=GEOMEAN(1,0,2)", .num), ("=HARMEAN(1,-1)", .num), ("=TRIMMEAN({1,2,3},1)", .num),23        ("=TRIMMEAN({1,2,3},-0.1)", .num), ("=LARGE({1,2,3},0)", .num), ("=LARGE({1,2,3},4)", .num), ("=SMALL({1,2,3},4)", .num), ("=PERCENTILE({1,2,3},1.1)", .num),24        ("=PERCENTILE.EXC({1,2,3},0.1)", .num), ("=PERCENTILE.EXC({1,2,3},1)", .num), ("=QUARTILE({1,2,3},5)", .num), ("=QUARTILE.EXC({1,2,3},0)", .num),25        ("=RANK(9,{1,2,3})", .na), ("=PERCENTRANK({1,2,3},4)", .na), ("=PERCENTRANK({1,2,3},2,0)", .num), ("=STANDARDIZE(1,2,0)", .num), ("=FISHER(1)", .num),26        ("=CORREL({1,2,3},{1,2})", .na), ("=CORREL({1,2,3},{5,5,5})", .div0), ("=SLOPE({1,2,3},{5,5,5})", .div0), ("=STEYX({1,2},{3,4})", .div0), ("=COVARIANCE.S({1},{2})", .div0),27        ("=FORECAST(1,{1,2,3},{5,5,5})", .div0), ("=PROB({1,2},{0.5,0.6},1)", .num), ("=PROB({1,2},{0.5},1)", .na),28        ("=NORM.DIST(1,0,0,TRUE)", .num), ("=NORM.INV(0,0,1)", .num), ("=NORM.INV(1,0,1)", .num), ("=NORM.INV(0.5,0,-1)", .num), ("=NORM.S.INV(1.5)", .num),29        ("=LOGNORM.DIST(0,1,1,TRUE)", .num), ("=LOGNORM.INV(0.5,0,0)", .num), ("=T.DIST(1,0,TRUE)", .num), ("=T.DIST.2T(-1,5)", .num), ("=TDIST(-1,5,1)", .num),30        ("=TDIST(1,5,3)", .num), ("=T.INV(0,5)", .num), ("=T.INV(1,5)", .num), ("=T.INV.2T(0,5)", .num), ("=CHISQ.DIST(-1,2,TRUE)", .num), ("=CHISQ.DIST(1,0,TRUE)", .num),31        ("=CHISQ.INV(-0.1,2)", .num), ("=CHISQ.INV(1,2)", .num), ("=CHISQ.INV.RT(0,2)", .num), ("=F.DIST(-1,2,2,TRUE)", .num), ("=F.DIST(1,0,2,TRUE)", .num),32        ("=F.INV.RT(0,2,2)", .num), ("=EXPON.DIST(-1,1,TRUE)", .num), ("=EXPON.DIST(1,0,TRUE)", .num), ("=GAMMA.DIST(-1,1,1,TRUE)", .num), ("=GAMMA.DIST(1,0,1,TRUE)", .num),33        ("=GAMMA.INV(1.5,1,1)", .num), ("=GAMMA(0)", .num), ("=GAMMA(-2)", .num), ("=GAMMALN(0)", .num), ("=GAMMALN(-1)", .num), ("=BETA.DIST(2,1,1,TRUE)", .num),34        ("=BETA.DIST(0.5,0,1,TRUE)", .num), ("=BETA.DIST(2,1,1,TRUE,3,3)", .num), ("=BETA.INV(0,1,1)", .num), ("=WEIBULL.DIST(-1,1,1,TRUE)", .num),35        ("=BINOM.DIST(11,10,0.5,TRUE)", .num), ("=BINOM.DIST(-1,10,0.5,TRUE)", .num), ("=BINOM.DIST(5,10,1.5,TRUE)", .num), ("=BINOM.INV(10,0.5,1.5)", .num),36        ("=POISSON.DIST(-1,2,TRUE)", .num), ("=POISSON.DIST(1,-2,TRUE)", .num), ("=HYPGEOM.DIST(5,4,8,20,TRUE)", .num), ("=HYPGEOM.DIST(1,30,8,20,TRUE)", .num),37        ("=NEGBINOM.DIST(-1,5,0.25,TRUE)", .num), ("=NEGBINOM.DIST(1,0,0.25,TRUE)", .num), ("=NEGBINOM.DIST(1,5,1.25,TRUE)", .num),38        ("=CONFIDENCE.NORM(0,1,5)", .num), ("=CONFIDENCE.NORM(0.05,0,5)", .num), ("=CONFIDENCE.NORM(0.05,1,0)", .num), ("=CONFIDENCE.T(0.05,1,1)", .div0),39        ("=T.TEST({1,2,3},{4,5,6},3,1)", .num), ("=T.TEST({1,2,3},{4,5,6},2,4)", .num), ("=T.TEST({1,2,3},{4,5},2,1)", .na), ("=F.TEST({1},{2,3})", .div0),40        ("=F.TEST({1,1,1},{2,3,4})", .div0), ("=CHISQ.TEST({1,2},{1,2,3})", .na), ("=CHISQ.TEST({1,2},{0,2})", .div0), ("=Z.TEST({1},2)", .div0),41        ("=ISEVEN(\"x\")", .value), ("=ISEVEN(TRUE)", .value), ("=ERROR.TYPE(5)", .na), ("=ISFORMULA(5)", .value), ("=FORMULATEXT(A1)", .na), ("=CELL(\"nope\",A1)", .value),42        ("=INFO(\"nope\")", .value), ("=SHEET(\"NoSuchSheet\")", .na),43        ("=BIN2DEC(\"102\")", .num), ("=BIN2DEC(\"11111111111\")", .num), ("=DEC2BIN(512)", .num), ("=DEC2BIN(-513)", .num), ("=DEC2BIN(5,2)", .num), ("=DEC2BIN(5,0)", .num),44        ("=DEC2HEX(549755813888)", .num), ("=DEC2OCT(536870912)", .num), ("=HEX2DEC(\"G1\")", .num), ("=HEX2BIN(\"200\")", .num), ("=OCT2BIN(\"1000\",3)", .num),45        ("=OCT2DEC(\"8\")", .num), ("=BITAND(-1,1)", .num), ("=BITAND(1.5,1)", .num), ("=BITOR(281474976710656,1)", .num), ("=BITLSHIFT(1,54)", .num), ("=BITRSHIFT(1,-54)", .num),46        ("=DELTA(\"a\",1)", .value), ("=GESTEP(\"a\")", .value), ("=CONVERT(1,\"m\",\"g\")", .na), ("=CONVERT(1,\"xyz\",\"m\")", .na), ("=CONVERT(1,\"kft\",\"m\")", .na),47        ("=CONVERT(\"a\",\"m\",\"ft\")", .value), ("=COMPLEX(1,2,\"k\")", .value), ("=IMABS(\"abc\")", .num), ("=IMSUM(\"1+i\",\"1+j\")", .value), ("=IMSUB(\"2j\",\"1-2i\")", .value),48        ("=IMDIV(\"1+i\",0)", .num), ("=IMLN(0)", .num), ("=IMARGUMENT(0)", .div0), ("=IMPOWER(0,-1)", .num), ("=IMABS(TRUE)", .value),49        ("=BESSELJ(1,-1)", .num), ("=BESSELY(0,1)", .num), ("=BESSELY(-1,1)", .num), ("=BESSELK(0,1)", .num), ("=BESSELI(1,-1)", .num), ("=BESSELJ(\"x\",1)", .value),50        ("=ROUND(\"abc\",1)", .value), ("=ROUND(1/0,1)", .div0), ("=SIN(NA())", .na), ("=SUMSQ(1,NA())", .na), ("=NORM.DIST(\"a\",0,1,TRUE)", .value),51    ]5253    @Test func excelErrorCodes() {54        let b = Book()55        var failures = [String]()56        for (f, e) in Self.cases {57            let v = b.eval(f).scalar58            if v != .error(e) { failures.append("\(f) expected \(e.rawValue) got \(v)") }59        }60        #expect(failures.isEmpty, Comment(rawValue: failures.joined(separator: "\n")))61    }6263    @Test func errorsInRangesPropagate() {64        let b = Book()65        b.fill("A1", [1, 2, 3])66        b.set("A4", "=1/0")67        b.set("B1", "x"); b.fill("B2", [5, 6]); b.set("B4", "y")68        for f in ["=PRODUCT(A1:A4)", "=SUMSQ(A1:A4)", "=MEDIAN(A1:A4)", "=STDEV(A1:A4)", "=GCD(A1:A4)", "=SUMPRODUCT(A1:A4,A1:A4)", "=LARGE(A1:A4,1)",69                  "=CORREL(A1:A4,A1:A4)", "=SUMIF(B1:B4,\"y\",A1:A4)", "=AVERAGEIFS(A1:A4,B1:B4,\"y\")", "=GEOMEAN(A1:A4)", "=VARA(A1:A4)", "=SKEW(A1:A4)"] {70            #expect(b.eval(f) == .error(.div0), Comment(rawValue: f))71        }72        // an error that is not selected by the criterion does not matter; COUNTIF never propagates73        #expect(b.eval("=SUMIF(B1:B4,\"x\",A1:A4)") == .number(1))74        #expect(b.eval("=SUMIFS(A1:A4,B1:B4,\">4\")") == .number(5))75        #expect(b.eval("=COUNTIF(A1:A4,\">1\")") == .number(2))76        #expect(b.eval("=COUNTIF(A1:A4,\"#DIV/0!\")") == .number(1))77        #expect(b.eval("=COUNTIF(A1:A4,\"<>2\")") == .number(3))78        #expect(b.eval("=AGGREGATE(9,6,A1:A4)") == .number(6))79        #expect(b.eval("=AGGREGATE(9,4,A1:A4)") == .error(.div0))80        #expect(b.eval("=AGGREGATE(2,4,A1:A4)") == .number(3))81        #expect(b.eval("=AGGREGATE(3,4,A1:A4)") == .number(4))82        #expect(b.eval("=AGGREGATE(3,6,A1:A4)") == .number(3))83        #expect(b.eval("=AGGREGATE(14,6,A1:A4,1)") == .number(3))84    }85}8687@Suite struct CoercionTests {88    /// Logical values and numeric text typed directly as arguments are counted; inside references89    /// they are ignored (or, for the "A" functions, counted as 1/0 and 0).90    @Test func directArgumentsVersusReferences() {91        let b = Book()92        b.set("A1", value: .number(4)); b.set("A2", value: .bool(true)); b.set("A3", value: .text("7")); b.set("A4", value: .number(10)); b.set("A5", value: .bool(false))93        #expect(b.eval("=PRODUCT(TRUE,\"3\",2)") == .number(6))94        #expect(b.eval("=MEDIAN(TRUE,\"4\",9,2,2)") == .number(2))95        #expect(b.eval("=SUMSQ(TRUE,\"2\")") == .number(5))96        #expect(b.eval("=GCD(TRUE,5)") == .number(1))97        #expect(close(b.num("=STDEV(TRUE,\"4\",9,2,2)"), 3.2093613071762426))98        #expect(close(b.num("=STDEVA(TRUE,\"4\",9,2,2)"), 3.2093613071762426))99        #expect(b.eval("=PRODUCT(A1:A5)") == .number(40))100        #expect(b.eval("=MEDIAN(A1:A5)") == .number(7))101        #expect(b.eval("=SUMSQ(A1:A5)") == .number(116))102        #expect(b.eval("=MAXA(A1:A5)") == .number(10))103        #expect(b.eval("=MINA(A1:A5)") == .number(0))104        #expect(b.eval("=MINA(A1:A4)") == .number(0))        // the text "7" counts as 0105        #expect(b.eval("=AVERAGEA(A1:A5)") == .number(3))     // (4 + 1 + 0 + 10 + 0) / 5106        #expect(close(b.num("=VARA(A1:A5)"), 18))107        #expect(close(b.num("=VARPA(A1:A5)"), 14.4))108        #expect(close(b.num("=STDEVPA(A1:A5)"), 14.4.squareRoot()))109        #expect(close(b.num("=VAR(A1:A5)"), 18))110        #expect(b.eval("=SUMPRODUCT(A1:A5,A1:A5)") == .number(116))   // logical values count as 0111        #expect(b.eval("=SUMX2PY2(A1:A5,A1:A5)") == .number(232))      // pairs with non-numbers are skipped112        #expect(b.eval("=SUMXMY2(A1:A5,{1;1;1;1;1})") == .number(90))113        #expect(b.eval("=LARGE(A1:A5,2)") == .number(4))114        #expect(b.eval("=PRODUCT(B1:B5)") == .number(0))               // nothing to multiply115        #expect(b.eval("=SQRT(TRUE)") == .number(1))116        #expect(b.eval("=SQRT(\"16\")") == .number(4))117        #expect(b.eval("=ROUND(\"2.5\",0)") == .number(3))118        #expect(b.eval("=ABS(B9)+ROUND(B9,2)") == .number(0))           // empty cells are 0119    }120121    @Test func logicalValuesInInformationFunctions() {122        let b = Book()123        b.set("A1", value: .bool(true)); b.set("A2", value: .text("")); b.set("A3", "=1/0"); b.set("A4", "=NA()"); b.set("A5", value: .number(3)); b.set("A6", value: .text("abc"))124        #expect(b.eval("=ISNUMBER(A1)") == .bool(false))125        #expect(b.eval("=ISNUMBER(TRUE)") == .bool(false))126        #expect(b.eval("=ISLOGICAL(A1)") == .bool(true))127        #expect(b.eval("=TYPE(A1)") == .number(4))128        #expect(b.eval("=TYPE(TRUE)") == .number(4))129        #expect(b.eval("=TYPE(A5)") == .number(1))130        #expect(b.eval("=TYPE(A6)") == .number(2))131        #expect(b.eval("=TYPE(A3)") == .number(16))132        #expect(b.eval("=TYPE(A5:A6)") == .number(64))133        #expect(b.eval("=TYPE({1,2})") == .number(64))134        #expect(b.eval("=TYPE(B7)") == .number(1))135        #expect(b.eval("=N(A1)") == .number(1))136        #expect(b.eval("=N(A6)") == .number(0))137        #expect(b.eval("=N(\"7\")") == .number(0))138        #expect(b.eval("=N(A3)") == .error(.div0))139        #expect(b.eval("=ISBLANK(B7)") == .bool(true))140        #expect(b.eval("=ISBLANK(A2)") == .bool(false))141        #expect(b.eval("=ISTEXT(A2)") == .bool(true))142        #expect(b.eval("=ISNONTEXT(B7)") == .bool(true))143        #expect(b.eval("=ISERROR(A3)") == .bool(true))144        #expect(b.eval("=ISERR(A3)") == .bool(true))145        #expect(b.eval("=ISERR(A4)") == .bool(false))146        #expect(b.eval("=ISNA(A4)") == .bool(true))147        #expect(b.eval("=ISNA(A3)") == .bool(false))148        #expect(b.eval("=ERROR.TYPE(A3)") == .number(2))149        #expect(b.eval("=ERROR.TYPE(A4)") == .number(7))150        #expect(b.eval("=ERROR.TYPE(SQRT(-1))") == .number(6))151        #expect(b.eval("=ERROR.TYPE(1+\"x\")") == .number(3))152        #expect(b.eval("=ISODD(B7)") == .bool(false))153        #expect(b.eval("=ISEVEN(-2.9)") == .bool(true))154        #expect(b.eval("=ISODD(\"3\")") == .bool(true))155        // element-wise over arrays156        #expect(b.eval("=SUM(--ISNUMBER(A1:A6))") == .number(1))157        #expect(b.eval("=SUM(--ISERROR(A1:A6))") == .number(2))158    }159160    @Test func arraysAreLiftedElementWise() {161        let b = Book()162        #expect(b.eval("=ROUND({1.25,2.35;3.45,-4.55},1)") == .array(ArrayValue([[.number(1.3), .number(2.4)], [.number(3.5), .number(-4.6)]])))163        #expect(b.eval("=SUM(MOD({7,8,9},{2;5}))") == .number(1 + 0 + 1 + 2 + 3 + 4))164        #expect(b.eval("=SUM(BIN2DEC({\"11\",\"101\"}))") == .number(8))165        #expect(b.eval("=SUM(COUNTIF({1,2,3,4},{\">1\",\">3\"}))") == .number(4))166        #expect(b.eval("=SUM(LARGE({5,1,9,3},{1,2}))") == .number(14))167        #expect(b.eval("=SUM(RANK({1,9},{5,1,9,3}))") == .number(5))168        #expect(close(b.num("=SUM(NORM.S.DIST({-1,1},TRUE))"), 1))169        #expect(b.eval("=INDEX(SQRT({4,-1,9}),2)") == .error(.num))170        b.fill("A1", [1.004, 2.005, 3.006])171        b.set("B1", "=ROUND(A1:A3,2)")172        #expect(b.get("B1") == .number(1))173        #expect(b.get("B2") == .number(2.01))174        #expect(b.get("B3") == .number(3.01))175    }176}177178@Suite struct CriteriaTests {179    @Test func matcher() {180        func m(_ c: Value, _ v: Value) -> Bool { Criteria(c).matches(v) }181        // numbers182        #expect(m(.number(5), .number(5)))183        #expect(m(.number(5), .text("5")))                     // numeric text equals the number184        #expect(!m(.number(5), .bool(true)))185        #expect(m(.text(">5"), .number(5.1)) && !m(.text(">5"), .number(5)) && !m(.text(">5"), .text("9")))186        #expect(m(.text(">=5"), .number(5)) && m(.text("<=5"), .number(5)) && m(.text("<5"), .number(-1)))187        #expect(m(.text("<>5"), .number(4)) && !m(.text("<>5"), .number(5)) && m(.text("<>5"), .text("x")) && m(.text("<>5"), .blank))188        #expect(m(.text("=5"), .number(5)) && m(.text("5"), .number(5)) && m(.text(" 5 "), .number(5)))189        #expect(m(.text("50%"), .number(0.5)) && m(.text(">1e2"), .number(101)) && m(.text("=-3"), .number(-3)))190        #expect(!m(.text("<5"), .blank) && !m(.text("=0"), .blank) && !m(.number(0), .blank))191        #expect(m(.blank, .number(0)))                           // an empty criteria cell means 0192        // dates are serial numbers193        #expect(m(.text(">=2024-03-01"), .number(45352)) && !m(.text(">2024-03-01"), .number(45352)))194        #expect(m(.text("3/1/2024"), .number(45352)) && m(.text("<3/2/2024"), .number(45352)))195        // text and wildcards196        #expect(m(.text("apple"), .text("APPLE")) && !m(.text("apple"), .text("apple pie")))197        #expect(m(.text("a*"), .text("Apple pie")) && m(.text("*pie"), .text("apple PIE")) && !m(.text("a*"), .text("banana")))198        #expect(m(.text("?at"), .text("cat")) && !m(.text("?at"), .text("at")) && !m(.text("?at"), .text("goat")))199        #expect(m(.text("a~*b"), .text("a*b")) && !m(.text("a~*b"), .text("axb")) && m(.text("a~?b"), .text("a?b")) && m(.text("a~~b"), .text("a~b")))200        #expect(m(.text("*"), .text("")) && m(.text("*"), .text("x")) && !m(.text("*"), .number(1)) && !m(.text("*"), .blank))201        #expect(m(.text("<>a*"), .text("banana")) && !m(.text("<>a*"), .text("apple")) && m(.text("<>a*"), .number(1)) && m(.text("<>a*"), .blank))202        #expect(m(.text(">b"), .text("cherry")) && !m(.text(">b"), .text("apple")) && !m(.text(">b"), .number(5)))203        #expect(m(.text("*a*b*c*"), .text("xxaxxbxxcxx")) && !m(.text("*a*b*c*"), .text("xxaxxcxxbxx")))204        // blanks205        #expect(m(.text(""), .blank) && m(.text(""), .text("")) && !m(.text(""), .number(0)) && !m(.text(""), .text("x")))206        #expect(m(.text("="), .blank) && !m(.text("="), .text("")))207        #expect(m(.text("<>"), .number(0)) && m(.text("<>"), .text("")) && !m(.text("<>"), .blank))208        // logical values and errors209        #expect(m(.bool(true), .bool(true)) && !m(.bool(true), .number(1)) && m(.text("TRUE"), .bool(true)) && m(.text("=false"), .bool(false)))210        #expect(m(.text("<>TRUE"), .bool(false)) && m(.text("<>TRUE"), .number(1)) && !m(.text("<>TRUE"), .bool(true)))211        #expect(m(.text("#N/A"), .error(.na)) && !m(.text("#N/A"), .error(.div0)) && m(.error(.div0), .error(.div0)) && !m(.text(">0"), .error(.na)))212        #expect(Criteria(.text("")).matchesBlank && Criteria(.text("<>x")).matchesBlank && !Criteria(.text("x")).matchesBlank && !Criteria(.number(0)).matchesBlank)213        // database flavour: a text criterion matches as a prefix, "=text" exactly214        #expect(Criteria(.text("Dav"), textPrefixMatch: true).matches(.text("David")))215        #expect(!Criteria(.text("Dav"), textPrefixMatch: false).matches(.text("David")))216        #expect(Criteria(.text("=Dave"), textPrefixMatch: true).matches(.text("dave")))217    }218219    @Test func logicalCriteriaInRanges() {220        let b = Book()221        b.set("A1", value: .bool(true)); b.set("A2", value: .bool(false)); b.set("A3", value: .number(1)); b.set("A4", value: .text("TRUE")); b.set("A5", value: .bool(true))222        b.fill("B1", [1, 2, 4, 8, 16])223        #expect(b.eval("=COUNTIF(A1:A5,TRUE)") == .number(2))224        #expect(b.eval("=COUNTIF(A1:A5,\"TRUE\")") == .number(2))225        #expect(b.eval("=COUNTIF(A1:A5,FALSE)") == .number(1))226        #expect(b.eval("=COUNTIF(A1:A5,1)") == .number(1))227        #expect(b.eval("=SUMIF(A1:A5,TRUE,B1:B5)") == .number(17))228        #expect(b.eval("=COUNTIF(A1:A5,\"<>TRUE\")") == .number(3))229        #expect(b.eval("=COUNTIF(A1:A5,\">0\")") == .number(1))230    }231232    @Test func blanksAndEmptyText() {233        let b = Book()234        b.set("A1", value: .number(1)); b.set("A2", "=\"\""); b.set("A4", value: .text("x")); b.set("A5", value: .number(0))235        #expect(b.eval("=COUNTBLANK(A1:A6)") == .number(3))      // A2 (empty text), A3, A6236        #expect(b.eval("=COUNTIF(A1:A6,\"\")") == .number(3))237        #expect(b.eval("=COUNTIF(A1:A6,\"=\")") == .number(2))    // truly empty cells only238        #expect(b.eval("=COUNTIF(A1:A6,\"<>\")") == .number(4))239        #expect(b.eval("=COUNTIF(A1:A6,\"<>x\")") == .number(5))240        #expect(b.eval("=COUNTIF(A1:A6,0)") == .number(1))241        #expect(b.eval("=COUNTIFS(A1:A6,\"\",A1:A6,\"<>x\")") == .number(3))242        #expect(b.eval("=COUNTIFS(A1:A6,\"<>x\",A1:A6,\"<>1\")") == .number(4))243        b.fill("B1", [10, 20, 30, 40, 50, 60])244        #expect(b.eval("=SUMIF(A1:A6,\"\",B1:B6)") == .number(110))245        #expect(b.eval("=SUMIFS(B1:B6,A1:A6,\"<>x\",A1:A6,\"<>1\")") == .number(160))246        #expect(b.eval("=MAXIFS(B1:B6,A1:A6,\"\")") == .number(60))247        #expect(b.eval("=MINIFS(B1:B6,A1:A6,\"zzz\")") == .number(0))248    }249250    @Test func sumRangeIsAnchoredAtItsTopLeftCell() {251        let b = Book()252        b.fill("A1", [1, 2, 3, 4]); b.fill("C3", [10, 20, 30, 40])253        #expect(b.eval("=SUMIF(A1:A4,\">2\",C3)") == .number(70))254        #expect(b.eval("=SUMIF(A1:A4,\">2\",C3:C4)") == .number(70))255        #expect(b.eval("=AVERAGEIF(A1:A4,\"<3\",C3)") == .number(15))256    }257258    @Test func wholeColumnReferencesStayFast() {259        let b = Book()260        let n = 60_000261        b.engine.transaction("fill") {262            for i in 0..<n {263                b.engine.set(sheet: 0, row: i, col: 0, .value(.number(Double(i % 10))))264                b.engine.set(sheet: 0, row: i, col: 1, .value(.number(2)))265                b.engine.set(sheet: 0, row: i, col: 2, .value(.text(i % 3 == 0 ? "red" : "blue")))266            }267        }268        b.engine.recalculate()269        let start = Date()270        #expect(b.eval("=COUNTIF(A:A,\">=5\")") == .number(Double(n / 2)))271        #expect(b.eval("=SUMIF(A:A,\">=5\",B:B)") == .number(Double(n)))272        #expect(b.eval("=COUNTIFS(A:A,0,C:C,\"red\")") == .number(2000))273        #expect(b.eval("=SUMIFS(B:B,A:A,\"<2\",C:C,\"b*\")") == .number(Double(2 * 8000)))274        #expect(b.eval("=AVERAGEIFS(A:A,C:C,\"red\")") == .number(4.5))275        #expect(b.eval("=COUNTIF(A:A,\"\")") == .number(Double(Limits.maxRows - n)))276        #expect(b.eval("=COUNTBLANK(A:B)") == .number(Double(2 * (Limits.maxRows - n))))277        #expect(b.eval("=COUNTIF(A:C,\"<>red\")") == .number(Double(3 * Limits.maxRows - n / 3)))278        #expect(b.eval("=COUNTIFS(C:C,\"<>red\",A:A,\"<>0\")") == .number(Double(Limits.maxRows - 24000)))279        #expect(b.eval("=SUMPRODUCT(A:A,B:B)") == .number(Double(n / 10 * 45 * 2)))280        #expect(b.eval("=MEDIAN(A:A)") == .number(4.5))281        #expect(b.eval("=SUBTOTAL(9,B:B)") == .number(Double(2 * n)))282        #expect(b.eval("=MAXIFS(A:A,C:C,\"blue\")") == .number(9))283        #expect(Date().timeIntervalSince(start) < 20)284    }285}286287@Suite struct SubtotalTests {288    @Test func hiddenAndFilteredRows() {289        let b = Book()290        b.fill("A1", [10, 20, 30, 40, 50, 60])291        b.engine.editMeta(sheet: 0) { meta in292            meta.hiddenRows.insert(1)          // row 2, hidden by hand293            meta.filteredRows.insert(3)        // row 4, hidden by a filter294        }295        #expect(b.eval("=SUM(A1:A6)") == .number(210))296        #expect(b.eval("=SUBTOTAL(9,A1:A6)") == .number(170))       // filtered rows are always ignored297        #expect(b.eval("=SUBTOTAL(109,A1:A6)") == .number(150))     // …and manually hidden ones too298        #expect(b.eval("=SUBTOTAL(1,A1:A6)") == .number(34))299        #expect(b.eval("=SUBTOTAL(101,A1:A6)") == .number(37.5))300        #expect(b.eval("=SUBTOTAL(2,A1:A6)") == .number(5))301        #expect(b.eval("=SUBTOTAL(103,A1:A6)") == .number(4))302        #expect(b.eval("=SUBTOTAL(4,A1:A6)") == .number(60))303        #expect(b.eval("=SUBTOTAL(105,A1:A6)") == .number(10))304        #expect(b.eval("=SUBTOTAL(106,A1:A3)") == .number(300))305        #expect(b.eval("=AGGREGATE(9,4,A1:A6)") == .number(210))306        #expect(b.eval("=AGGREGATE(9,5,A1:A6)") == .number(150))307        #expect(b.eval("=AGGREGATE(12,5,A1:A6)") == .number(40))308        #expect(b.eval("=AGGREGATE(14,5,A1:A6,2)") == .number(50))309        #expect(b.eval("=AGGREGATE(15,4,A1:A6,2)") == .number(20))310    }311312    @Test func nestedSubtotalsAreIgnored() {313        let b = Book()314        b.fill("A1", [1, 2, 3])315        b.set("A4", "=SUBTOTAL(9,A1:A3)")316        b.fill("A5", [10, 20])317        b.set("A7", "=SUBTOTAL(9,A5:A6)")318        b.set("A8", "=SUM(A1:A3)")319        #expect(b.eval("=SUBTOTAL(9,A1:A7)") == .number(36))320        #expect(b.eval("=SUBTOTAL(9,A1:A8)") == .number(42))        // a plain SUM is not a subtotal321        #expect(b.eval("=AGGREGATE(9,0,A1:A7)") == .number(36))322        #expect(b.eval("=AGGREGATE(9,4,A1:A7)") == .number(72))323        #expect(b.eval("=SUBTOTAL(3,A1:A7)") == .number(5))324        b.set("A9", "=AGGREGATE(9,4,A1:A3)")325        #expect(b.eval("=SUBTOTAL(9,A9)") == .number(0))326    }327328    @Test func dependsOnPendingFormulas() {329        // SUBTOTAL over formulas that are still dirty must see their values330        let b = Book()331        b.set("A1", "5")332        for r in 2...40 { b.set("A\(r)", "=A\(r - 1)+1") }333        b.set("B1", "=SUBTOTAL(9,A1:A40)")334        b.set("B2", "=SUMIF(A1:A40,\">40\")")335        b.set("B3", "=MEDIAN(A1:A40)")336        #expect(b.get("B1") == .number(Double((5...44).reduce(0, +))))337        #expect(b.get("B2") == .number(Double((41...44).reduce(0, +))))338        #expect(b.get("B3") == .number(24.5))339        b.set("A1", "105")340        #expect(b.get("B1") == .number(Double((105...144).reduce(0, +))))341        #expect(b.get("B3") == .number(124.5))342    }343}344345@Suite struct InformationTests {346    @Test func referencesAndFormulas() {347        let b = Book(sheets: ["Main", "Other Sheet"])348        b.set("A1", "=SUM(B1:B3)*2"); b.set("A2", "42"); b.set("A3", "hello")349        #expect(b.eval("=ISFORMULA(A1)") == .bool(true))350        #expect(b.eval("=ISFORMULA(A2)") == .bool(false))351        #expect(b.eval("=FORMULATEXT(A1)") == .text("=SUM(B1:B3)*2"))352        #expect(b.eval("=FORMULATEXT(A2)") == .error(.na))353        #expect(b.eval("=ISREF(A1)") == .bool(true))354        #expect(b.eval("=ISREF(A1:B2)") == .bool(true))355        #expect(b.eval("=ISREF(5)") == .bool(false))356        #expect(b.eval("=ISREF(\"A1\")") == .bool(false))357        #expect(b.eval("=SHEET()") == .number(1))358        #expect(b.eval("=SHEET('Other Sheet'!A1)") == .number(2))359        #expect(b.eval("=SHEET(\"other sheet\")") == .number(2))360        #expect(b.eval("=SHEETS()") == .number(2))361        #expect(b.eval("=SHEETS(A1:B2)") == .number(1))362        #expect(b.eval("=CELL(\"address\",C7)") == .text("$C$7"))363        #expect(b.eval("=CELL(\"address\",'Other Sheet'!C7)") == .text("'Other Sheet'!$C$7"))364        #expect(b.eval("=CELL(\"row\",C7)") == .number(7))365        #expect(b.eval("=CELL(\"col\",C7)") == .number(3))366        #expect(b.eval("=CELL(\"contents\",A2)") == .number(42))367        #expect(b.eval("=CELL(\"contents\",A3)") == .text("hello"))368        #expect(b.eval("=CELL(\"type\",A2)") == .text("v"))369        #expect(b.eval("=CELL(\"type\",A3)") == .text("l"))370        #expect(b.eval("=CELL(\"type\",Z99)") == .text("b"))371        #expect(b.eval("=CELL(\"ROW\",A5:B9)") == .number(5))372        b.set("D4", "=CELL(\"address\")")373        #expect(b.get("D4") == .text("$D$4"))374        #expect(b.eval("=INFO(\"system\")") == .text("mac"))375        #expect(b.eval("=INFO(\"recalc\")") == .text("Automatic"))376        #expect(b.eval("=INFO(\"numfile\")") == .number(2))377    }378379    @Test func isOmitted() {380        let b = Book()381        #expect(b.eval("=LAMBDA(x,y,ISOMITTED(y))(1)") == .bool(true))382        #expect(b.eval("=LAMBDA(x,y,ISOMITTED(y))(1,2)") == .bool(false))383        #expect(b.eval("=LAMBDA(x,y,IF(ISOMITTED(y),x,x+y))(5,)") == .number(5))384        #expect(b.eval("=ISOMITTED(1)") == .bool(false))385    }386}387388@Suite struct MiscellaneousTests {389    @Test func randomFunctions() {390        let b = Book()391        for _ in 0..<200 {392            let v = b.num("=RANDBETWEEN(-3,3)")393            #expect(v >= -3 && v <= 3 && v == v.rounded())394        }395        #expect(b.eval("=RANDBETWEEN(2.2,2.9)") == .error(.num))396        #expect(b.eval("=RANDBETWEEN(2.5,3.5)") == .number(3))397        guard case .array(let a) = b.eval("=RANDARRAY(4,3,10,20,TRUE)") else { Issue.record("RANDARRAY did not return an array"); return }398        #expect(a.rows == 4 && a.cols == 3)399        #expect(a.items.allSatisfy { ($0.numberValue ?? -1) >= 10 && ($0.numberValue ?? 99) <= 20 && $0.numberValue == $0.numberValue?.rounded() })400        guard case .array(let u) = b.eval("=RANDARRAY(50,2)") else { Issue.record("RANDARRAY did not return an array"); return }401        #expect(u.items.allSatisfy { ($0.numberValue ?? -1) >= 0 && ($0.numberValue ?? 2) < 1 })402        #expect(Set(u.items.compactMap(\.numberValue)).count > 90)403        let single = b.num("=RANDARRAY()")404        #expect(single >= 0 && single < 1)405        #expect(b.eval("=RANDARRAY(2,2,5,1)") == .error(.value))406        #expect(b.eval("=RANDARRAY(0)") == .error(.value))407        #expect(b.eval("=RANDARRAY(2,2,1.5,3,TRUE)") == .error(.value))408        for name in ["RANDBETWEEN", "RANDARRAY", "INFO", "CELL"] { #expect(FunctionRegistry.standard.lookup(name)?.isVolatile == true) }409        // volatile cells are recalculated410        b.set("A1", "=RANDARRAY(3,1)")411        let first = [b.get("A1"), b.get("A2"), b.get("A3")]412        b.set("C1", "1")413        #expect([b.get("A1"), b.get("A2"), b.get("A3")] != first)414    }415416    @Test func matrixFunctionsSpill() {417        let b = Book()418        b.set("A1", "=MUNIT(3)")419        #expect(b.get("B2") == .number(1) && b.get("C1") == .number(0) && b.get("C3") == .number(1))420        b.set("E1", "=MMULT({1,2;3,4},{5,6;7,8})")421        #expect(b.get("E1") == .number(19) && b.get("F1") == .number(22) && b.get("E2") == .number(43) && b.get("F2") == .number(50))422        b.set("H1", "=MINVERSE({4,7;2,6})")423        #expect(close(b.get("H1").numberValue ?? .nan, 0.6) && close(b.get("I1").numberValue ?? .nan, -0.7))424        #expect(close(b.get("H2").numberValue ?? .nan, -0.2) && close(b.get("I2").numberValue ?? .nan, 0.4))425        #expect(close(b.num("=MDETERM(MMULT({4,7;2,6},MINVERSE({4,7;2,6})))"), 1))426        b.set("K1", "=LINEST({3,5,7,9},{1,2,3,4},TRUE,TRUE)")427        #expect(close(b.get("K1").numberValue ?? .nan, 2) && close(b.get("L1").numberValue ?? .nan, 1))428        #expect(b.get("M3") == .number(0) || b.get("M3") == .blank)      // the block is 5 × 2429        #expect(b.get("L4") == .number(2))                                 // degrees of freedom430        #expect(b.get("K5").numberValue.map { close($0, 20) } == true)431        b.set("O1", "=FREQUENCY({1,2,2,3,9},{2,5})")432        #expect(b.get("O1") == .number(3) && b.get("O2") == .number(1) && b.get("O3") == .number(1))433        b.set("Q1", "=MODE.MULT({1,2,2,3,3,4})")434        #expect(b.get("Q1") == .number(2) && b.get("Q2") == .number(3))435    }436437    @Test func complexNumbersInRanges() {438        let b = Book()439        b.set("A1", value: .text("1+2i")); b.set("A2", value: .number(3)); b.set("A4", value: .text("-i")); b.set("B1", value: .text("2j"))440        #expect(b.eval("=IMSUM(A1:A4)") == .text("4+i"))441        #expect(b.eval("=IMPRODUCT(A1:A4)") == .text("6-3i"))442        #expect(b.eval("=IMSUM(A1:A4,\"1-i\",{\"i\",\"2\"})") == .text("7+i"))443        #expect(b.eval("=IMSUM(A1:B1)") == .error(.value))                // i and j cannot be mixed444        #expect(b.eval("=IMSUM(B1,\"1+j\")") == .text("1+3j"))445        #expect(b.eval("=IMSQRT(\"-4\")") == .text("1.22464679914735E-16+2i") || b.eval("=IMABS(IMSQRT(\"-4\"))") == .number(2))446        #expect(b.eval("=COMPLEX(1E-20,3)") == .text("1E-20+3i"))447        #expect(b.eval("=IMREAL(\"1.5e2+2e-1i\")") == .number(150))448        #expect(b.eval("=IMAGINARY(\"1.5e2+2e-1i\")") == .number(0.2))449        #expect(b.eval("=IMAGINARY(\"-j\")") == .number(-1))450        #expect(b.eval("=IMCONJUGATE(\"3-4j\")") == .text("3+4j"))451    }452453    @Test func databaseCriteria() {454        let b = Book()455        let rows: [[Value]] = [456            [.text("Name"), .text("Dept"), .text("Pay")],457            [.text("David"), .text("Eng"), .number(10)], [.text("Dave"), .text("Ops"), .number(20)], [.text("Eve"), .text("Eng"), .number(40)],458            [.text("Dana"), .text("Ops"), .text("n/a")], [.text("dav"), .text("Eng"), .number(80)],459        ]460        for (r, row) in rows.enumerated() { for (c, v) in row.enumerated() { b.engine.set(sheet: 0, row: r, col: c, .value(v)) } }461        b.set("E1", value: .text("Name")); b.set("F1", value: .text("Pay")); b.set("G1", value: .text("dept"))462        b.set("E2", value: .text("Dav"))463        #expect(b.eval("=DSUM(A1:C6,\"Pay\",E1:E2)") == .number(110))           // prefix match: David, Dave, dav464        #expect(b.eval("=DCOUNTA(A1:C6,\"Name\",E1:E2)") == .number(3))465        b.set("E2", value: .text("=Dav"))466        #expect(b.eval("=DSUM(A1:C6,3,E1:E2)") == .number(80))                    // exact match, case-insensitive467        b.set("E2", value: .text("D*a"))468        #expect(b.eval("=DCOUNTA(A1:C6,1,E1:E2)") == .number(1) || b.eval("=DCOUNTA(A1:C6,1,E1:E2)") == .number(4))469        b.set("E2", value: .text("<>Eve")); b.set("F2", value: .text(">=20"))470        #expect(b.eval("=DSUM(A1:C6,\"pay\",E1:F2)") == .number(100))471        #expect(b.eval("=DCOUNT(A1:C6,,E1:F2)") == .number(2))472        // OR across rows, a blank criteria row matches every record473        b.set("E2", value: .text("=Eve")); b.set("F2", value: .blank); b.set("G3", value: .text("Ops"))474        #expect(b.eval("=DSUM(A1:C6,\"Pay\",E1:G3)") == .number(60))475        #expect(b.eval("=DCOUNT(A1:C6,\"Pay\",E1:G3)") == .number(2))476        #expect(b.eval("=DCOUNTA(A1:C6,\"Pay\",E1:G3)") == .number(3))477        #expect(b.eval("=DMAX(A1:C6,\"Pay\",E1:G4)") == .number(80))478        #expect(b.eval("=DGET(A1:C6,\"Dept\",E1:E2)") == .text("Eng"))479        #expect(b.eval("=DGET(A1:C6,\"Pay\",G1:G3)") == .error(.num))480        b.set("G3", value: .text("zzz")); b.set("E2", value: .text("zzz"))481        #expect(b.eval("=DGET(A1:C6,\"Pay\",E1:G3)") == .error(.value))482        #expect(b.eval("=DSUM(A1:C6,\"Pay\",E1:G3)") == .number(0))483        #expect(b.eval("=DAVERAGE(A1:C6,\"Pay\",E1:G3)") == .error(.div0))484        #expect(b.eval("=DSUM(A1:C6,\"Nope\",E1:E2)") == .error(.value))485        #expect(b.eval("=DSUM(A1:C6,4,E1:E2)") == .error(.value))486        #expect(b.eval("=DSUM(A1:C6,\"Pay\",E1:E1)") == .error(.value))487    }488489    @Test func frequencyAndSeries() {490        let b = Book()491        #expect(b.eval("=FREQUENCY({1,5,5,7,12},{10,5})") == .array(ArrayValue(column: [.number(1), .number(3), .number(1)])))   // unsorted bins keep their order492        #expect(b.eval("=SUM(FREQUENCY({1,\"a\",3},{2}))") == .number(2))493        #expect(close(b.num("=SERIESSUM(2,1,2,{1,1,1})"), 2 + 8 + 32))494        #expect(b.eval("=SERIESSUM(2,1,2,{1,\"a\"})") == .error(.value))495        b.fill("A1", [12, 18, 30])496        #expect(b.eval("=GCD(A1:A3)") == .number(6))497        #expect(b.eval("=LCM(A1:A3)") == .number(180))498        #expect(b.eval("=MULTINOMIAL(A1:A2)") == .number(86493225))499    }500501    @Test func linestDegenerateAndOrientation() {502        let b = Book()503        // regressors in rows (known_y's is a row)504        #expect(close(b.num("=INDEX(LINEST({5,9,7,15},{1,2,3,4;1,0,2,3}),1,3)"), b.num("=INDEX(LINEST({5;9;7;15},{1,1;2,0;3,2;4,3}),1,3)")))505        #expect(close(b.num("=INDEX(LINEST({5,9,7,15},{1,2,3,4;1,0,2,3}),1,1)"), b.num("=INDEX(LINEST({5;9;7;15},{1,1;2,0;3,2;4,3}),1,1)")))506        // without a constant the intercept is 0 and its standard error is #N/A507        #expect(b.eval("=INDEX(LINEST({2,4,6},{1,2,3},FALSE,TRUE),1,2)") == .number(0))508        #expect(b.eval("=INDEX(LINEST({2,4,6},{1,2,3},FALSE,TRUE),2,2)") == .error(.na))509        #expect(b.eval("=INDEX(LINEST({2,4,6},{1,2,3},TRUE,TRUE),3,3)") == .error(.ref))510        #expect(b.eval("=LINEST({1,2,3},{1,2})") == .error(.ref))511        #expect(b.eval("=LINEST({1,\"a\",3},{1,2,3})") == .error(.value))512        #expect(b.eval("=LOGEST({1,-2,3},{1,2,3})") == .error(.num))513        #expect(close(b.num("=INDEX(TREND({2,4,6}),1,3)"), 6))514        #expect(close(b.num("=INDEX(GROWTH({2,4,8},{1,2,3},{4,5}),1,2)"), 32, rel: 1e-12))515    }516517    @Test func roundingPreservesLargeAndTinyValues() {518        let b = Book()519        #expect(b.eval("=ROUND(123456789012345,2)") == .number(123456789012345))520        #expect(b.eval("=ROUND(1E+300,2)") == .number(1e300))521        #expect(b.eval("=ROUND(1E-300,2)") == .number(0))522        #expect(b.eval("=ROUNDUP(1E-300,2)") == .number(0.01))523        #expect(b.eval("=ROUNDUP(-1E-300,0)") == .number(-1))524        #expect(b.eval("=ROUNDDOWN(-0.999999,0)") == .number(0))525        #expect(b.eval("=ROUND(0.1+0.2,15)") == .number(0.3))526        #expect(b.eval("=ROUND(1234.5678,-2)") == .number(1200))527        #expect(b.eval("=ROUND(1250,-2)") == .number(1300))528        #expect(b.eval("=ROUND(-1250,-2)") == .number(-1300))529        #expect(b.eval("=ROUND(5,-1)") == .number(10))530        #expect(b.eval("=ROUND(4.9,-1)") == .number(0))531        #expect(b.eval("=ROUND(0.5,0)") == .number(1))532        #expect(b.eval("=ROUND(1.5,0)") == .number(2))533        #expect(b.eval("=ROUND(0.49999999999999994,0)") == .number(1))       // reads 0.500000000000000 at 15 digits534        #expect(b.eval("=ROUND(0.4999999999999,0)") == .number(0))535        #expect(b.eval("=ROUND(2.4999999999999996,0)") == .number(3))        // 15 significant digits read 2.50000000000000536        #expect(b.eval("=CEILING(1.1,0.1)") == .number(1.1))537        #expect(b.eval("=FLOOR(0.7,0.1)") == .number(0.7000000000000001) || b.eval("=FLOOR(0.7,0.1)") == .number(0.7))538        #expect(b.eval("=MROUND(6.05,0.1)") == .number(6.1000000000000005) || b.eval("=MROUND(6.05,0.1)") == .number(6.1))539        #expect(b.eval("=INT(-0.5)") == .number(-1))540        #expect(b.eval("=EVEN(0)") == .number(0))541        #expect(b.eval("=ODD(0)") == .number(1))542    }543544    /// Writes the registry as TSV for Generators/coverage.py (which builds the table of COMPATIBILITY.md).545    @Test func dumpRegistry() throws {546        let reg = FunctionRegistry()547        NumericFunctions.register(into: reg)548        let text = reg.all.map { "\($0.name)\t\($0.category.rawValue)\t\($0.signature)\t\($0.summary)" }.joined(separator: "\n") + "\n"549        try text.write(toFile: NSTemporaryDirectory() + "functions_numeric_registry.tsv", atomically: true, encoding: .utf8)550        try? text.write(toFile: "/tmp/functions_numeric_registry.tsv", atomically: true, encoding: .utf8)551    }552553    @Test func registryMetadata() {554        _ = registered555        let reg = FunctionRegistry()556        NumericFunctions.register(into: reg)557        #expect(reg.count >= 290)558        for spec in reg.all {559            #expect(spec.signature.hasPrefix(spec.name + "("), Comment(rawValue: "signature of \(spec.name): \(spec.signature)"))560            #expect(spec.summary.count > 10 && spec.summary.hasSuffix("."), Comment(rawValue: "summary of \(spec.name): \(spec.summary)"))561            #expect(spec.minArgs <= spec.maxArgs)562        }563        // nothing owned by SheetCore is replaced564        for name in ["SUM", "AVERAGE", "MIN", "MAX", "COUNT", "COUNTA", "AND", "OR", "XOR", "NOT", "TRUE", "FALSE", "NA", "ABS", "ROW", "COLUMN", "ROWS", "COLUMNS",565                     "INDEX", "OFFSET", "INDIRECT", "NOW", "TODAY", "RAND", "IF", "IFS", "IFERROR", "IFNA", "SWITCH", "CHOOSE", "LET", "LAMBDA", "T"] {566            #expect(reg.lookup(name) == nil, Comment(rawValue: name))567        }568        #expect(reg.lookup("SIN")?.category == .trig)569        #expect(reg.lookup("ROUND")?.category == .math)570        #expect(reg.lookup("NORM.DIST")?.category == .statistical)571        #expect(reg.lookup("DSUM")?.category == .database)572        #expect(reg.lookup("CONVERT")?.category == .engineering)573        #expect(reg.lookup("ISBLANK")?.category == .information)574    }575}576
Files main
Menu

Theme