diff options
Diffstat (limited to 'calc_test.go')
| -rw-r--r-- | calc_test.go | 121 |
1 files changed, 78 insertions, 43 deletions
diff --git a/calc_test.go b/calc_test.go index a391a19..de34723 100644 --- a/calc_test.go +++ b/calc_test.go @@ -57,12 +57,12 @@ func TestCalcCellValue(t *testing.T) { // BESSELJ "=BESSELJ(1.9,2)": "0.329925727692387", // BESSELK - "=BESSELK(0.05,0)": "3.114234034289662", + "=BESSELK(0.05,0)": "3.11423403428966", "=BESSELK(0.05,1)": "19.90967432724863", "=BESSELK(0.05,2)": "799.501207124235", - "=BESSELK(3,2)": "0.061510458561912", + "=BESSELK(3,2)": "0.0615104585619118", // BESSELY - "=BESSELY(0.05,0)": "-1.979311006841528", + "=BESSELY(0.05,0)": "-1.97931100684153", "=BESSELY(0.05,1)": "-12.789855163794034", "=BESSELY(0.05,2)": "-509.61489554491976", "=BESSELY(9,2)": "-0.229082087487741", @@ -169,7 +169,7 @@ func TestCalcCellValue(t *testing.T) { "=IMCOS(0.5)": "0.877582561890373", "=IMCOS(\"3+0.5i\")": "-1.1163412445261518-0.0735369737112366i", // IMCOSH - "=IMCOSH(0.5)": "1.127625965206381", + "=IMCOSH(0.5)": "1.12762596520638", "=IMCOSH(\"3+0.5i\")": "8.835204606500994+4.802825082743033i", "=IMCOSH(\"2-i\")": "2.0327230070196656-3.0518977991518i", "=IMCOSH(COMPLEX(1,-1))": "0.8337300251311491-0.9888977057628651i", @@ -188,7 +188,7 @@ func TestCalcCellValue(t *testing.T) { "=IMDIV(COMPLEX(5,2),COMPLEX(0,1))": "2-5i", // IMEXP "=IMEXP(0)": "1", - "=IMEXP(0.5)": "1.648721270700128", + "=IMEXP(0.5)": "1.64872127070013", "=IMEXP(\"1-2i\")": "-1.1312043837568135-2.4717266720048183i", "=IMEXP(COMPLEX(1,-1))": "1.4686939399158851-2.2873552871788423i", // IMLN @@ -243,7 +243,7 @@ func TestCalcCellValue(t *testing.T) { "=IMSUM(COMPLEX(5,2),COMPLEX(0,1))": "5+3i", // IMTAN "=IMTAN(-0)": "0", - "=IMTAN(0.5)": "0.546302489843791", + "=IMTAN(0.5)": "0.54630248984379", "=IMTAN(\"3+0.5i\")": "-0.11162105077158344+0.46946999342588536i", "=IMTAN(\"2-i\")": "-0.24345820118572523-1.16673625724092i", "=IMTAN(COMPLEX(1,-1))": "0.2717525853195117-1.0839233273386948i", @@ -275,21 +275,21 @@ func TestCalcCellValue(t *testing.T) { "=ABS(ABS(-1))": "1", // ACOS "=ACOS(-1)": "3.141592653589793", - "=ACOS(0)": "1.570796326794897", - "=ACOS(ABS(0))": "1.570796326794897", + "=ACOS(0)": "1.5707963267949", + "=ACOS(ABS(0))": "1.5707963267949", // ACOSH "=ACOSH(1)": "0", "=ACOSH(2.5)": "1.566799236972411", - "=ACOSH(5)": "2.292431669561178", - "=ACOSH(ACOSH(5))": "1.471383321536679", + "=ACOSH(5)": "2.29243166956118", + "=ACOSH(ACOSH(5))": "1.47138332153668", // ACOT "=_xlfn.ACOT(1)": "0.785398163397448", "=_xlfn.ACOT(-2)": "2.677945044588987", - "=_xlfn.ACOT(0)": "1.570796326794897", + "=_xlfn.ACOT(0)": "1.5707963267949", "=_xlfn.ACOT(_xlfn.ACOT(0))": "0.566911504941009", // ACOTH "=_xlfn.ACOTH(-5)": "-0.202732554054082", - "=_xlfn.ACOTH(1.1)": "1.522261218861711", + "=_xlfn.ACOTH(1.1)": "1.52226121886171", "=_xlfn.ACOTH(2)": "0.549306144334055", "=_xlfn.ACOTH(ABS(-2))": "0.549306144334055", // ARABIC @@ -299,12 +299,12 @@ func TestCalcCellValue(t *testing.T) { "=_xlfn.ARABIC(\"\")": "0", "=_xlfn.ARABIC(\" ll lc \")": "-50", // ASIN - "=ASIN(-1)": "-1.570796326794897", + "=ASIN(-1)": "-1.5707963267949", "=ASIN(0)": "0", "=ASIN(ASIN(0))": "0", // ASINH "=ASINH(0)": "0", - "=ASINH(-0.5)": "-0.481211825059604", + "=ASINH(-0.5)": "-0.481211825059603", "=ASINH(2)": "1.44363547517881", "=ASINH(ASINH(0))": "0", // ATAN @@ -383,22 +383,22 @@ func TestCalcCellValue(t *testing.T) { "=COS(COS(0))": "0.54030230586814", // COSH "=COSH(0)": "1", - "=COSH(0.5)": "1.127625965206381", - "=COSH(-2)": "3.762195691083632", - "=COSH(COSH(0))": "1.543080634815244", + "=COSH(0.5)": "1.12762596520638", + "=COSH(-2)": "3.76219569108363", + "=COSH(COSH(0))": "1.54308063481524", // _xlfn.COT - "=_xlfn.COT(0.785398163397448)": "1.000000000000001", + "=_xlfn.COT(0.785398163397448)": "1", "=_xlfn.COT(_xlfn.COT(0.45))": "-0.545473116787229", // _xlfn.COTH - "=_xlfn.COTH(-3.14159265358979)": "-1.003741873197322", - "=_xlfn.COTH(_xlfn.COTH(1))": "1.156014018113954", + "=_xlfn.COTH(-3.14159265358979)": "-1.00374187319732", + "=_xlfn.COTH(_xlfn.COTH(1))": "1.15601401811395", // _xlfn.CSC - "=_xlfn.CSC(-6)": "3.578899547254406", + "=_xlfn.CSC(-6)": "3.57889954725441", "=_xlfn.CSC(1.5707963267949)": "1", - "=_xlfn.CSC(_xlfn.CSC(1))": "1.077851840310882", + "=_xlfn.CSC(_xlfn.CSC(1))": "1.07785184031088", // _xlfn.CSCH - "=_xlfn.CSCH(-3.14159265358979)": "-0.086589537530047", - "=_xlfn.CSCH(_xlfn.CSCH(1))": "1.044510103955183", + "=_xlfn.CSCH(-3.14159265358979)": "-0.0865895375300472", + "=_xlfn.CSCH(_xlfn.CSCH(1))": "1.04451010395518", // _xlfn.DECIMAL `=_xlfn.DECIMAL("1100",2)`: "12", `=_xlfn.DECIMAL("186A0",16)`: "100000", @@ -419,9 +419,9 @@ func TestCalcCellValue(t *testing.T) { "=EVEN((0))": "0", // EXP "=EXP(100)": "2.6881171418161356E+43", - "=EXP(0.1)": "1.105170918075648", + "=EXP(0.1)": "1.10517091807565", "=EXP(0)": "1", - "=EXP(-5)": "0.006737946999085", + "=EXP(-5)": "0.00673794699908547", "=EXP(EXP(0))": "2.718281828459045", // FACT "=FACT(3)": "6", @@ -502,18 +502,18 @@ func TestCalcCellValue(t *testing.T) { "=LN(1)": "0", "=LN(100)": "4.605170185988092", "=LN(0.5)": "-0.693147180559945", - "=LN(LN(100))": "1.527179625807901", + "=LN(LN(100))": "1.5271796258079", // LOG "=LOG(64,2)": "6", "=LOG(100)": "2", "=LOG(4,0.5)": "-2", - "=LOG(500)": "2.698970004336019", + "=LOG(500)": "2.69897000433602", "=LOG(LOG(100))": "0.301029995663981", // LOG10 "=LOG10(100)": "2", "=LOG10(1000)": "3", "=LOG10(0.001)": "-3", - "=LOG10(25)": "1.397940008672038", + "=LOG10(25)": "1.39794000867204", "=LOG10(LOG10(100))": "0.301029995663981", // IMLOG2 "=IMLOG2(\"5+2i\")": "2.4289904975637864+0.5489546632866347i", @@ -626,9 +626,9 @@ func TestCalcCellValue(t *testing.T) { "=_xlfn.SEC(0)": "1", "=_xlfn.SEC(_xlfn.SEC(0))": "0.54030230586814", // SECH - "=_xlfn.SECH(-3.14159265358979)": "0.086266738334055", + "=_xlfn.SECH(-3.14159265358979)": "0.0862667383340547", "=_xlfn.SECH(0)": "1", - "=_xlfn.SECH(_xlfn.SECH(0))": "0.648054273663886", + "=_xlfn.SECH(_xlfn.SECH(0))": "0.648054273663885", // SIGN "=SIGN(9.5)": "1", "=SIGN(-9.5)": "-1", @@ -665,10 +665,10 @@ func TestCalcCellValue(t *testing.T) { "=STDEVA(MUNIT(2))": "0.577350269189626", "=STDEVA(0,INT(0))": "0", // POISSON.DIST - "=POISSON.DIST(20,25,FALSE)": "0.051917468608491", + "=POISSON.DIST(20,25,FALSE)": "0.0519174686084913", "=POISSON.DIST(35,40,TRUE)": "0.242414197690103", // POISSON - "=POISSON(20,25,FALSE)": "0.051917468608491", + "=POISSON(20,25,FALSE)": "0.0519174686084913", "=POISSON(35,40,TRUE)": "0.242414197690103", // SUM "=SUM(1,2)": "3", @@ -760,15 +760,15 @@ func TestCalcCellValue(t *testing.T) { "=GAMMA(1.5)": "0.886226925452758", "=GAMMA(5.5)": "52.34277778455352", // GAMMALN - "=GAMMALN(4.5)": "2.453736570842443", + "=GAMMALN(4.5)": "2.45373657084244", "=GAMMALN(INT(1))": "0", // HARMEAN - "=HARMEAN(2.5,3,0.5,1,3)": "1.229508196721312", - "=HARMEAN(\"2.5\",3,0.5,1,INT(3),\"\")": "1.229508196721312", + "=HARMEAN(2.5,3,0.5,1,3)": "1.22950819672131", + "=HARMEAN(\"2.5\",3,0.5,1,INT(3),\"\")": "1.22950819672131", // KURT - "=KURT(F1:F9)": "-1.033503502551368", - "=KURT(F1,F2:F9)": "-1.033503502551368", - "=KURT(INT(1),MUNIT(2))": "-3.333333333333336", + "=KURT(F1:F9)": "-1.03350350255137", + "=KURT(F1,F2:F9)": "-1.03350350255137", + "=KURT(INT(1),MUNIT(2))": "-3.33333333333334", // NORM.DIST "=NORM.DIST(0.8,1,0.3,TRUE)": "0.252492537546923", "=NORM.DIST(50,40,20,FALSE)": "0.017603266338215", @@ -993,6 +993,29 @@ func TestCalcCellValue(t *testing.T) { "=YEAR(42171)": "2015", "=YEAR(\"29-May-2015\")": "2015", "=YEAR(\"05/03/1984\")": "1984", + // YEARFRAC + "=YEARFRAC(42005,42005)": "0", + "=YEARFRAC(42005,42094)": "0.25", + "=YEARFRAC(42005,42094,0)": "0.25", + "=YEARFRAC(42005,42094,1)": "0.243835616438356", + "=YEARFRAC(42005,42094,2)": "0.247222222222222", + "=YEARFRAC(42005,42094,3)": "0.243835616438356", + "=YEARFRAC(42005,42094,4)": "0.247222222222222", + "=YEARFRAC(\"01/01/2015\",\"03/31/2015\")": "0.25", + "=YEARFRAC(\"01/01/2015\",\"03/31/2015\",0)": "0.25", + "=YEARFRAC(\"01/01/2015\",\"03/31/2015\",1)": "0.243835616438356", + "=YEARFRAC(\"01/01/2015\",\"03/31/2015\",2)": "0.247222222222222", + "=YEARFRAC(\"01/01/2015\",\"03/31/2015\",3)": "0.243835616438356", + "=YEARFRAC(\"01/01/2015\",\"03/31/2015\",4)": "0.247222222222222", + "=YEARFRAC(\"01/01/2015\",42094)": "0.25", + "=YEARFRAC(42005,\"03/31/2015\",0)": "0.25", + "=YEARFRAC(\"01/31/2015\",\"03/31/2015\")": "0.166666666666667", + "=YEARFRAC(\"01/30/2015\",\"03/31/2015\")": "0.166666666666667", + "=YEARFRAC(\"02/29/2000\", \"02/29/2008\")": "8", + "=YEARFRAC(\"02/29/2000\", \"02/29/2008\",1)": "7.998175182481752", + "=YEARFRAC(\"02/29/2000\", \"01/29/2001\",1)": "0.915300546448087", + "=YEARFRAC(\"02/29/2000\", \"03/29/2000\",1)": "0.0792349726775956", + "=YEARFRAC(\"01/31/2000\", \"03/29/2000\",4)": "0.163888888888889", // Text Functions // CHAR "=CHAR(65)": "A", @@ -1246,7 +1269,7 @@ func TestCalcCellValue(t *testing.T) { "=ISPMT(0.05/12,2,60,50000)": "-201.38888888888886", "=ISPMT(0.05/12,2,1,50000)": "208.33333333333334", // NOMINAL - "=NOMINAL(0.025,12)": "0.024718035238113", + "=NOMINAL(0.025,12)": "0.0247180352381129", // NPER "=NPER(0.04,-6000,50000)": "10.338035071507665", "=NPER(0,-6000,50000)": "8.333333333333334", @@ -2062,6 +2085,12 @@ func TestCalcCellValue(t *testing.T) { "=YEAR(-1)": "YEAR only accepts positive argument", "=YEAR(\"text\")": "#VALUE!", "=YEAR(\"January 25, 100\")": "#VALUE!", + // YEARFRAC + "=YEARFRAC()": "YEARFRAC requires 3 or 4 arguments", + "=YEARFRAC(42005,42094,5)": "invalid basis", + "=YEARFRAC(\"\",42094,5)": "#VALUE!", + "=YEARFRAC(42005,\"\",5)": "#VALUE!", + "=YEARFRAC(42005,42094,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // NOW "=NOW(A1)": "NOW accepts no arguments", // TODAY @@ -2412,8 +2441,8 @@ func TestCalcCellValue(t *testing.T) { "=(-2-SUM(-4+A2))*5": "0", "=SUM(Sheet1!A1:Sheet1!A1:A2,A2)": "5", "=SUM(A1,A2,A3)*SUM(2,3)": "30", - "=1+SUM(SUM(A1+A2/A3)*(2-3),2)": "1.333333333333334", - "=A1/A2/SUM(A1:A2:B1)": "0.041666666666667", + "=1+SUM(SUM(A1+A2/A3)*(2-3),2)": "1.33333333333333", + "=A1/A2/SUM(A1:A2:B1)": "0.0416666666666667", "=A1/A2/SUM(A1:A2:B1)*A3": "0.125", "=SUM(B1:D1)": "4", "=SUM(\"X\")": "0", @@ -2760,7 +2789,7 @@ func TestCalcMIRR(t *testing.T) { cellData := [][]interface{}{{-100}, {18}, {22.5}, {28}, {35.5}, {45}} f := prepareCalcData(cellData) formulaList := map[string]string{ - "=MIRR(A1:A5,0.055,0.05)": "0.025376365108071", + "=MIRR(A1:A5,0.055,0.05)": "0.0253763651080707", "=MIRR(A1:A6,0.055,0.05)": "0.1000268752662", } for formula, expected := range formulaList { @@ -2848,3 +2877,9 @@ func TestStrToDate(t *testing.T) { _, _, _, _, err := strToDate("") assert.Equal(t, formulaErrorVALUE, err.Error) } + +func TestGetYearDays(t *testing.T) { + for _, data := range [][]int{{2021, 0, 360}, {2000, 1, 366}, {2021, 1, 365}, {2000, 3, 365}} { + assert.Equal(t, data[2], getYearDays(data[0], data[1])) + } +} |