1import {
   2  Sheet
   3} from "../src/Sheet";
   4import {
   5  assertEquals,
   6  assertFormulaEquals,
   7  assertFormulaResultsInType,
   8  assertFormulaEqualsArray,
   9  assertFormulaEqualsError,
  10  assertFormulaEqualsDependsOnReference,
  11  test
  12} from "./Utils/Asserts";
  13import {
  14  NA_ERROR,
  15  PARSE_ERROR,
  16  REF_ERROR
  17} from "../src/Errors";
  18import {
  19  Cell
  20} from "../src/Cell";
  21
  22test("Sheet ABS", function(){
  23  assertFormulaEquals("=ABS(-10)", 10);
  24  assertFormulaEquals("=ABS(0)", 0);
  25});
  26
  27test("Sheet ACOS", function(){
  28  assertFormulaEquals("=ACOS(0)", 1.5707963267948966);
  29});
  30
  31test("Sheet ACCRINT", function(){
  32  assertFormulaEquals("=ACCRINT(DATE(2000, 1, 1), DATE(2000, 2, 1), DATE(2002, 12, 31), 0.05, 100, 4)",
  33    14.98631386861314);
  34});
  35
  36
  37test("Sheet ACOSH", function(){
  38  assertFormulaEquals("=ACOSH(22)", 3.783672704329451);
  39});
  40
  41test("Sheet ACOTH", function(){
  42  assertFormulaEquals("=ACOTH(22)", 0.04548588910286339);
  43});
  44
  45test("Sheet AND", function(){
  46  assertFormulaEquals("=AND(10, 10)", true);
  47  assertFormulaEquals("=AND(10, 0)", false);
  48});
  49
  50test("Sheet ARABIC", function(){
  51  assertFormulaEquals('=ARABIC("XIV")', 14);
  52});
  53
  54test("Sheet ASIN", function(){
  55  assertFormulaEquals("=ASIN(0.1)", 0.1001674211615598);
  56});
  57
  58test("Sheet ASINH", function(){
  59  assertFormulaEquals("=ASINH(0.1)", 0.09983407889920758);
  60});
  61
  62test("Sheet ATAN", function(){
  63  assertFormulaEquals("=ATAN(0.1)", 0.09966865249116204);
  64});
  65
  66test("Sheet ATAN2", function(){
  67  assertFormulaEquals("=ATAN2(4, 3)", 0.6435011087932844);
  68});
  69
  70test("Sheet ATANH", function(){
  71  assertFormulaEquals("=ATANH(0.51)", 0.5627297693521489);
  72});
  73
  74test("Sheet AVEDEV", function(){
  75  assertFormulaEquals("=AVEDEV(1, 2, 4, 56.7)", 20.387500000000003);
  76});
  77
  78test("Sheet AVERAGE", function(){
  79  assertFormulaEquals("=AVERAGE(10, 20, 4.1)", 11.366666666666667);
  80});
  81
  82test("Sheet AVERAGEA", function(){
  83  assertFormulaEquals("=AVERAGEA(10, 20, 4.1)", 11.366666666666667);
  84});
  85
  86test("Sheet AVERAGEIF", function(){
  87  assertFormulaEquals("=AVERAGEIF([1, 5, 10], '>2')", 7.5);
  88});
  89
  90test("Sheet BIN2DEC", function(){
  91  assertFormulaEquals("=BIN2DEC('1010101010')", -342);
  92});
  93
  94test("Sheet BIN2HEX", function(){
  95  assertFormulaEquals("=BIN2HEX(1010101010)", "FFFFFFFEAA");
  96});
  97
  98test("Sheet BIN2OCT", function(){
  99  assertFormulaEquals("=BIN2OCT(1010101010)", "7777777252");
 100});
 101
 102test("Sheet CEILING", function(){
 103  assertFormulaEquals("=CEILING(22.22, 0.1)", 22.3);
 104});
 105
 106test("Sheet CHAR", function(){
 107  assertFormulaEquals("=CHAR(97)", "a");
 108});
 109
 110test("Sheet CODE", function(){
 111  assertFormulaEquals("=CODE('a')", 97);
 112});
 113
 114test("Sheet COMBIN", function(){
 115  assertFormulaEquals("=COMBIN(4, 2)", 6);
 116});
 117
 118test("Sheet CONCATENATE", function(){
 119  assertFormulaEquals('=CONCATENATE("hey", " ", "there")', "hey there");
 120});
 121
 122test("Sheet CONVERT", function(){
 123  assertFormulaEquals('=CONVERT(5.1, "mm", "m")', 0.0050999999999999995);
 124});
 125
 126test("Sheet CORREL", function(){
 127  // assertFormulaEquals('=CORREL([9, 5], [10, 4])', 1); // TODO: [ISSUE-003]
 128});
 129
 130test("Sheet CHOOSE", function(){
 131  assertFormulaEquals('=CHOOSE(3, 1, 2, 3)', 3);
 132  assertFormulaEquals('=CHOOSE(2, 1, 2, 3)', 2);
 133  assertFormulaEquals('=CHOOSE(1, 1, 2, 3)', 1);
 134});
 135
 136test("Sheet COS", function(){
 137  assertFormulaEquals("=COS(PI())", -1);
 138});
 139
 140test("Sheet TRIM", function(){
 141  assertFormulaEquals("=TRIM(' trim ')", "trim");
 142});
 143
 144// I have strong suspicions that these are subject to minuscule rounding-errors and can change on different chips.
 145test("Sheet COSH", function(){
 146  assertFormulaEquals("=COSH(PI())", 11.591953275521519);
 147});
 148
 149test("Sheet COT", function(){
 150  assertFormulaEquals('=COT(30)', -0.15611995216165922);
 151});
 152
 153test("Sheet COTH", function(){
 154  assertFormulaEquals('=COTH(2)', 1.0373147207275482);
 155});
 156
 157test("Sheet COUNT", function(){
 158  assertFormulaEquals('=COUNT([1, 5, 10])', 3);
 159});
 160
 161test("Sheet COUNTA", function(){
 162  assertFormulaEquals("=COUNTA(10, 10, 22)", 3);
 163});
 164
 165test("Sheet COUNTIF", function(){
 166  assertFormulaEquals('=COUNTIF([1, 5, 10], ">4")', 2);
 167});
 168
 169test("Sheet COUNTIFS", function(){
 170  // assertFormulaEquals('=COUNTIFS([1, 5, 10], ">4", [1, 5, 10], ">4")', 2);  // TODO: [ISSUE-003]
 171});
 172
 173test("Sheet COUNTUNIQUE", function(){
 174  assertFormulaEquals('=COUNTUNIQUE([1, 1, 10])', 2);
 175});
 176
 177test("Sheet CUMIPMT", function(){
 178  assertFormulaEquals("=CUMIPMT(0.12, 12, 100, 1, 5, 0)", -54.39423242396348);
 179});
 180
 181test("Sheet COMPRINC", function(){
 182  assertFormulaEquals("=CUMPRINC(0.12, 12, 100, 1, 5, 0)", -26.324171373034403);
 183});
 184
 185test("Sheet DATE", function(){
 186  assertFormulaEquals("=DATE(2017, 6, 24)", 42910);
 187});
 188
 189test("Sheet DATEVALUE", function(){
 190  assertFormulaEquals("=DATEVALUE('2017/6/24')", 42910);
 191});
 192
 193test("Sheet DAY", function(){
 194  assertFormulaEquals("=DAY(DATE(1992, 6, 24))", 24);
 195});
 196
 197test("Sheet DAYS", function(){
 198  assertFormulaEquals("=DAYS(DATE(1992, 6, 24), DATE(1991, 6, 24))", 366);
 199});
 200
 201test("Sheet DAYS360", function(){
 202  assertFormulaEquals("=DAYS360(DATE(1992, 6, 24), DATE(1991, 6, 24))", -360);
 203});
 204
 205test("Sheet DB", function(){
 206  assertFormulaEquals("=DB(100, 50, 10, 2, 12)", 6.2482428240683285);
 207});
 208
 209test("Sheet DDB", function(){
 210  assertFormulaEquals("=DDB(100, 50, 10, 2, 2.25)", 17.4375);
 211});
 212
 213test("Sheet DEC2BIN", function(){
 214  assertFormulaEquals('=DEC2BIN("100", 8)', "01100100");
 215});
 216
 217test("Sheet DEC2HEX", function(){
 218  assertFormulaEquals('=DEC2HEX("100")', "64");
 219});
 220
 221test("Sheet DEC2OCT", function(){
 222  assertFormulaEquals('=DEC2OCT("100")', "144");
 223});
 224
 225test("Sheet DEGREES", function(){
 226  assertFormulaEquals('=DEGREES(PI())', 180);
 227});
 228
 229test("Sheet LCM", function(){
 230  assertFormulaEquals('=LCM(2, 5)', 10);
 231});
 232
 233test("Sheet GAMMALN", function(){
 234  assertFormulaEquals('=GAMMALN(4.5)', 2.453736570842444);
 235});
 236
 237test("Sheet PRODUCT", function(){
 238  assertFormulaEquals('=PRODUCT(2, 2)', 4);
 239});
 240
 241test("Sheet QUOTIENT", function(){
 242  assertFormulaEquals('=QUOTIENT(8, 2)', 4);
 243});
 244
 245test("Sheet UPLUS", function(){
 246  assertFormulaEquals('=UPLUS(8)', 8);
 247});
 248
 249test("Sheet UMINUS", function(){
 250  assertFormulaEquals('=UMINUS(8)', -8);
 251});
 252
 253test("Sheet STDEV", function(){
 254  assertFormulaEquals('=STDEV([33, 44])', 7.7781745930520225);
 255});
 256
 257test("Sheet STDEVA", function(){
 258  assertFormulaEquals('=STDEVA(33, 44)', 7.7781745930520225);
 259});
 260
 261test("Sheet STDEVP", function(){
 262  assertFormulaEquals('=STDEVP(33, 44)', 5.5);
 263});
 264
 265test("Sheet STDEVPA", function(){
 266  assertFormulaEquals('=STDEVPA(33, 44)', 5.5);
 267});
 268
 269test("Sheet PERCENTILE", function(){
 270  assertFormulaEquals('=PERCENTILE([10], 0)', 10);
 271});
 272
 273test("Sheet QUARTILE", function(){
 274  assertFormulaEquals('=QUARTILE([1, 2, 3, 4], 0)', 1);
 275});
 276
 277test("Sheet DELTA", function(){
 278  assertFormulaEquals('=DELTA(2, 2)', 1);
 279});
 280
 281test("Sheet RAND", function(){
 282  assertFormulaResultsInType('=RAND()', "number");
 283});
 284
 285test("Sheet RANDBETWEEN", function(){
 286  assertFormulaResultsInType('=RANDBETWEEN(1, 2)', "number");
 287});
 288
 289test("Sheet MULTIPLY", function(){
 290  assertFormulaEquals('=MULTIPLY(10, 10)', 100);
 291});
 292
 293test("Sheet MULTIPLY", function(){
 294  assertFormulaEquals('=MULTIPLY(2, 2)', 4);
 295});
 296
 297test("Sheet DIVIDE", function(){
 298  assertFormulaEquals('=DIVIDE(22, 11)', 2);
 299});
 300
 301test("Sheet EQ", function(){
 302  assertFormulaEquals('=EQ(22, 11)', false);
 303});
 304
 305test("Sheet GT", function(){
 306  assertFormulaEquals('=GT(1, 0)', true);
 307});
 308
 309test("Sheet GTE", function(){
 310  assertFormulaEquals('=GTE(1, 1)', true);
 311});
 312
 313test("Sheet LT", function(){
 314  assertFormulaEquals('=LT(0, 1)', true);
 315});
 316
 317test("Sheet NE", function(){
 318  assertFormulaEquals('=NE(0, 1)', true);
 319});
 320
 321test("Sheet LTE", function(){
 322  assertFormulaEquals('=LTE(0, 0)', true);
 323});
 324
 325
 326test("Sheet SIGN", function(){
 327  assertFormulaEquals('=SIGN(100)', 1);
 328});
 329
 330test("Sheet DELTA", function(){
 331  assertFormulaEquals('=DELTA(2, 2)', 1);
 332});
 333
 334test("Sheet DEVSQ", function(){
 335  assertFormulaEquals('=DEVSQ(1, 2)', 0.5);
 336});
 337
 338test("Sheet DOLLAR", function(){
 339  assertFormulaEquals('=DOLLAR(1.2351, 4)', 1.2351);
 340});
 341
 342test("Sheet DOLLARDE", function(){
 343  assertFormulaEquals('=DOLLARDE(100.1, 32)', 100.3125);
 344});
 345
 346test("Sheet DOLLARFR", function(){
 347  assertFormulaEquals('=DOLLARFR(100.1, 32)', 100.032);
 348});
 349
 350test("Sheet AND", function(){
 351  assertFormulaEquals('=AND(10)', true);
 352});
 353
 354test("Sheet EDATE", function(){
 355  assertFormulaEquals('=EDATE(DATE(1992, 6, 24), 1)', 33809);
 356});
 357
 358test("Sheet EOMONTH", function(){
 359  assertFormulaEquals('=EOMONTH(DATE(1992, 6, 24), 0)', 33785);
 360});
 361
 362test("Sheet EFFECT", function(){
 363  assertFormulaEquals('=EFFECT(0.99, 12)', 1.5890167507927795);
 364});
 365
 366test("Sheet ERF", function(){
 367  assertFormulaEquals('=ERF(2)', 0.9953222650189527);
 368});
 369
 370test("Sheet ERFC", function(){
 371  assertFormulaEquals('=ERFC(2)', 0.004677734981047288);
 372});
 373
 374test("Sheet EVEN", function(){
 375  assertFormulaEquals('=EVEN(3)', 4);
 376});
 377
 378test("Sheet EXACT", function(){
 379  assertFormulaEquals('=EXACT("m", "M")', false);
 380});
 381
 382test("Sheet EXPONDIST", function(){
 383  assertFormulaEquals('=EXPONDIST(4, 0.5, false)', 0.06766764161830635);
 384});
 385
 386test("Sheet FALSE", function(){
 387  assertFormulaEquals('=FALSE()', false);
 388});
 389
 390test("Sheet F.DIST", function(){
 391  assertFormulaEquals('=F.DIST(15.35, 7, 6, false)', 0.0003451054686025578);
 392  assertFormulaEquals('=F.DIST(15.35, 7, 6, true)', 0.9980694465675269);
 393});
 394
 395test("Sheet FINV", function(){
 396  assertFormulaEquals('=FINV(0.42, 2, 3)', 1.174597274485816);
 397});
 398
 399test("Sheet FISHER", function(){
 400  assertFormulaEquals('=FISHER(0.962)', 1.972066740199461);
 401});
 402
 403test("Sheet FISHERINV", function(){
 404  assertFormulaEquals('=FISHERINV(0.962)', 0.7451676440945232);
 405});
 406
 407test("Sheet IF", function(){
 408  assertFormulaEquals('=IF("m" = "m", "hit", "miss")', 'hit');
 409});
 410
 411test("Sheet INT", function(){
 412  assertFormulaEquals('=INT(99.33)', 99);
 413});
 414
 415test("Sheet ISEVEN", function(){
 416  assertFormulaEquals('=ISEVEN(4)', true);
 417});
 418
 419test("Sheet ISODD", function(){
 420  assertFormulaEquals('=ISODD(3)', true);
 421});
 422
 423test("Sheet LN", function(){
 424  assertFormulaEquals('=LN(100)', 4.605170185988092);
 425});
 426
 427test("Sheet LOG", function(){
 428  assertFormulaEquals('=LOG(256, 2)', 8);
 429});
 430
 431test("Sheet LOG10", function(){
 432  assertFormulaEquals('=LOG10(100)', 2);
 433});
 434
 435test("Sheet MAX", function(){
 436  assertFormulaEquals('=MAX(100, 22)', 100);
 437});
 438
 439test("Sheet MAXA", function(){
 440  assertFormulaEquals('=MAXA(100, 22, 44)', 100);
 441});
 442
 443test("Sheet MEDIAN", function(){
 444  assertFormulaEquals('=MEDIAN(100, 22, 54)', 54);
 445});
 446
 447test("Sheet MIN", function(){
 448  assertFormulaEquals('=MIN(100, 22, 44)', 22);
 449});
 450
 451test("Sheet MINA", function(){
 452  assertFormulaEquals('=MINA(100, 22, 44)', 22);
 453});
 454
 455test("Sheet MOD", function(){
 456  assertFormulaEquals('=MOD(10, 3)', 1);
 457});
 458
 459test("Sheet TRUE", function(){
 460  assertFormulaEquals('=TRUE()', true);
 461});
 462
 463test("Sheet NOT", function(){
 464  assertFormulaEquals('=NOT(TRUE())', false);
 465});
 466
 467test("Sheet ODD", function(){
 468  assertFormulaEquals('=ODD(2)', 3);
 469});
 470
 471test("Sheet OR", function(){
 472  assertFormulaEquals('=OR("m" = "p", "n" = "n")', true);
 473});
 474
 475test("Sheet PI", function(){
 476  assertFormulaEquals('=PI()', 3.141592653589793);
 477});
 478
 479test("Sheet POWER", function(){
 480  assertFormulaEquals('=POWER(4, 10)', 1048576);
 481});
 482
 483test("Sheet ROUND", function(){
 484  assertFormulaEquals('=ROUND(99.44, 1)', 99.4);
 485});
 486
 487test("Sheet ROUNDDOWN", function(){
 488  assertFormulaEquals('=ROUNDDOWN(99.46, 1)', 99.4);
 489});
 490
 491test("Sheet ROUNDUP", function(){
 492  assertFormulaEquals('=ROUNDUP(99.46, 1)', 99.5);
 493});
 494
 495test("Sheet SIN", function(){
 496  assertFormulaEquals('=SIN(0)', 0);
 497  assertFormulaEquals('=SIN(1)', 0.8414709848078965);
 498  assertFormulaEquals('=SIN(PI() / 2)', 1);
 499  assertFormulaEquals('=SIN(PI())', 0);
 500});
 501
 502test("Sheet SINH", function(){
 503  assertFormulaEquals('=SINH(PI())', 11.548739357257748);
 504});
 505
 506test("Sheet SPLIT", function(){
 507  assertFormulaEqualsArray('=SPLIT("1,2,3", ",", TRUE)', [ '1', '2', '3' ]);
 508});
 509
 510test("Sheet SQRT", function(){
 511  assertFormulaEquals('=SQRT(9)', 3);
 512});
 513
 514test("Sheet SQRTPI", function(){
 515  assertFormulaEquals('=SQRTPI(9)', 5.317361552716548);
 516});
 517
 518test("Sheet SUM", function(){
 519  assertFormulaEquals('=SUM(10, 10)', 20);
 520});
 521
 522test("Sheet SUMIF", function(){
 523  assertFormulaEquals('=SUMIF([1, 5, 10], 5)', 5);
 524});
 525
 526test("Sheet SUMPRODUCT", function(){
 527  // assertFormulaEquals('=SUMPRODUCT([1, 5, 10], [2, 2, 2])', 32); // TODO: [ISSUE-003]
 528});
 529
 530test("Sheet SUMSQ", function(){
 531  // assertFormulaEquals('=SUMSQ([1, 5, 10], 10)', 226); // TODO: [ISSUE-003]
 532});
 533
 534test("Sheet SUMX2MY2", function(){
 535  // assertFormulaEquals('=SUMX2MY2([1,2,3],[4,5,6])', -63); // TODO: [ISSUE-003]
 536});
 537
 538test("Sheet SUMX2PY2", function(){
 539  // assertFormulaEquals('=SUMX2PY2([1, 2, 3], [4, 5, 6])', 91); // TODO: [ISSUE-003]
 540});
 541
 542test("Sheet TAN", function(){
 543  assertFormulaEquals('=TAN(0)', 0);
 544  assertFormulaEquals('=TAN(1)', 1.5574077246549023);
 545  assertFormulaEquals('=TAN(PI() / 2)', 16331239353195370);
 546  assertFormulaEquals('=TAN(PI())', 0);
 547});
 548
 549test("Sheet TANH", function(){
 550  assertFormulaEquals('=TANH(PI())', 0.99627207622075);
 551});
 552
 553test("Sheet TRUE", function(){
 554  assertFormulaEquals('=TRUE()', true);
 555});
 556
 557test("Sheet TRUNC", function(){
 558  assertFormulaEquals('=TRUNC(3.1415, 2)', 3.14);
 559});
 560
 561test("Sheet XOR", function(){
 562  assertFormulaEquals('=XOR(1, 1)', false);
 563});
 564
 565test("Sheet YEARFRAC", function(){
 566  assertFormulaEquals('=YEARFRAC(1, 1461, 2)', 4.055555555555555);
 567});
 568
 569test("Sheet RADIANS", function(){
 570  assertFormulaEquals('=RADIANS(180)', 3.141592653589793);
 571});
 572
 573test("Sheet MONTH", function(){
 574  assertFormulaEquals('=MONTH(DATE(1992, 6, 24))', 6);
 575});
 576
 577test("Sheet YEAR", function(){
 578  assertFormulaEquals('=YEAR(DATE(1992, 6, 24))', 1992);
 579});
 580
 581test("Sheet WEEKDAY", function(){
 582  assertFormulaEquals('=WEEKDAY(DATE(1992, 6, 20))', 7);
 583});
 584
 585test("Sheet WEEKNUM", function(){
 586  assertFormulaEquals('=WEEKNUM(DATE(1992, 6, 19))', 25);
 587});
 588
 589test("Sheet DATEDIF", function(){
 590  assertFormulaEquals('=DATEDIF("1992-6-19", "1996-6-19", "Y")', 4);
 591});
 592
 593test("Sheet TIMEVALUE", function(){
 594  assertFormulaEquals('=TIMEVALUE("8:10")', 0.3402777777777778);
 595});
 596
 597test("Sheet HOUR", function(){
 598  assertFormulaEquals('=HOUR("8:10")', 8);
 599});
 600
 601test("Sheet MINUTE", function(){
 602  assertFormulaEquals('=MINUTE("8:10:29")', 10);
 603});
 604
 605test("Sheet SECOND", function(){
 606  assertFormulaEquals('=SECOND("8:10:29")', 29);
 607});
 608
 609test("Sheet NETWORKDAYS", function(){
 610  assertFormulaEquals('=NETWORKDAYS("1992-1-1", "1992-1-30")', 22);
 611});
 612
 613test("Sheet NETWORKDAYS.INTL", function(){
 614  assertFormulaEquals('=NETWORKDAYS.INTL("1992-1-1", "1992-1-30")', 22);
 615});
 616
 617test("Sheet TIME", function(){
 618  assertFormulaEquals('=TIME(10, 10, 10)', 0.4237268518518518);
 619});
 620
 621test("Sheet WORKDAY", function(){
 622  assertFormulaEquals('=WORKDAY(DATE(1999, 2, 2), 10)', 36207);
 623});
 624
 625test("Sheet WORKDAY.INTL", function(){
 626  assertFormulaEquals('=WORKDAY.INTL(DATE(1999, 2, 2), 10)', 36207);
 627});
 628
 629test("Sheet NA", function(){
 630  assertFormulaEqualsError('=NA()', NA_ERROR);
 631});
 632
 633test("Sheet ISTEXT", function(){
 634  assertFormulaEquals('=ISTEXT("str")', true);
 635});
 636
 637test("Sheet ISNONTEXT", function(){
 638  assertFormulaEquals('=ISNONTEXT("str")', false);
 639});
 640
 641test("Sheet ISLOGICAL", function(){
 642  assertFormulaEquals('=ISLOGICAL(true)', true);
 643});
 644
 645test("Sheet ISNUMBER", function(){
 646  assertFormulaEquals('=ISNUMBER(5)', true);
 647});
 648
 649test("Sheet MROUND", function(){
 650  assertFormulaEquals('=MROUND(21, 14)', 28);
 651});
 652
 653test("Sheet FACTDOUBLE", function(){
 654  assertFormulaEquals('=FACTDOUBLE(7)', 105);
 655});
 656
 657test("Sheet FREQUENCY", function(){
 658  assertFormulaEqualsArray('=FREQUENCY([10, 2, 3, 44, 1, 2], 22)', [5, 1]);
 659});
 660
 661test("Sheet GROWTH", function(){
 662  // TODO: [ISSUE-003]
 663  // assertFormulaEqualsArray('=GROWTH([15.53, 19.99, 20.43, 21.18, 25.93, 30.00, 30.00, 34.01, 36.47],
 664  // [1, 2, 3, 4, 5, 6, 7, 8, 9],[10, 11, 12])', [41.740521723275876, 46.22712349335047, 51.19598074591973]);
 665});
 666
 667test("Sheet TRIMMEAN", function(){
 668  assertFormulaEquals('=TRIMMEAN([1], 0.1)', 1);
 669});
 670
 671test("Sheet SLOPE", function(){
 672  // assertFormulaEquals('=SLOPE([600, 800], [44, 4.1])', -5.012531328320802); // TODO: [ISSUE-003]
 673});
 674
 675test("Sheet LOWER", function(){
 676  assertFormulaEquals('=LOWER("STR")', "str");
 677});
 678
 679test("Sheet UPPER", function(){
 680  assertFormulaEquals('=UPPER("str")', "STR");
 681});
 682
 683test("Sheet STANDARDIZE", function(){
 684  assertFormulaEquals('=STANDARDIZE(10, 2, 1)', 8);
 685});
 686
 687test("Sheet SMALL", function(){
 688  assertFormulaEquals('=SMALL([1, 2], 2)', 2);
 689});
 690
 691test("Sheet LARGE", function(){
 692  assertFormulaEquals('=LARGE([1, 2], 2)', 1);
 693});
 694
 695test("Sheet INTERCEPT", function(){
 696  // assertFormulaEquals('=INTERCEPT([1, 2, 3, 4], [10, 20, 33, 44])', 0.1791776688042246); // TODO: [ISSUE-003]
 697});
 698
 699test("Sheet FORECAST", function(){
 700  // assertFormulaEquals('=FORECAST([0], [1, 2, 3, 4], [10, 20, 33, 44])', 0.1791776688042246); // TODO: [ISSUE-003]
 701});
 702
 703test("Sheet SYD", function(){
 704  assertFormulaEquals('=SYD(100, 22, 10, 3)', 11.345454545454546);
 705});
 706
 707test("Sheet SLN", function(){
 708  assertFormulaEquals('=SLN(100, 22, 10)', 7.80);
 709});
 710
 711test("Sheet NPER", function(){
 712  assertFormulaEquals('=NPER(0.04, 100, 4000, 0, 0)', -24.362418941571313);
 713});
 714
 715test("Sheet NOMINAL", function(){
 716  assertFormulaEquals('=NOMINAL(0.8, 12)', 0.6024201620105654);
 717});
 718
 719test("Sheet MIRR", function(){
 720  assertFormulaEquals('=MIRR([10, 20, -30, 40], 0.05, 0.06)', 0.3458084697540138);
 721});
 722
 723test("Sheet IRR", function(){
 724  assertFormulaEquals('=IRR([-100, 100, 100])', 0.6180339809507132);
 725});
 726
 727test("Sheet IPMT", function(){
 728  assertFormulaEquals('=IPMT(0.025, 1, 66, 25000)', -625);
 729});
 730
 731test("Sheet FV", function(){
 732  assertFormulaEquals('=FV(0.05, 1, 66, 25000)', -26316);
 733});
 734
 735test("Sheet ISEMAIL", function(){
 736  assertFormulaEquals('=ISEMAIL("[email protected]")', true);
 737});
 738
 739test("Sheet ISURL", function(){
 740  assertFormulaEquals('=ISURL("example.com")', true);
 741});
 742
 743test("Sheet LINEST", function(){
 744  // TODO: [ISSUE-003]
 745  // assertFormulaEqualsArray('=LINEST([15.53, 19.99, 20.43, 21.18, 25.93, 30], [1, 2, 3, 4, 5, 6])',
 746  //     [2.5977142857142863,	13.08466666666666]);
 747});
 748
 749test("Sheet POISSON, POISSON.DIST", function(){
 750  assertFormulaEquals('=POISSON(3, 5, true)', 0.2650259152973617);
 751  assertFormulaEquals('=POISSON.DIST(3, 5, true)', 0.2650259152973617);
 752});
 753
 754test("Sheet PERCENTRANK, PERCENTRANK.INC", function(){
 755  assertFormulaEquals('=PERCENTRANK([1], 1)', 1);
 756  assertFormulaEquals('=PERCENTRANK.INC([1], 1)', 1);
 757});
 758
 759test("Sheet PERCENTRANK.EXC", function(){
 760  assertFormulaEquals('=PERCENTRANK.EXC([1], 1)', 1);
 761});
 762
 763test("Sheet NORMSINV", function(){
 764  assertFormulaEquals('=NORMSINV(0.1)', -1.2815515655446006);
 765});
 766
 767test("Sheet NORMSINV", function(){
 768  assertFormulaEquals('=NORMDIST(1, 0, 6, true)', 0.5661838326109037);
 769});
 770
 771test("Sheet NORMINV", function(){
 772  assertFormulaEquals('=NORMINV(0.8, 0, 6)', 5.0497274014374876);
 773});
 774
 775test("Sheet NEGBINOMDIST", function(){
 776  assertFormulaEquals('=NEGBINOMDIST(5, 3, 0.2)', 0.05505024000000004);
 777});
 778
 779test("Sheet GEOMEAN", function(){
 780  assertFormulaEquals('=GEOMEAN(10, 4, 6, 3, 6, 7, 1, 1)', 3.6313885790189477);
 781});
 782
 783test("Sheet HARMEAN", function(){
 784  assertFormulaEquals('=HARMEAN(10, 4, 6, 3, 6, 7, 1, 1)', 2.532027128862095);
 785});
 786
 787test("Sheet CONFIDENCE", function(){
 788  assertFormulaEquals('=CONFIDENCE(0.04, 6.48, 25)', 2.6616585881788426);
 789});
 790
 791test("Sheet N", function(){
 792  assertFormulaEquals('=N("10")', 10);
 793});
 794
 795test("Sheet UNARY_PERCENT", function(){
 796  assertFormulaEquals('=UNARY_PERCENT(10)', 0.1);
 797});
 798
 799test("Sheet MULTINOMIAL", function(){
 800  assertFormulaEquals('=MULTINOMIAL(2, 22)', 276);
 801});
 802
 803test("Sheet BINOMDIST", function(){
 804  assertFormulaEquals('=BINOMDIST(14, 22, 0.4, true)', 0.9929516025629364);
 805});
 806
 807test("Sheet COVAR", function(){
 808  // TODO: [ISSUE-003]
 809  // assertFormulaEquals('=COVAR([2, 4, 5, 1], [7, 3, 1, 3])', -2);
 810});
 811
 812test("Sheet ISREF", function(){
 813  assertFormulaEquals('=ISREF(B1)', true);
 814  assertFormulaEquals('=ISREF(B1:B10)', true);
 815  assertFormulaEquals('=ISREF(100)', false);
 816});
 817
 818test("Sheet ISBLANK", function(){
 819  assertFormulaEquals('=ISBLANK(10)', false);
 820  assertFormulaEquals('=ISBLANK(N10)', true);
 821});
 822
 823test("Sheet ISERR", function(){
 824  assertFormulaEquals('=ISERR(10)', false);
 825  assertFormulaEquals('=ISERR(1/0)', true);
 826  assertFormulaEquals('=ISERR(NA())', false);
 827  assertFormulaEquals('=ISERR(M7)', false);
 828  assertFormulaEquals('=ISERR([])', true);
 829  assertFormulaEquals('=ISERR(NOTAFUNCTION())', true);
 830  assertFormulaEquals('=ISERR(ACOS(44))', true);
 831  assertFormulaEqualsError('=ISERR(10e)', PARSE_ERROR);
 832});
 833
 834test("Sheet ISERROR", function(){
 835  assertFormulaEquals('=ISERROR(10)', false);
 836  assertFormulaEquals('=ISERROR(1/0)', true);
 837  assertFormulaEquals('=ISERROR(NA())', true);
 838  assertFormulaEquals('=ISERROR(M7)', false);
 839  assertFormulaEquals('=ISERROR([])', true);
 840  assertFormulaEquals('=ISERROR(NOTAFUNCTION())', true);
 841  assertFormulaEquals('=ISERROR(ACOS(44))', true);
 842  assertFormulaEqualsError('=ISERROR(10e)', PARSE_ERROR);
 843});
 844
 845test("Sheet ISNA", function(){
 846  assertFormulaEquals('=ISNA(10)', false);
 847  assertFormulaEquals('=ISNA(1/0)', false);
 848  assertFormulaEquals('=ISNA(NA())', true);
 849  assertFormulaEquals('=ISNA(M7)', false);
 850  assertFormulaEquals('=ISNA([])', false);
 851  assertFormulaEquals('=ISNA(NOTAFUNCTION())', false);
 852  assertFormulaEquals('=ISNA(ACOS(44))', false);
 853  assertFormulaEqualsError('=ISNA(10e)', PARSE_ERROR);});
 854
 855test("Sheet IFERROR", function(){
 856  assertFormulaEquals('=IFERROR(10)', 10);
 857  assertFormulaEquals('=IFERROR(NA())', null);
 858  assertFormulaEquals('=IFERROR(NOTAFUNCTION())', null);
 859  assertFormulaEquals('=IFERROR(1/0)', null);
 860  assertFormulaEquals('=IFERROR(M7)', new Cell("M7"));
 861  assertFormulaEquals('=IFERROR([])', null);
 862});
 863
 864test("Sheet ISFORMULA", function(){
 865  assertFormulaEqualsError('=ISFORMULA(10)', NA_ERROR);
 866  assertFormulaEqualsError('=ISFORMULA(false)', NA_ERROR);
 867  assertFormulaEqualsError('=ISFORMULA("str")', NA_ERROR);
 868  assertFormulaEqualsError('=ISFORMULA([])', REF_ERROR);
 869  assertFormulaEqualsError('=ISFORMULA([10])', NA_ERROR);
 870  assertFormulaEqualsDependsOnReference('D1', "=SUM(10, 5)", '=ISFORMULA(D1)', true);
 871  assertFormulaEquals('=ISFORMULA(M7)', false);
 872});
 873
 874test("Sheet TYPE", function(){
 875  assertFormulaEquals('=TYPE(10)', 1);
 876});
 877
 878test("Sheet COLUMN", function(){
 879  assertFormulaEqualsDependsOnReference('D1', 10, '=COLUMN(D1)', 4);
 880});
 881
 882test("Sheet ROW", function(){
 883  assertFormulaEqualsDependsOnReference('D2', 10, '=ROW(D2)', 2);
 884});
 885
 886test("Sheet T", function(){
 887  assertFormulaEquals('=T(10)', "");
 888  assertFormulaEquals('=T("str")', "str");
 889});
 890
 891test("Sheet PPMT", function(){
 892  assertFormulaEquals('=PPMT(0, 3, 24, 33000, 0, 1)', -1375.00);
 893});
 894
 895test("Sheet WEIBULL", function(){
 896  assertFormulaEquals('=WEIBULL(2.4, 2, 4, true)', 0.302323673928969);
 897});
 898
 899test("Sheet VARPA", function(){
 900  assertFormulaEquals('=VARPA(1, 2, 3, 4, 5, 6, 7, 8)', 5.25);
 901});
 902
 903test("Sheet VARP", function(){
 904  assertFormulaEquals('=VARP(1, 2, 3, 4, 5, 6, 7, 8)', 5.25);
 905});
 906
 907test("Sheet VARA", function(){
 908  assertFormulaEquals('=VARA(1, 2, 3, 4, 5, 6, 7, 8)', 6);
 909});
 910
 911test("Sheet VAR", function(){
 912  assertFormulaEquals('=VAR(1, 2, 3, 4, 5, 6, 7, 8)', 6);
 913});
 914
 915test("Sheet PERMUT", function(){
 916  assertFormulaEquals('=PERMUT(4, 2)', 12);
 917});
 918
 919test("Sheet RSQ", function(){
 920  // TODO: [ISSUE-003]
 921  // assertFormulaEquals('=RSQ([10, 22, 4], [1, 3, 7])', 0.2500000000000001);
 922});
 923
 924test("Sheet SKEW", function(){
 925  assertFormulaEquals('=SKEW(1, 2, 3, 4, 5, 6, 100)', 2.6336050735387375);
 926});
 927
 928test("Sheet STEYX", function(){
 929  // TODO: [ISSUE-003]
 930  // assertFormulaEquals('=STEYX([1, 2, 3, 4], [1, 3, 5, 2])', 1.4638501094227998);
 931});
 932
 933test("Sheet PROB", function(){
 934  // TODO: [ISSUE-003]
 935  // assertFormulaEquals('=PROB([1, 2, 3, 4], [0.25, 0.25, 0.25, 0.25], 3)', 0.25);
 936});
 937
 938test("Sheet MODE", function(){
 939  assertFormulaEquals('=MODE(1, 6, 7, 7, 8)', 7);
 940});
 941
 942test("Sheet RANK", function(){
 943  // TODO: [ISSUE-003]
 944  // assertFormulaEquals('=RANK([2], [1, 2, 3, 4, 5, 6, 7, 8, 9], true)', 2);
 945});
 946
 947test("Sheet RANK.AVG", function(){
 948  // TODO: [ISSUE-003]
 949  // assertFormulaEquals('=RANK.AVG([2], [1, 2, 3, 4, 5, 6, 7, 8, 9], true)', 2);
 950});
 951
 952test("Sheet RANK.EQ", function(){
 953  // TODO: [ISSUE-003]
 954  // assertFormulaEquals('=RANK.EQ([2], [1, 2, 3, 4, 5, 6, 7, 8, 9], true)', 2);
 955});
 956
 957test("Sheet LOGNORMDIST", function(){
 958  assertFormulaEquals('=LOGNORMDIST(4, 4, 6)', 0.33155709720516946);
 959});
 960
 961test("Sheet LOGNORMDIST", function(){
 962  assertFormulaEquals('=TDIST(0.55, 1, 2)', 0.6798800684756632);
 963});
 964
 965test("Sheet TO_DATE", function(){
 966  assertFormulaEquals('=TO_DATE(2)', 2);
 967});
 968
 969test("Sheet TO_DOLLARS", function(){
 970  assertFormulaEquals('=TO_DOLLARS(2)', 2);
 971});
 972
 973test("Sheet TO_PERCENT", function(){
 974  assertFormulaEquals('=TO_PERCENT(20)', 20);
 975});
 976
 977test("Sheet TO_TEXT", function(){
 978  assertFormulaEquals('=TO_TEXT(false)', "FALSE");
 979});
 980
 981test("Sheet ERROR.TYPE", function(){
 982  let sheet = new Sheet();
 983  sheet.setCell("M1", "= 1/0");
 984  sheet.setCell("A1", "=ERROR.TYPE(M1)");
 985  assertEquals(sheet.getCell("A1").getValue(), 2);
 986  // Empty range is a ref error, and should be caught by formula
 987  assertFormulaEquals('=ERROR.TYPE([])', 4);
 988  // Divide by zero error should be caught by formula
 989  assertFormulaEquals('=ERROR.TYPE(1/0)', 2);
 990  // NA error should also be caught by formula
 991  assertFormulaEquals('=ERROR.TYPE(NA())', 7);
 992  // name error should also be caught by formula
 993  assertFormulaEquals('=ERROR.TYPE(NOTAFUNCTION())', 5);
 994  // num error should also be caught by formula
 995  assertFormulaEquals('=ERROR.TYPE(ACOS(44))', 6);
 996  // Parse error should bubble up to cell
 997  assertFormulaEqualsError('=ERROR.TYPE(10e)', PARSE_ERROR);
 998  // Ref to empty cell should bubble up to cell
 999  assertFormulaEqualsError('=ERROR.TYPE(M8)', NA_ERROR);
1000  // Non-error value passed in should cause NA_ERROR
1001  assertFormulaEqualsError('=ERROR.TYPE(10)', NA_ERROR);
1002});
1003
1004test("Sheet ADDRESS", function(){
1005  assertFormulaEquals('=ADDRESS(2170, 2, 4, true, "SheetOne")', "SheetOne!B2170");
1006});
1007
1008test("Sheet COLUMNS", function(){
1009  assertFormulaEquals('=COLUMNS(1)', 1);
1010  assertFormulaEquals('=COLUMNS([1, 2, 3, 4])', 4);
1011  assertFormulaEquals('=COLUMNS(M1)', 1);
1012  assertFormulaEquals('=COLUMNS(B1:M44)', 12);
1013});
1014
1015test("Sheet ROWS", function(){
1016  assertFormulaEquals('=ROWS(1)', 1);
1017  assertFormulaEquals('=ROWS([1, 2, 3, 4])', 1);
1018  assertFormulaEquals('=ROWS(M1)', 1);
1019  assertFormulaEquals('=ROWS(B1:M44)', 44);
1020});
1021
1022test("Sheet SERIESSUM", function() {
1023  // TODO: [ISSUE-003]
1024  // assertFormulaEquals('=SERIESSUM([1], [0], [1], [4, 5, 6])', 15);
1025});
1026
1027test("Sheet ROMAN", function(){
1028  assertFormulaEquals('=ROMAN(3999)', "MMMCMXCIX");
1029});
1030
1031test("Sheet TEXT", function(){
1032  assertFormulaEquals('=TEXT(12.3, "###.##")', "12.3");
1033});
1034
1035test("Sheet FVSCHEDULE", function(){
1036  // TODO: [ISSUE-003]
1037  // assertFormulaEquals('=FVSCHEDULE([0.025], [1, 2, 3, 4])', 3.0000000000000004);
1038});
1039
1040test("Sheet PV", function(){
1041  assertFormulaEquals('=PV(2, 12, 100)', -49.99990591617884);
1042});
1043
1044test("Sheet RATE", function(){
1045  assertFormulaEquals('=RATE(12, -100, 400, 100)', 0.2225948800332845);
1046});
1047
1048test("Sheet SUBTOTAL", function(){
1049  // TODO: [ISSUE-003]
1050  // assertFormulaEquals('=SUBTOTAL([1], [1, 2, 3, 4, 5, 6, 7])', 4);
1051});
1052
1053test("Sheet HYPGEOMDIST", function(){
1054  assertFormulaEquals('=HYPGEOMDIST(4, 12, 20, 44)', 0.16895408557348432);
1055});
1056
1057test("Sheet ZTEST", function(){
1058  // TODO: [ISSUE-003]
1059  // assertFormulaEquals('=ZTEST([1, 2, 3, 4, 5, 6, 7], 5.6, 1.1)', 0.9999405457342111);
1060});
1061
1062test("Sheet FIND", function(){
1063  assertFormulaEquals('=FIND("s", "soup")', 1);
1064});
1065
1066test("Sheet JOIN", function(){
1067  // TODO: [ISSUE-003]
1068  // assertFormulaEquals('=JOIN([","], [1, 2, 3])', "1,2,3");
1069});
1070
1071test("Sheet LEN", function(){
1072  assertFormulaEquals('=LEN("soup")', 4);
1073});
1074
1075test("Sheet LEFT", function(){
1076  assertFormulaEquals('=LEFT("soup")', "s");
1077});
1078
1079test("Sheet RIGHT", function(){
1080  assertFormulaEquals('=RIGHT("soup")', "p");
1081});
1082
1083test("Sheet SEARCH", function(){
1084  assertFormulaEquals('=SEARCH("soup", "soup?")', 1);
1085});
1086
1087test("Sheet REPT", function(){
1088  assertFormulaEquals('=REPT("a", 2)', "aa");
1089});
1090
1091test("Sheet VALUE", function(){
1092  assertFormulaEquals('=VALUE("10")', 10);
1093});
1094
1095test("Sheet CLEAN", function(){
1096  assertFormulaEquals('=CLEAN("hello"&CHAR(31))', "hello");
1097});
1098
1099test("Sheet MID", function(){
1100  assertFormulaEquals('=MID("hey there", 5, 4)', "ther");
1101});
1102
1103test("Sheet PROPER", function(){
1104  assertFormulaEquals('=PROPER("hey there")', "Hey There");
1105});
1106
1107test("Sheet REPLACE", function(){
1108  assertFormulaEquals('=REPLACE("Hey there", 1, 3, "Hello")', "Hello there");
1109});
1110
1111test("Sheet SUBSTITUTE", function(){
1112  assertFormulaEquals('=SUBSTITUTE("Hey darkness my old friend", "Hey", "Hello")', "Hello darkness my old friend");
1113});
1114
1115test("Sheet parsing error", function(){
1116  assertFormulaEqualsError('= 10e', PARSE_ERROR);
1117  assertFormulaEqualsError('= SUM(', PARSE_ERROR);
1118});