SPB Git

Repository Public

ExcelDocumentationTests.swift 19.5 KB 215 lines swift

1import Foundation2import Testing3import SheetCore4@testable import FunctionsNumeric56/// Worked examples published in Microsoft's Excel function reference (support.microsoft.com).7/// They anchor the library to Excel itself where LibreOffice or scipy could only vouch for the maths.8/// Values are given with the number of digits the documentation prints.9@Suite struct ExcelDocumentationTests {10    /// (formula, expected, absolute tolerance)11    static let numeric: [(String, Double, Double)] = [12        // rounding13        ("=ROUND(2.15,1)", 2.2, 0), ("=ROUND(2.149,1)", 2.1, 0), ("=ROUND(-1.475,2)", -1.48, 0), ("=ROUND(21.5,-1)", 20, 0),14        ("=ROUND(626.3,-3)", 1000, 0), ("=ROUND(1.98,-1)", 0, 0), ("=ROUND(-50.55,-2)", -100, 0),15        ("=ROUND(2.5,0)", 3, 0), ("=ROUND(-2.5,0)", -3, 0), ("=ROUND(1.005,2)", 1.01, 0), ("=ROUND(0.285,2)", 0.29, 0), ("=ROUND(2.675,2)", 2.68, 0),16        ("=ROUNDUP(3.2,0)", 4, 0), ("=ROUNDUP(76.9,0)", 77, 0), ("=ROUNDUP(3.14159,3)", 3.142, 0), ("=ROUNDUP(-3.14159,1)", -3.2, 0), ("=ROUNDUP(31415.92654,-2)", 31500, 0),17        ("=ROUNDDOWN(3.2,0)", 3, 0), ("=ROUNDDOWN(76.9,0)", 76, 0), ("=ROUNDDOWN(3.14159,3)", 3.141, 0), ("=ROUNDDOWN(-3.14159,1)", -3.1, 0), ("=ROUNDDOWN(31415.92654,-2)", 31400, 0),18        ("=MROUND(10,3)", 9, 0), ("=MROUND(-10,-3)", -9, 0), ("=MROUND(1.3,0.2)", 1.4, 1e-15),19        ("=CEILING(2.5,1)", 3, 0), ("=CEILING(-2.5,-2)", -4, 0), ("=CEILING(-2.5,2)", -2, 0), ("=CEILING(1.5,0.1)", 1.5, 1e-15), ("=CEILING(0.234,0.01)", 0.24, 1e-15),20        ("=FLOOR(3.7,2)", 2, 0), ("=FLOOR(-2.5,-2)", -2, 0), ("=FLOOR(1.58,0.1)", 1.5, 1e-15), ("=FLOOR(0.234,0.01)", 0.23, 1e-15),21        ("=CEILING.MATH(24.3,5)", 25, 0), ("=CEILING.MATH(6.7)", 7, 0), ("=CEILING.MATH(-8.1,2)", -8, 0), ("=CEILING.MATH(-5.5,2,-1)", -6, 0),22        ("=FLOOR.MATH(24.3,5)", 20, 0), ("=FLOOR.MATH(6.7)", 6, 0), ("=FLOOR.MATH(-8.1,2)", -10, 0), ("=FLOOR.MATH(-5.5,2,-1)", -4, 0),23        ("=CEILING.PRECISE(4.3)", 5, 0), ("=CEILING.PRECISE(-4.3)", -4, 0), ("=CEILING.PRECISE(4.3,2)", 6, 0), ("=CEILING.PRECISE(4.3,-2)", 6, 0), ("=CEILING.PRECISE(-4.3,2)", -4, 0),24        ("=ISO.CEILING(4.3)", 5, 0), ("=ISO.CEILING(-4.3)", -4, 0), ("=ISO.CEILING(4.3,2)", 6, 0), ("=ISO.CEILING(4.3,-2)", 6, 0), ("=ISO.CEILING(-4.3,2)", -4, 0), ("=ISO.CEILING(-4.3,-2)", -4, 0),25        ("=FLOOR.PRECISE(-3.2,-1)", -4, 0), ("=FLOOR.PRECISE(3.2,1)", 3, 0), ("=FLOOR.PRECISE(-3.2,1)", -4, 0), ("=FLOOR.PRECISE(3.2,-1)", 3, 0), ("=FLOOR.PRECISE(3.2)", 3, 0),26        ("=EVEN(1.5)", 2, 0), ("=EVEN(3)", 4, 0), ("=EVEN(2)", 2, 0), ("=EVEN(-1)", -2, 0),27        ("=ODD(1.5)", 3, 0), ("=ODD(3)", 3, 0), ("=ODD(2)", 3, 0), ("=ODD(-1)", -1, 0), ("=ODD(-2)", -3, 0),28        ("=INT(8.9)", 8, 0), ("=INT(-8.9)", -9, 0), ("=TRUNC(8.9)", 8, 0), ("=TRUNC(-8.9)", -8, 0), ("=TRUNC(0.45)", 0, 0),29        ("=MOD(3,2)", 1, 0), ("=MOD(-3,2)", 1, 0), ("=MOD(3,-2)", -1, 0), ("=MOD(-3,-2)", -1, 0),30        ("=QUOTIENT(5,2)", 2, 0), ("=QUOTIENT(4.5,3.1)", 1, 0), ("=QUOTIENT(-10,3)", -3, 0),31        // math32        ("=COMBIN(8,2)", 28, 0), ("=COMBINA(4,3)", 20, 0), ("=COMBINA(10,3)", 220, 0), ("=PERMUT(100,3)", 970200, 0), ("=PERMUT(3,2)", 6, 0), ("=PERMUTATIONA(3,2)", 9, 0),33        ("=PERMUTATIONA(2,2)", 4, 0), ("=FACT(5)", 120, 0), ("=FACT(1.9)", 1, 0), ("=FACT(0)", 1, 0), ("=FACTDOUBLE(6)", 48, 0), ("=FACTDOUBLE(7)", 105, 0),34        ("=MULTINOMIAL(2,3,4)", 1260, 0), ("=GCD(5,2)", 1, 0), ("=GCD(24,36)", 12, 0), ("=GCD(7,1)", 1, 0), ("=GCD(5,0)", 5, 0), ("=LCM(5,2)", 10, 0), ("=LCM(24,36)", 72, 0),35        ("=SERIESSUM(0.785398163,0,2,{1,-0.5,0.041666667,-0.001388889})", 0.707103, 1e-6), ("=SQRTPI(1)", 1.772454, 1e-6), ("=SQRTPI(2)", 2.506628, 1e-6),36        ("=SUMX2MY2({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})", -55, 0), ("=SUMX2PY2({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})", 521, 0), ("=SUMXMY2({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})", 79, 0),37        ("=SUMPRODUCT({3,4;8,6;1,9},{2,7;6,7;5,3})", 156, 0), ("=SUMSQ(3,4)", 25, 0), ("=PRODUCT(5,15,30)", 2250, 0), ("=POWER(5,2)", 25, 0), ("=POWER(98.6,3.2)", 2401077.222, 1e-3),38        ("=POWER(4,5/4)", 5.656854249, 1e-9), ("=LOG(8,2)", 3, 0), ("=LOG(86,2.7182818)", 4.4543473, 1e-7), ("=LOG10(86)", 1.9345, 1e-4), ("=LN(86)", 4.4543473, 1e-7),39        ("=EXP(2)", 7.389056, 1e-6), ("=SIGN(-0.00001)", -1, 0), ("=ARABIC(\"LVII\")", 57, 0), ("=ARABIC(\"mcmxii\")", 1912, 0),40        ("=DECIMAL(\"FF\",16)", 255, 0), ("=DECIMAL(111,2)", 7, 0), ("=DECIMAL(\"zap\",36)", 45745, 0),41        ("=MDETERM({1,3,8,5;1,3,6,1;1,1,1,0;7,3,10,2})", 88, 1e-10), ("=MDETERM({3,6,1;1,1,0;3,10,2})", 1, 1e-12), ("=MDETERM({3,6;1,1})", -3, 1e-12),42        ("=ATAN2(1,1)", 0.785398163, 1e-9), ("=ATAN2(-1,-1)", -2.35619449, 1e-8), ("=ACOT(2)", 0.4636, 1e-4), ("=ACOTH(6)", 0.168, 1e-3), ("=COT(30)", -0.156, 1e-3),43        ("=COTH(2)", 1.037, 1e-3), ("=CSC(15)", 1.538, 1e-3), ("=CSCH(1.5)", 0.4696, 1e-4), ("=SEC(45)", 1.90359, 1e-5), ("=SECH(45)", 5.73e-20, 1e-22),44        ("=DEGREES(PI())", 180, 1e-13), ("=RADIANS(270)", 4.712389, 1e-6), ("=SINH(1)", 1.175201194, 1e-9), ("=ACOSH(10)", 2.9932228, 1e-7), ("=ATANH(0.76159416)", 1.00000001, 1e-8),45        // statistics46        ("=AVEDEV(4,5,6,7,5,4,3)", 1.020408, 1e-6), ("=DEVSQ(4,5,8,7,11,4,3)", 48, 1e-12), ("=GEOMEAN(4,5,8,7,11,4,3)", 5.476987, 1e-6), ("=HARMEAN(4,5,8,7,11,4,3)", 5.028376, 1e-6),47        ("=KURT(3,4,5,2,3,4,5,6,4,7)", -0.151799637, 1e-9), ("=SKEW(3,4,5,2,3,4,5,6,4,7)", 0.359543, 1e-6), ("=SKEW.P(3,4,5,2,3,4,5,6,4,7)", 0.303193, 1e-6),48        ("=TRIMMEAN({4,5,6,7,2,3,4,5,1,2,3},0.2)", 3.778, 1e-3), ("=LARGE({3,4,5,2,3,4,5,6,4,7},3)", 5, 0), ("=LARGE({3,4,5,2,3,4,5,6,4,7},7)", 4, 0),49        ("=SMALL({3,4,5,2,3,4,6,4,7},4)", 4, 0), ("=SMALL({1,4,8,3,7,12,54,8,23},2)", 3, 0), ("=MEDIAN(1,2,3,4,5)", 3, 0), ("=MEDIAN(1,2,3,4,5,6)", 3.5, 0),50        ("=MODE.SNGL(5.6,4,4,3,2,4)", 4, 0), ("=QUARTILE({1,2,4,7,8,9,10,12},1)", 3.5, 1e-12), ("=QUARTILE.EXC({6,7,15,36,39,40,41,42,43,47,49},1)", 15, 0),51        ("=QUARTILE.EXC({6,7,15,36,39,40,41,42,43,47,49},3)", 43, 0), ("=QUARTILE.INC({6,7,15,36,39,40,41,42,43,47,49},1)", 25.5, 0),52        ("=PERCENTILE({1,2,3,4},0.3)", 1.9, 1e-12), ("=PERCENTILE.EXC({1,2,3,6,6,6,7,8,9},0.25)", 2.5, 1e-12), ("=PERCENTILE.INC({1,3,2,4},0.3)", 1.9, 1e-12),53        ("=RANK.AVG(94,{89,88,92,101,94,97,95})", 4, 0), ("=RANK.EQ(7,{7,3.5,3.5,1,2},1)", 5, 0), ("=RANK(3.5,{7,3.5,3.5,1,2},1)", 3, 0),54        ("=RANK.EQ(2,{7,3.5,3.5,1,2})", 4, 0), ("=RANK.AVG(3.5,{7,3.5,3.5,1,2},1)", 3.5, 0),55        ("=PERCENTRANK.INC({13,12,11,8,4,3,2,1,1,1},2)", 0.333, 0), ("=PERCENTRANK.INC({13,12,11,8,4,3,2,1,1,1},4)", 0.555, 0),56        ("=PERCENTRANK.INC({13,12,11,8,4,3,2,1,1,1},8)", 0.666, 0), ("=PERCENTRANK.INC({13,12,11,8,4,3,2,1,1,1},5)", 0.583, 0),57        ("=PERCENTRANK({13,12,11,8,4,3,2,1,1,1},2)", 0.333, 0),58        ("=PERCENTRANK.EXC({1,2,3,6,6,6,7,8,9},7)", 0.7, 0), ("=PERCENTRANK.EXC({1,2,3,6,6,6,7,8,9},5.43)", 0.381, 0), ("=PERCENTRANK.EXC({1,2,3,6,6,6,7,8,9},5.43,1)", 0.3, 0),59        ("=STANDARDIZE(42,40,1.5)", 1.33333333, 1e-8), ("=FISHER(0.75)", 0.9729551, 1e-7), ("=FISHERINV(0.972955)", 0.75, 1e-6),60        ("=PROB({0,1,2,3},{0.2,0.3,0.1,0.4},2)", 0.1, 1e-15), ("=PROB({0,1,2,3},{0.2,0.3,0.1,0.4},1,3)", 0.8, 1e-15),61        ("=CORREL({3,2,4,5,6},{9,7,12,15,17})", 0.997054486, 1e-9), ("=COVARIANCE.P({3,2,4,5,6},{9,7,12,15,17})", 5.2, 1e-12), ("=COVAR({3,2,4,5,6},{9,7,12,15,17})", 5.2, 1e-12),62        ("=COVARIANCE.S({2,4,8},{5,11,12})", 9.666666667, 1e-9), ("=SLOPE({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})", 0.305556, 1e-6),63        ("=INTERCEPT({2,3,9,1,8},{6,5,11,7,5})", 0.0483871, 1e-7), ("=STEYX({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})", 3.305719, 1e-6), ("=RSQ({2,3,9,1,8,7,5},{6,5,11,7,5,4,4})", 0.05795, 1e-5),64        ("=PEARSON({9,7,5,3,1},{10,6,1,5,3})", 0.699379, 1e-6), ("=FORECAST(30,{6,7,9,15,21},{20,28,31,38,40})", 10.607253, 1e-6),65        ("=FORECAST.LINEAR(30,{6,7,9,15,21},{20,28,31,38,40})", 10.607253, 1e-6),66        ("=INDEX(LINEST({1,9,5,7},{0,4,2,3}),1,1)", 2, 1e-12), ("=INDEX(LINEST({1,9,5,7},{0,4,2,3}),1,2)", 1, 1e-12),67        ("=SUM(LINEST({1,9,5,7},{0,4,2,3},FALSE))", 2.31034483, 1e-8),68        ("=CONFIDENCE(0.05,2.5,50)", 0.692952, 1e-6), ("=CONFIDENCE.NORM(0.05,2.5,50)", 0.692952, 1e-6), ("=CONFIDENCE.T(0.05,1,50)", 0.284196855, 1e-9),69        ("=Z.TEST({3,6,7,8,6,5,4,2,1,9},4)", 0.090574, 1e-6), ("=Z.TEST({3,6,7,8,6,5,4,2,1,9},6)", 0.863043, 1e-6),70        ("=T.TEST({3,4,5,8,9,1,2,4,5},{6,19,3,2,14,4,5,17,1},2,1)", 0.196016, 1e-6), ("=F.TEST({6,7,9,15,21},{20,28,31,38,40})", 0.64831785, 1e-8),71        ("=CHISQ.TEST({58,35;11,25;10,23},{45.35,47.65;17.56,18.44;16.09,16.91})", 0.0003082, 1e-7),72        // distributions73        ("=NORM.DIST(42,40,1.5,TRUE)", 0.9087888, 1e-7), ("=NORM.DIST(42,40,1.5,FALSE)", 0.10934, 1e-5), ("=NORM.INV(0.908789,40,1.5)", 42.000002, 1e-6),74        ("=NORM.S.DIST(1.333333,TRUE)", 0.908788726, 1e-9), ("=NORM.S.DIST(1.333333,FALSE)", 0.164010148, 1e-9), ("=NORM.S.INV(0.908789)", 1.3333347, 1e-7),75        ("=T.DIST(60,1,TRUE)", 0.99469533, 1e-8), ("=T.DIST(8,3,FALSE)", 0.00073691, 1e-8), ("=T.DIST.2T(1.959999998,60)", 0.054644930, 1e-9),76        ("=T.DIST.RT(1.959999998,60)", 0.027322465, 1e-9), ("=T.INV(0.75,2)", 0.8164966, 1e-7), ("=T.INV.2T(0.546449,60)", 0.606533, 1e-6), ("=TINV(0.05464,60)", 1.96, 1e-2),77        ("=CHISQ.DIST(0.5,1,TRUE)", 0.52049988, 1e-8), ("=CHISQ.DIST(2,3,FALSE)", 0.20755375, 1e-8), ("=CHISQ.DIST.RT(18.307,10)", 0.0500006, 1e-7),78        ("=CHISQ.INV(0.93,1)", 3.283020286, 1e-9), ("=CHISQ.INV(0.6,2)", 1.832581464, 1e-9), ("=CHISQ.INV.RT(0.050001,10)", 18.306973, 1e-6),79        ("=F.DIST(15.2069,6,4,TRUE)", 0.99, 1e-6), ("=F.DIST(15.2069,6,4,FALSE)", 0.0012238, 1e-7), ("=F.DIST.RT(15.2068649,6,4)", 0.01, 1e-8),80        ("=F.INV(0.01,6,4)", 0.10930991, 1e-8), ("=F.INV.RT(0.01,6,4)", 15.20686, 1e-5),81        ("=BINOM.DIST(6,10,0.5,FALSE)", 0.2050781, 1e-7), ("=BINOM.INV(6,0.5,0.75)", 4, 0), ("=BINOM.DIST.RANGE(60,0.75,48)", 0.084, 1e-3), ("=BINOM.DIST.RANGE(60,0.75,45,50)", 0.524, 1e-3),82        ("=POISSON.DIST(2,5,TRUE)", 0.124652, 1e-6), ("=POISSON.DIST(2,5,FALSE)", 0.084224, 1e-6),83        ("=EXPON.DIST(0.2,10,TRUE)", 0.86466472, 1e-8), ("=EXPON.DIST(0.2,10,FALSE)", 1.35335283, 1e-8),84        ("=GAMMA.DIST(10.00001131,9,2,FALSE)", 0.032639, 1e-6), ("=GAMMA.DIST(10.00001131,9,2,TRUE)", 0.068094, 1e-6), ("=GAMMA.INV(0.068094,9,2)", 10.0000112, 1e-6),85        ("=GAMMA(2.5)", 1.329, 1e-3), ("=GAMMA(-3.75)", 0.268, 1e-3), ("=GAMMALN(4)", 1.7917595, 1e-7), ("=GAMMALN.PRECISE(4)", 1.7917595, 1e-7),86        ("=BETA.DIST(2,8,10,TRUE,1,3)", 0.6854706, 1e-7), ("=BETA.DIST(2,8,10,FALSE,1,3)", 1.4837646, 1e-7), ("=BETA.INV(0.685470581,8,10,1,3)", 2, 1e-8),87        ("=LOGNORM.DIST(4,3.5,1.2,TRUE)", 0.0390836, 1e-7), ("=LOGNORM.DIST(4,3.5,1.2,FALSE)", 0.0176176, 1e-7), ("=LOGNORM.INV(0.039084,3.5,1.2)", 4.0000252, 1e-6),88        ("=WEIBULL.DIST(105,20,100,TRUE)", 0.929581, 1e-6), ("=WEIBULL.DIST(105,20,100,FALSE)", 0.035589, 1e-6),89        ("=HYPGEOM.DIST(1,4,8,20,TRUE)", 0.4654, 1e-4), ("=HYPGEOM.DIST(1,4,8,20,FALSE)", 0.3633, 1e-4),90        ("=NEGBINOM.DIST(10,5,0.25,TRUE)", 0.3135141, 1e-7), ("=NEGBINOM.DIST(10,5,0.25,FALSE)", 0.0550487, 1e-7),91        ("=PHI(0.75)", 0.301137432, 1e-9), ("=GAUSS(2)", 0.47725, 1e-5), ("=ERF(0.745)", 0.70792892, 1e-8), ("=ERF(1)", 0.84270079, 1e-8), ("=ERFC(1)", 0.15729921, 1e-8),92        ("=ERF.PRECISE(0.745)", 0.70792892, 1e-8), ("=ERFC.PRECISE(1)", 0.15729921, 1e-8),93        // engineering94        // Excel's Bessel routines are only accurate to about 7 digits (the page prints 0.329925829 where95        // J2(1.9) = 0.3299257277…); this library returns the exact values, hence the loose tolerance.96        ("=BESSELJ(1.9,2)", 0.329925829, 2e-7), ("=BESSELY(2.5,1)", 0.145918138, 2e-7), ("=BESSELI(1.5,1)", 0.981666428, 2e-7), ("=BESSELK(1.5,1)", 0.277387804, 2e-7),97        ("=BIN2DEC(1100100)", 100, 0), ("=BIN2DEC(1111111111)", -1, 0), ("=HEX2DEC(\"A5\")", 165, 0), ("=HEX2DEC(\"FFFFFFFF5B\")", -165, 0), ("=HEX2DEC(\"3DA408B9\")", 1034160313, 0),98        ("=OCT2DEC(54)", 44, 0), ("=OCT2DEC(7777777533)", -165, 0),99        ("=BITAND(1,5)", 1, 0), ("=BITAND(13,25)", 9, 0), ("=BITOR(23,10)", 31, 0), ("=BITXOR(5,3)", 6, 0), ("=BITLSHIFT(4,2)", 16, 0), ("=BITRSHIFT(13,2)", 3, 0),100        ("=DELTA(5,4)", 0, 0), ("=DELTA(5,5)", 1, 0), ("=DELTA(0.5,0)", 0, 0), ("=GESTEP(5,4)", 1, 0), ("=GESTEP(5,5)", 1, 0), ("=GESTEP(-4,-5)", 1, 0), ("=GESTEP(-1)", 0, 0),101        ("=CONVERT(1,\"lbm\",\"kg\")", 0.4535924, 1e-7), ("=CONVERT(68,\"F\",\"C\")", 20, 1e-12), ("=CONVERT(CONVERT(100,\"ft\",\"m\"),\"ft\",\"m\")", 9.290304, 1e-12),102        ("=IMABS(\"5+12i\")", 13, 0), ("=IMAGINARY(\"3+4i\")", 4, 0), ("=IMAGINARY(\"0-j\")", -1, 0), ("=IMAGINARY(4)", 0, 0), ("=IMREAL(\"6-9i\")", 6, 0),103        ("=IMARGUMENT(\"3+4i\")", 0.92729522, 1e-8),104    ]105106    @Test func numericExamples() {107        let b = Book()108        var failures = [String]()109        for (f, expected, tol) in Self.numeric {110            let v = b.eval(f).scalar111            guard case .number(let n) = v, abs(n - expected) <= tol else {112                failures.append("\(f) expected \(expected) got \(v)")113                continue114            }115        }116        #expect(failures.isEmpty, Comment(rawValue: failures.joined(separator: "\n")))117    }118119    static let texts: [(String, String)] = [120        ("=ROMAN(499,0)", "CDXCIX"), ("=ROMAN(499,1)", "LDVLIV"), ("=ROMAN(499,2)", "XDIX"), ("=ROMAN(499,3)", "VDIV"), ("=ROMAN(499,4)", "ID"),121        ("=ROMAN(499)", "CDXCIX"), ("=ROMAN(1999)", "MCMXCIX"), ("=ROMAN(0)", ""),122        ("=BASE(7,2)", "111"), ("=BASE(100,16)", "64"), ("=BASE(15,2,10)", "0000001111"),123        ("=DEC2BIN(9,4)", "1001"), ("=DEC2BIN(-100)", "1110011100"), ("=DEC2HEX(100,4)", "0064"), ("=DEC2HEX(-54)", "FFFFFFFFCA"), ("=DEC2HEX(28)", "1C"),124        ("=DEC2HEX(64,1)", "#NUM!"), ("=DEC2OCT(58,3)", "072"), ("=DEC2OCT(-100)", "7777777634"),125        ("=BIN2HEX(11111011,4)", "00FB"), ("=BIN2HEX(1110)", "E"), ("=BIN2HEX(1111111111)", "FFFFFFFFFF"),126        ("=BIN2OCT(1001,3)", "011"), ("=BIN2OCT(1100100)", "144"), ("=BIN2OCT(1111111111)", "7777777777"),127        ("=HEX2BIN(\"F\",8)", "00001111"), ("=HEX2BIN(\"B7\")", "10110111"), ("=HEX2BIN(\"FFFFFFFFFF\")", "1111111111"),128        ("=HEX2OCT(\"F\",3)", "017"), ("=HEX2OCT(\"3B4E\")", "35516"), ("=HEX2OCT(\"FFFFFFFF00\")", "7777777400"),129        ("=OCT2BIN(3,3)", "011"), ("=OCT2BIN(7777777000)", "1000000000"), ("=OCT2HEX(100,4)", "0040"), ("=OCT2HEX(7777777533)", "FFFFFFFF5B"),130        ("=COMPLEX(3,4)", "3+4i"), ("=COMPLEX(3,4,\"j\")", "3+4j"), ("=COMPLEX(0,1)", "i"), ("=COMPLEX(1,0)", "1"),131        ("=IMCONJUGATE(\"3+4i\")", "3-4i"), ("=IMDIV(\"-238+240i\",\"10+24i\")", "5+12i"), ("=IMPRODUCT(\"3+4i\",\"5-3i\")", "27+11i"), ("=IMPRODUCT(\"1+2i\",30)", "30+60i"),132        ("=IMSUB(\"13+4i\",\"5+3i\")", "8+i"), ("=IMSUM(\"3+4i\",\"5-3i\")", "8+i"),133        ("=CONVERT(2.5,\"ft\",\"sec\")", "#N/A"), ("=MROUND(5,-2)", "#NUM!"), ("=FLOOR(2.5,-2)", "#NUM!"), ("=CEILING(2.5,-2)", "#NUM!"),134    ]135136    @Test func textExamples() {137        let b = Book()138        var failures = [String]()139        for (f, expected) in Self.texts {140            let v = b.eval(f).scalar141            if v.description != expected { failures.append("\(f) expected \(expected) got \(v)") }142        }143        #expect(failures.isEmpty, Comment(rawValue: failures.joined(separator: "\n")))144    }145146    /// Complex results printed by the documentation (compared numerically: the last digit of a147    /// 15-digit rendering depends on the platform's libm).148    static let complex: [(String, Double, Double)] = [149        ("=IMCOS(\"1+i\")", 0.833730025131149, -0.988897705762865), ("=IMEXP(\"1+i\")", 1.46869393991589, 2.28735528717884),150        ("=IMLN(\"3+4i\")", 1.6094379124341, 0.927295218001612), ("=IMLOG10(\"3+4i\")", 0.698970004336019, 0.402719196273373),151        ("=IMLOG2(\"3+4i\")", 2.32192809488736, 1.33780421245098), ("=IMPOWER(\"2+3i\",3)", -46, 9),152        ("=IMSIN(\"4+3i\")", -7.61923172032141, -6.548120040911), ("=IMSQRT(\"1+i\")", 1.09868411346781, 0.455089860562227),153    ]154155    @Test func complexExamples() throws {156        let b = Book()157        for (f, re, im) in Self.complex {158            let text = try #require(b.eval(f).scalar.textValue, Comment(rawValue: f))159            let z = try ComplexNumber(text: text)160            #expect(abs(z.re - re) < 1e-12 && abs(z.im - im) < 1e-12, Comment(rawValue: "\(f) got \(text)"))161        }162    }163164    /// The database example of the DSUM / DAVERAGE / DGET… help pages (orchard).165    @Test func databaseExamples() {166        let b = Book()167        let criteria: [[Value]] = [168            [.text("Tree"), .text("Height"), .text("Age"), .text("Yield"), .text("Profit"), .text("Height")],169            [.text("=Apple"), .text(">10"), .blank, .blank, .blank, .text("<16")],170            [.text("=Pear"), .blank, .blank, .blank, .blank, .blank],171        ]172        let data: [[Value]] = [173            [.text("Tree"), .text("Height"), .text("Age"), .text("Yield"), .text("Profit")],174            [.text("Apple"), .number(18), .number(20), .number(14), .number(105)],175            [.text("Pear"), .number(12), .number(12), .number(10), .number(96)],176            [.text("Cherry"), .number(13), .number(14), .number(9), .number(105)],177            [.text("Apple"), .number(14), .number(15), .number(10), .number(75)],178            [.text("Pear"), .number(9), .number(8), .number(8), .number(76.8)],179            [.text("Apple"), .number(8), .number(9), .number(6), .number(45)],180        ]181        for (r, row) in criteria.enumerated() { for (c, v) in row.enumerated() where !v.isBlank { b.engine.set(sheet: 0, row: r, col: c, .value(v)) } }182        for (r, row) in data.enumerated() { for (c, v) in row.enumerated() { b.engine.set(sheet: 0, row: r + 4, col: c, .value(v)) } }183        let cases: [(String, Double)] = [184            ("=DCOUNT(A5:E11,\"Age\",A1:F2)", 1), ("=DCOUNTA(A5:E11,\"Profit\",A1:F2)", 1), ("=DMAX(A5:E11,\"Profit\",A1:F3)", 96),185            ("=DMIN(A5:E11,\"Profit\",A1:B2)", 75), ("=DSUM(A5:E11,\"Profit\",A1:A2)", 225), ("=DSUM(A5:E11,\"Profit\",A1:F3)", 247.8),186            ("=DPRODUCT(A5:E11,\"Yield\",A1:B2)", 140), ("=DAVERAGE(A5:E11,\"Yield\",A1:B2)", 12), ("=DAVERAGE(A5:E11,3,A5:E11)", 13),187            ("=DSTDEV(A5:E11,\"Yield\",A1:A3)", 2.96647939), ("=DSTDEVP(A5:E11,\"Yield\",A1:A3)", 2.6532998), ("=DVAR(A5:E11,\"Yield\",A1:A3)", 8.8),188            ("=DVARP(A5:E11,\"Yield\",A1:A3)", 7.04), ("=DGET(A5:E11,\"Yield\",A1:F2)", 10),189        ]190        for (f, expected) in cases {191            #expect(close(b.num(f), expected, rel: 1e-7), Comment(rawValue: "\(f) got \(b.eval(f))"))192        }193        #expect(b.eval("=DGET(A5:E11,\"Yield\",A1:A3)") == .error(.num))194    }195196    /// FREQUENCY, TREND, GROWTH and LOGEST examples.197    @Test func arrayExamples() {198        let b = Book()199        let freq = b.eval("=FREQUENCY({79,85,78,85,50,81,95,88,97},{70,79,89})")200        #expect(freq == .array(ArrayValue(column: [.number(1), .number(2), .number(4), .number(2)])))201        // TREND help page: monthly costs 133890…150000 for months 1…12, forecast for months 13…17202        b.fill("A1", [133890, 135000, 135790, 137300, 138130, 139100, 139900, 141120, 141890, 143230, 144000, 145290])203        b.fill("B1", (1...12).map(Double.init))204        #expect(abs(b.num("=INDEX(TREND(A1:A12,B1:B12,{13;14;15;16;17}),1)") - 146172) < 0.5)205        #expect(abs(b.num("=INDEX(TREND(A1:A12,,{13;14;15;16;17}),5)") - 150244) < 0.5)206        // GROWTH / LOGEST help page: units 33100…220000 for months 11…16207        b.fill("D1", [33100, 47300, 69000, 102000, 150000, 220000])208        b.fill("E1", [11, 12, 13, 14, 15, 16])209        #expect(abs(b.num("=INDEX(GROWTH(D1:D6,E1:E6,{17;18}),1)") - 320197) < 0.5)210        #expect(abs(b.num("=INDEX(GROWTH(D1:D6,E1:E6,{17;18}),2)") - 468536) < 0.5)211        #expect(abs(b.num("=INDEX(LOGEST(D1:D6,E1:E6,TRUE,TRUE),1,1)") - 1.4633) < 1e-4)212        #expect(abs(b.num("=INDEX(LOGEST(D1:D6,E1:E6,TRUE,TRUE),1,2)") - 495.3) < 0.05)213    }214}215
Files main
Menu

Theme