From db7b4ee36200df4b4838c2111e81808016b4f6ef Mon Sep 17 00:00:00 2001 From: xuri Date: Mon, 1 Feb 2021 00:07:51 +0800 Subject: update formula functions test --- calc_test.go | 323 +++++++++++++++++++++++++++++++++-------------------------- 1 file changed, 182 insertions(+), 141 deletions(-) (limited to 'calc_test.go') diff --git a/calc_test.go b/calc_test.go index d0b1c64..d3621c6 100644 --- a/calc_test.go +++ b/calc_test.go @@ -177,6 +177,7 @@ func TestCalcCellValue(t *testing.T) { "=_xlfn.CSC(_xlfn.CSC(1))": "1.077851840310882", // _xlfn.CSCH "=_xlfn.CSCH(-3.14159265358979)": "-0.086589537530047", + "=_xlfn.CSCH(_xlfn.CSCH(1))": "1.044510103955183", // _xlfn.DECIMAL `=_xlfn.DECIMAL("1100",2)`: "12", `=_xlfn.DECIMAL("186A0",16)`: "100000", @@ -184,8 +185,9 @@ func TestCalcCellValue(t *testing.T) { `=_xlfn.DECIMAL("70122",8)`: "28754", `=_xlfn.DECIMAL("0x70122",8)`: "28754", // DEGREES - "=DEGREES(1)": "57.29577951308232", - "=DEGREES(2.5)": "143.2394487827058", + "=DEGREES(1)": "57.29577951308232", + "=DEGREES(2.5)": "143.2394487827058", + "=DEGREES(DEGREES(1))": "3282.806350011744", // EVEN "=EVEN(23)": "24", "=EVEN(2.22)": "4", @@ -193,47 +195,54 @@ func TestCalcCellValue(t *testing.T) { "=EVEN(-0.3)": "-2", "=EVEN(-11)": "-12", "=EVEN(-4)": "-4", + "=EVEN((0))": "0", // EXP - "=EXP(100)": "2.6881171418161356E+43", - "=EXP(0.1)": "1.105170918075648", - "=EXP(0)": "1", - "=EXP(-5)": "0.006737946999085", + "=EXP(100)": "2.6881171418161356E+43", + "=EXP(0.1)": "1.105170918075648", + "=EXP(0)": "1", + "=EXP(-5)": "0.006737946999085", + "=EXP(EXP(0))": "2.718281828459045", // FACT - "=FACT(3)": "6", - "=FACT(6)": "720", - "=FACT(10)": "3.6288E+06", + "=FACT(3)": "6", + "=FACT(6)": "720", + "=FACT(10)": "3.6288E+06", + "=FACT(FACT(3))": "720", // FACTDOUBLE - "=FACTDOUBLE(5)": "15", - "=FACTDOUBLE(8)": "384", - "=FACTDOUBLE(13)": "135135", + "=FACTDOUBLE(5)": "15", + "=FACTDOUBLE(8)": "384", + "=FACTDOUBLE(13)": "135135", + "=FACTDOUBLE(FACTDOUBLE(1))": "1", // FLOOR - "=FLOOR(26.75,0.1)": "26.700000000000003", - "=FLOOR(26.75,0.5)": "26.5", - "=FLOOR(26.75,1)": "26", - "=FLOOR(26.75,10)": "20", - "=FLOOR(26.75,20)": "20", - "=FLOOR(-26.75,-0.1)": "-26.700000000000003", - "=FLOOR(-26.75,-1)": "-26", - "=FLOOR(-26.75,-5)": "-25", + "=FLOOR(26.75,0.1)": "26.700000000000003", + "=FLOOR(26.75,0.5)": "26.5", + "=FLOOR(26.75,1)": "26", + "=FLOOR(26.75,10)": "20", + "=FLOOR(26.75,20)": "20", + "=FLOOR(-26.75,-0.1)": "-26.700000000000003", + "=FLOOR(-26.75,-1)": "-26", + "=FLOOR(-26.75,-5)": "-25", + "=FLOOR(FLOOR(26.75,1),1)": "26", // _xlfn.FLOOR.MATH - "=_xlfn.FLOOR.MATH(58.55)": "58", - "=_xlfn.FLOOR.MATH(58.55,0.1)": "58.5", - "=_xlfn.FLOOR.MATH(58.55,5)": "55", - "=_xlfn.FLOOR.MATH(58.55,1,1)": "58", - "=_xlfn.FLOOR.MATH(-58.55,1)": "-59", - "=_xlfn.FLOOR.MATH(-58.55,1,-1)": "-58", - "=_xlfn.FLOOR.MATH(-58.55,1,1)": "-59", // should be -58 - "=_xlfn.FLOOR.MATH(-58.55,10)": "-60", + "=_xlfn.FLOOR.MATH(58.55)": "58", + "=_xlfn.FLOOR.MATH(58.55,0.1)": "58.5", + "=_xlfn.FLOOR.MATH(58.55,5)": "55", + "=_xlfn.FLOOR.MATH(58.55,1,1)": "58", + "=_xlfn.FLOOR.MATH(-58.55,1)": "-59", + "=_xlfn.FLOOR.MATH(-58.55,1,-1)": "-58", + "=_xlfn.FLOOR.MATH(-58.55,1,1)": "-59", // should be -58 + "=_xlfn.FLOOR.MATH(-58.55,10)": "-60", + "=_xlfn.FLOOR.MATH(_xlfn.FLOOR.MATH(1),10)": "0", // _xlfn.FLOOR.PRECISE - "=_xlfn.FLOOR.PRECISE(26.75,0.1)": "26.700000000000003", - "=_xlfn.FLOOR.PRECISE(26.75,0.5)": "26.5", - "=_xlfn.FLOOR.PRECISE(26.75,1)": "26", - "=_xlfn.FLOOR.PRECISE(26.75)": "26", - "=_xlfn.FLOOR.PRECISE(26.75,10)": "20", - "=_xlfn.FLOOR.PRECISE(26.75,0)": "0", - "=_xlfn.FLOOR.PRECISE(-26.75,1)": "-27", - "=_xlfn.FLOOR.PRECISE(-26.75,-1)": "-27", - "=_xlfn.FLOOR.PRECISE(-26.75,-5)": "-30", + "=_xlfn.FLOOR.PRECISE(26.75,0.1)": "26.700000000000003", + "=_xlfn.FLOOR.PRECISE(26.75,0.5)": "26.5", + "=_xlfn.FLOOR.PRECISE(26.75,1)": "26", + "=_xlfn.FLOOR.PRECISE(26.75)": "26", + "=_xlfn.FLOOR.PRECISE(26.75,10)": "20", + "=_xlfn.FLOOR.PRECISE(26.75,0)": "0", + "=_xlfn.FLOOR.PRECISE(-26.75,1)": "-27", + "=_xlfn.FLOOR.PRECISE(-26.75,-1)": "-27", + "=_xlfn.FLOOR.PRECISE(-26.75,-5)": "-30", + "=_xlfn.FLOOR.PRECISE(_xlfn.FLOOR.PRECISE(26.75),-5)": "25", // GCD "=GCD(0)": "0", `=GCD("",1)`: "1", @@ -242,61 +251,71 @@ func TestCalcCellValue(t *testing.T) { "=GCD(15,10,25)": "5", "=GCD(0,8,12)": "4", "=GCD(7,2)": "1", + "=GCD(1,GCD(1))": "1", // INT "=INT(100.9)": "100", "=INT(5.22)": "5", "=INT(5.99)": "5", "=INT(-6.1)": "-7", "=INT(-100.9)": "-101", + "=INT(INT(0))": "0", // ISO.CEILING - "=ISO.CEILING(22.25)": "23", - "=ISO.CEILING(22.25,1)": "23", - "=ISO.CEILING(22.25,0.1)": "22.3", - "=ISO.CEILING(22.25,10)": "30", - "=ISO.CEILING(-22.25,1)": "-22", - "=ISO.CEILING(-22.25,0.1)": "-22.200000000000003", - "=ISO.CEILING(-22.25,5)": "-20", - "=ISO.CEILING(-22.25,0)": "0", + "=ISO.CEILING(22.25)": "23", + "=ISO.CEILING(22.25,1)": "23", + "=ISO.CEILING(22.25,0.1)": "22.3", + "=ISO.CEILING(22.25,10)": "30", + "=ISO.CEILING(-22.25,1)": "-22", + "=ISO.CEILING(-22.25,0.1)": "-22.200000000000003", + "=ISO.CEILING(-22.25,5)": "-20", + "=ISO.CEILING(-22.25,0)": "0", + "=ISO.CEILING(1,ISO.CEILING(1,0))": "0", // LCM - "=LCM(1,5)": "5", - "=LCM(15,10,25)": "150", - "=LCM(1,8,12)": "24", - "=LCM(7,2)": "14", - "=LCM(7)": "7", - `=LCM("",1)`: "1", - `=LCM(0,0)`: "0", + "=LCM(1,5)": "5", + "=LCM(15,10,25)": "150", + "=LCM(1,8,12)": "24", + "=LCM(7,2)": "14", + "=LCM(7)": "7", + `=LCM("",1)`: "1", + `=LCM(0,0)`: "0", + `=LCM(0,LCM(0,0))`: "0", // LN - "=LN(1)": "0", - "=LN(100)": "4.605170185988092", - "=LN(0.5)": "-0.693147180559945", + "=LN(1)": "0", + "=LN(100)": "4.605170185988092", + "=LN(0.5)": "-0.693147180559945", + "=LN(LN(100))": "1.527179625807901", // LOG - "=LOG(64,2)": "6", - "=LOG(100)": "2", - "=LOG(4,0.5)": "-2", - "=LOG(500)": "2.698970004336019", + "=LOG(64,2)": "6", + "=LOG(100)": "2", + "=LOG(4,0.5)": "-2", + "=LOG(500)": "2.698970004336019", + "=LOG(LOG(100))": "0.301029995663981", // LOG10 - "=LOG10(100)": "2", - "=LOG10(1000)": "3", - "=LOG10(0.001)": "-3", - "=LOG10(25)": "1.397940008672038", + "=LOG10(100)": "2", + "=LOG10(1000)": "3", + "=LOG10(0.001)": "-3", + "=LOG10(25)": "1.397940008672038", + "=LOG10(LOG10(100))": "0.301029995663981", // MOD - "=MOD(6,4)": "2", - "=MOD(6,3)": "0", - "=MOD(6,2.5)": "1", - "=MOD(6,1.333)": "0.668", - "=MOD(-10.23,1)": "0.77", + "=MOD(6,4)": "2", + "=MOD(6,3)": "0", + "=MOD(6,2.5)": "1", + "=MOD(6,1.333)": "0.668", + "=MOD(-10.23,1)": "0.77", + "=MOD(MOD(1,1),1)": "0", // MROUND - "=MROUND(333.7,0.5)": "333.5", - "=MROUND(333.8,1)": "334", - "=MROUND(333.3,2)": "334", - "=MROUND(555.3,400)": "400", - "=MROUND(555,1000)": "1000", - "=MROUND(-555.7,-1)": "-556", - "=MROUND(-555.4,-1)": "-555", - "=MROUND(-1555,-1000)": "-2000", + "=MROUND(333.7,0.5)": "333.5", + "=MROUND(333.8,1)": "334", + "=MROUND(333.3,2)": "334", + "=MROUND(555.3,400)": "400", + "=MROUND(555,1000)": "1000", + "=MROUND(-555.7,-1)": "-556", + "=MROUND(-555.4,-1)": "-555", + "=MROUND(-1555,-1000)": "-2000", + "=MROUND(MROUND(1,1),1)": "1", // MULTINOMIAL - "=MULTINOMIAL(3,1,2,5)": "27720", - `=MULTINOMIAL("",3,1,2,5)`: "27720", + "=MULTINOMIAL(3,1,2,5)": "27720", + `=MULTINOMIAL("",3,1,2,5)`: "27720", + "=MULTINOMIAL(MULTINOMIAL(1))": "1", // _xlfn.MUNIT "=_xlfn.MUNIT(4)": "", // ODD @@ -307,81 +326,96 @@ func TestCalcCellValue(t *testing.T) { "=ODD(-1.3)": "-3", "=ODD(-10)": "-11", "=ODD(-3)": "-3", + "=ODD(ODD(1))": "1", // PI "=PI()": "3.141592653589793", // POWER - "=POWER(4,2)": "16", + "=POWER(4,2)": "16", + "=POWER(4,POWER(1,1))": "4", // PRODUCT - "=PRODUCT(3,6)": "18", - `=PRODUCT("",3,6)`: "18", + "=PRODUCT(3,6)": "18", + `=PRODUCT("",3,6)`: "18", + `=PRODUCT(PRODUCT(1),3,6)`: "18", // QUOTIENT - "=QUOTIENT(5,2)": "2", - "=QUOTIENT(4.5,3.1)": "1", - "=QUOTIENT(-10,3)": "-3", + "=QUOTIENT(5,2)": "2", + "=QUOTIENT(4.5,3.1)": "1", + "=QUOTIENT(-10,3)": "-3", + "=QUOTIENT(QUOTIENT(1,2),3)": "0", // RADIANS - "=RADIANS(50)": "0.872664625997165", - "=RADIANS(-180)": "-3.141592653589793", - "=RADIANS(180)": "3.141592653589793", - "=RADIANS(360)": "6.283185307179586", + "=RADIANS(50)": "0.872664625997165", + "=RADIANS(-180)": "-3.141592653589793", + "=RADIANS(180)": "3.141592653589793", + "=RADIANS(360)": "6.283185307179586", + "=RADIANS(RADIANS(360))": "0.109662271123215", // ROMAN - "=ROMAN(499,0)": "CDXCIX", - "=ROMAN(1999,0)": "MCMXCIX", - "=ROMAN(1999,1)": "MLMVLIV", - "=ROMAN(1999,2)": "MXMIX", - "=ROMAN(1999,3)": "MVMIV", - "=ROMAN(1999,4)": "MIM", - "=ROMAN(1999,-1)": "MCMXCIX", - "=ROMAN(1999,5)": "MIM", + "=ROMAN(499,0)": "CDXCIX", + "=ROMAN(1999,0)": "MCMXCIX", + "=ROMAN(1999,1)": "MLMVLIV", + "=ROMAN(1999,2)": "MXMIX", + "=ROMAN(1999,3)": "MVMIV", + "=ROMAN(1999,4)": "MIM", + "=ROMAN(1999,-1)": "MCMXCIX", + "=ROMAN(1999,5)": "MIM", + "=ROMAN(1999,ODD(1))": "MLMVLIV", // ROUND - "=ROUND(100.319,1)": "100.30000000000001", - "=ROUND(5.28,1)": "5.300000000000001", - "=ROUND(5.9999,3)": "6.000000000000002", - "=ROUND(99.5,0)": "100", - "=ROUND(-6.3,0)": "-6", - "=ROUND(-100.5,0)": "-101", - "=ROUND(-22.45,1)": "-22.5", - "=ROUND(999,-1)": "1000", - "=ROUND(991,-1)": "990", + "=ROUND(100.319,1)": "100.30000000000001", + "=ROUND(5.28,1)": "5.300000000000001", + "=ROUND(5.9999,3)": "6.000000000000002", + "=ROUND(99.5,0)": "100", + "=ROUND(-6.3,0)": "-6", + "=ROUND(-100.5,0)": "-101", + "=ROUND(-22.45,1)": "-22.5", + "=ROUND(999,-1)": "1000", + "=ROUND(991,-1)": "990", + "=ROUND(ROUND(100,1),-1)": "100", // ROUNDDOWN - "=ROUNDDOWN(99.999,1)": "99.9", - "=ROUNDDOWN(99.999,2)": "99.99000000000002", - "=ROUNDDOWN(99.999,0)": "99", - "=ROUNDDOWN(99.999,-1)": "90", - "=ROUNDDOWN(-99.999,2)": "-99.99000000000002", - "=ROUNDDOWN(-99.999,-1)": "-90", + "=ROUNDDOWN(99.999,1)": "99.9", + "=ROUNDDOWN(99.999,2)": "99.99000000000002", + "=ROUNDDOWN(99.999,0)": "99", + "=ROUNDDOWN(99.999,-1)": "90", + "=ROUNDDOWN(-99.999,2)": "-99.99000000000002", + "=ROUNDDOWN(-99.999,-1)": "-90", + "=ROUNDDOWN(ROUNDDOWN(100,1),-1)": "100", // ROUNDUP` - "=ROUNDUP(11.111,1)": "11.200000000000001", - "=ROUNDUP(11.111,2)": "11.120000000000003", - "=ROUNDUP(11.111,0)": "12", - "=ROUNDUP(11.111,-1)": "20", - "=ROUNDUP(-11.111,2)": "-11.120000000000003", - "=ROUNDUP(-11.111,-1)": "-20", + "=ROUNDUP(11.111,1)": "11.200000000000001", + "=ROUNDUP(11.111,2)": "11.120000000000003", + "=ROUNDUP(11.111,0)": "12", + "=ROUNDUP(11.111,-1)": "20", + "=ROUNDUP(-11.111,2)": "-11.120000000000003", + "=ROUNDUP(-11.111,-1)": "-20", + "=ROUNDUP(ROUNDUP(100,1),-1)": "100", // SEC "=_xlfn.SEC(-3.14159265358979)": "-1", "=_xlfn.SEC(0)": "1", + "=_xlfn.SEC(_xlfn.SEC(0))": "0.54030230586814", // SECH "=_xlfn.SECH(-3.14159265358979)": "0.086266738334055", "=_xlfn.SECH(0)": "1", + "=_xlfn.SECH(_xlfn.SECH(0))": "0.648054273663886", // SIGN "=SIGN(9.5)": "1", "=SIGN(-9.5)": "-1", "=SIGN(0)": "0", "=SIGN(0.00000001)": "1", "=SIGN(6-7)": "-1", + "=SIGN(SIGN(-1))": "-1", // SIN "=SIN(0.785398163)": "0.707106780905509", + "=SIN(SIN(1))": "0.745624141665558", // SINH - "=SINH(0)": "0", - "=SINH(0.5)": "0.521095305493747", - "=SINH(-2)": "-3.626860407847019", + "=SINH(0)": "0", + "=SINH(0.5)": "0.521095305493747", + "=SINH(-2)": "-3.626860407847019", + "=SINH(SINH(0))": "0", // SQRT - "=SQRT(4)": "2", - `=SQRT("")`: "0", + "=SQRT(4)": "2", + "=SQRT(SQRT(16))": "2", // SQRTPI - "=SQRTPI(5)": "3.963327297606011", - "=SQRTPI(0.2)": "0.792665459521202", - "=SQRTPI(100)": "17.72453850905516", - "=SQRTPI(0)": "0", + "=SQRTPI(5)": "3.963327297606011", + "=SQRTPI(0.2)": "0.792665459521202", + "=SQRTPI(100)": "17.72453850905516", + "=SQRTPI(0)": "0", + "=SQRTPI(SQRTPI(0))": "0", // SUM "=SUM(1,2)": "3", `=SUM("",1,2)`: "3", @@ -415,27 +449,33 @@ func TestCalcCellValue(t *testing.T) { "=SUMSQ(A1:A4)": "14", "=SUMSQ(A1,B1,A2,B2,6)": "82", `=SUMSQ("",A1,B1,A2,B2,6)`: "82", + `=SUMSQ(1,SUMSQ(1))`: "2", // TAN "=TAN(1.047197551)": "1.732050806782486", "=TAN(0)": "0", + "=TAN(TAN(0))": "0", // TANH - "=TANH(0)": "0", - "=TANH(0.5)": "0.46211715726001", - "=TANH(-2)": "-0.964027580075817", + "=TANH(0)": "0", + "=TANH(0.5)": "0.46211715726001", + "=TANH(-2)": "-0.964027580075817", + "=TANH(TANH(0))": "0", // TRUNC - "=TRUNC(99.999,1)": "99.9", - "=TRUNC(99.999,2)": "99.99", - "=TRUNC(99.999)": "99", - "=TRUNC(99.999,-1)": "90", - "=TRUNC(-99.999,2)": "-99.99", - "=TRUNC(-99.999,-1)": "-90", + "=TRUNC(99.999,1)": "99.9", + "=TRUNC(99.999,2)": "99.99", + "=TRUNC(99.999)": "99", + "=TRUNC(99.999,-1)": "90", + "=TRUNC(-99.999,2)": "-99.99", + "=TRUNC(-99.999,-1)": "-90", + "=TRUNC(TRUNC(1),-1)": "0", // Statistical Functions // COUNTA `=COUNTA()`: "0", `=COUNTA(A1:A5,B2:B5,"text",1,2)`: "8", + `=COUNTA(COUNTA(1))`: "1", // MEDIAN - "=MEDIAN(A1:A5,12)": "2", - "=MEDIAN(A1:A5)": "1.5", + "=MEDIAN(A1:A5,12)": "2", + "=MEDIAN(A1:A5)": "1.5", + "=MEDIAN(A1:A5,MEDIAN(A1:A5,12))": "2", // Information Functions // ISBLANK "=ISBLANK(A1)": "FALSE", @@ -706,8 +746,8 @@ func TestCalcCellValue(t *testing.T) { // MULTINOMIAL `=MULTINOMIAL("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", // _xlfn.MUNIT - "=_xlfn.MUNIT()": "MUNIT requires 1 numeric argument", // not support currently - `=_xlfn.MUNIT("X")`: "strconv.Atoi: parsing \"X\": invalid syntax", // not support currently + "=_xlfn.MUNIT()": "MUNIT requires 1 numeric argument", // not support currently + `=_xlfn.MUNIT("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", // not support currently // ODD "=ODD()": "ODD requires 1 numeric argument", `=ODD("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", @@ -732,8 +772,8 @@ func TestCalcCellValue(t *testing.T) { // RAND "=RAND(1)": "RAND accepts no arguments", // RANDBETWEEN - `=RANDBETWEEN("X",1)`: "strconv.ParseInt: parsing \"X\": invalid syntax", - `=RANDBETWEEN(1,"X")`: "strconv.ParseInt: parsing \"X\": invalid syntax", + `=RANDBETWEEN("X",1)`: "strconv.ParseFloat: parsing \"X\": invalid syntax", + `=RANDBETWEEN(1,"X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", "=RANDBETWEEN()": "RANDBETWEEN requires 2 numeric arguments", "=RANDBETWEEN(2,1)": "#NUM!", // ROMAN @@ -770,6 +810,7 @@ func TestCalcCellValue(t *testing.T) { `=SINH("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", // SQRT "=SQRT()": "SQRT requires 1 numeric argument", + `=SQRT("")`: "strconv.ParseFloat: parsing \"\": invalid syntax", `=SQRT("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", "=SQRT(-1)": "#NUM!", // SQRTPI -- cgit v1.2.1