1package sheet23import (4 "math"5 "testing"6)78func TestMathFunctions(t *testing.T) {9 checkFormulas(t, fixture(t), []fnCase{10 {`=SUMIF(B1:B5, "north", C1:C5)`, num(9)},11 {`=SUMIF(A1:A7, ">15")`, num(50)},12 {`=SUMIF(A1:A7, "<>10")`, num(45)}, // text and booleans aren't summed13 {`=SUMIF(B1:B5, "n*", C1)`, num(9)}, // sum range resized from its corner14 {`=SUMIF(C1:C7, 3)`, num(3)},15 {`=SUMIF(C1:C7, ">=" & C5)`, num(18)},16 {`=SUMIF(B1:B7, "~*")`, num(0)},17 {`=SUMIFS(C1:C5, B1:B5, "north", A1:A5, ">10")`, num(3)},18 {`=SUMIFS(C1:C5, B1:B5, "north", A1:A4, ">10")`, ErrValue}, // mismatched sizes19 {"=SUMPRODUCT(A1:A3, C1:C3)", num(140)},20 {"=SUMPRODUCT(A1:A4, C1:C4)", num(140)}, // text counts as 021 {"=SUMPRODUCT(A1:A3, C1:C4)", ErrValue},22 {"=PRODUCT(C1:C5)", num(120)},23 {"=PRODUCT(A4)", num(0)}, // text in references is ignored24 {"=PRODUCT(2, \"3\")", num(6)},25 {"=POWER(2, 10)", num(1024)},26 {"=POWER(0, -1)", ErrDiv0},27 {"=POWER(-8, 1/3)", num(-2)}, // an odd root, as Sheets28 {"=(-8)^(1/3)", num(-2)},29 {"=POWER(-32, 0.2)", num(-2)},30 {"=POWER(-8, 0.5)", ErrNum},31 {"=POWER(-8, 2/3)", ErrNum},32 {"=POWER(-8, 2)", num(64)},33 {"=ROUND(1.005, 2)", num(1.01)},34 {"=ROUND(-2.5)", num(-3)},35 {"=ROUND(1234.5, -2)", num(1200)},36 {"=ROUNDUP(2.3, 1)", num(2.3)}, // not 2.4 from binary noise37 {"=ROUNDUP(-2.31, 1)", num(-2.4)},38 {"=ROUNDUP(1234, -2)", num(1300)},39 {"=ROUNDDOWN(2.39, 1)", num(2.3)},40 {"=ROUNDDOWN(-2.39)", num(-2)},41 {"=TRUNC(-7.9)", num(-7)},42 {"=CEILING(2.1)", num(3)},43 {"=CEILING(7, 5)", num(10)},44 {"=CEILING(0.3, 0.1)", num(0.3)},45 {"=CEILING(-2.5, 1)", num(-2)},46 {"=CEILING(-2.5, -1)", num(-3)},47 {"=CEILING(2.5, -1)", ErrNum},48 {"=CEILING(5, 0)", num(0)},49 {"=FLOOR(2.9)", num(2)},50 {"=FLOOR(7, 5)", num(5)},51 {"=FLOOR(-2.5, 1)", num(-3)},52 {"=FLOOR(-2.5, -1)", num(-2)},53 {"=SIGN(-3)", num(-1)},54 {"=SIGN(0)", num(0)},55 {"=EXP(1)", num(math.E)},56 {"=LN(EXP(2))", num(2)},57 {"=LN(0)", ErrNum},58 {"=LOG(8, 2)", num(3)},59 {"=LOG(1000)", num(3)},60 {"=LOG(8, 1)", ErrDiv0},61 {"=LOG(-1)", ErrNum},62 {"=LOG10(0.01)", num(-2)},63 {"=QUOTIENT(-7, 2)", num(-3)},64 {"=QUOTIENT(1, 0)", ErrDiv0},65 {"=RANDBETWEEN(3, 3)", num(3)},66 {"=RANDBETWEEN(5, 1)", ErrNum},67 {"=AND(RAND()>=0, RAND()<1)", boolean(true)},68 })69}