Repository Public
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