From 067c5d564383aa56f91fc0b9250352a5542c1e6f Mon Sep 17 00:00:00 2001 From: xuri Date: Mon, 21 Mar 2022 00:02:42 +0800 Subject: This closes #1185, fix formula function calculation result precision issue on arm64 * New formula functions: BINOM.DIST.RANGE and BINOM.INV * Fix complex number calculation result precision issue --- calc_test.go | 123 ++++++++++++++++++++++++++++++++++++++--------------------- 1 file changed, 80 insertions(+), 43 deletions(-) (limited to 'calc_test.go') diff --git a/calc_test.go b/calc_test.go index e0cd0d0..cb09d26 100644 --- a/calc_test.go +++ b/calc_test.go @@ -206,21 +206,21 @@ func TestCalcCellValue(t *testing.T) { // IMCOS "=IMCOS(0)": "1", "=IMCOS(0.5)": "0.877582561890373", - "=IMCOS(\"3+0.5i\")": "-1.1163412445261518-0.0735369737112366i", + "=IMCOS(\"3+0.5i\")": "-1.11634124452615-0.0735369737112366i", // IMCOSH "=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", + "=IMCOSH(\"3+0.5i\")": "8.83520460650099+4.80282508274303i", + "=IMCOSH(\"2-i\")": "2.03272300701967-3.0518977991518i", + "=IMCOSH(COMPLEX(1,-1))": "0.833730025131149-0.988897705762865i", // IMCOT "=IMCOT(0.5)": "1.83048772171245", - "=IMCOT(\"3+0.5i\")": "-0.4793455787473728-2.016092521506228i", - "=IMCOT(\"2-i\")": "-0.171383612909185+0.8213297974938518i", - "=IMCOT(COMPLEX(1,-1))": "0.21762156185440268+0.868014142895925i", + "=IMCOT(\"3+0.5i\")": "-0.479345578747373-2.01609252150623i", + "=IMCOT(\"2-i\")": "-0.171383612909185+0.821329797493852i", + "=IMCOT(COMPLEX(1,-1))": "0.217621561854403+0.868014142895925i", // IMCSC - "=IMCSC(\"j\")": "-0.8509181282393216i", + "=IMCSC(\"j\")": "-0.850918128239322j", // IMCSCH - "=IMCSCH(COMPLEX(1,-1))": "0.30393100162842646+0.6215180171704284i", + "=IMCSCH(COMPLEX(1,-1))": "0.303931001628426+0.621518017170428i", // IMDIV "=IMDIV(\"5+2i\",\"1+i\")": "3.5-1.5i", "=IMDIV(\"2+2i\",\"2+i\")": "1.2+0.4i", @@ -228,18 +228,18 @@ func TestCalcCellValue(t *testing.T) { // IMEXP "=IMEXP(0)": "1", "=IMEXP(0.5)": "1.64872127070013", - "=IMEXP(\"1-2i\")": "-1.1312043837568135-2.4717266720048183i", - "=IMEXP(COMPLEX(1,-1))": "1.4686939399158851-2.2873552871788423i", + "=IMEXP(\"1-2i\")": "-1.13120438375681-2.47172667200482i", + "=IMEXP(COMPLEX(1,-1))": "1.46869393991589-2.28735528717884i", // IMLN "=IMLN(0.5)": "-0.693147180559945", - "=IMLN(\"3+0.5i\")": "1.1123117757621668+0.16514867741462683i", - "=IMLN(\"2-i\")": "0.8047189562170503-0.4636476090008061i", - "=IMLN(COMPLEX(1,-1))": "0.3465735902799727-0.7853981633974483i", + "=IMLN(\"3+0.5i\")": "1.11231177576217+0.165148677414627i", + "=IMLN(\"2-i\")": "0.80471895621705-0.463647609000806i", + "=IMLN(COMPLEX(1,-1))": "0.346573590279973-0.785398163397448i", // IMLOG10 "=IMLOG10(0.5)": "-0.301029995663981", - "=IMLOG10(\"3+0.5i\")": "0.48307086636951624+0.07172315929479262i", - "=IMLOG10(\"2-i\")": "0.34948500216800943-0.20135959813668655i", - "=IMLOG10(COMPLEX(1,-1))": "0.1505149978319906-0.3410940884604603i", + "=IMLOG10(\"3+0.5i\")": "0.483070866369516+0.0717231592947926i", + "=IMLOG10(\"2-i\")": "0.349485002168009-0.201359598136687i", + "=IMLOG10(COMPLEX(1,-1))": "0.150514997831991-0.34109408846046i", // IMREAL "=IMREAL(\"5+2i\")": "5", "=IMREAL(\"2+2i\")": "2", @@ -248,31 +248,31 @@ func TestCalcCellValue(t *testing.T) { "=IMREAL(COMPLEX(4,1))": "4", // IMSEC "=IMSEC(0.5)": "1.13949392732455", - "=IMSEC(\"3+0.5i\")": "-0.8919131797403304+0.05875317818173977i", - "=IMSEC(\"2-i\")": "-0.4131493442669401-0.687527438655479i", - "=IMSEC(COMPLEX(1,-1))": "0.49833703055518686-0.5910838417210451i", + "=IMSEC(\"3+0.5i\")": "-0.89191317974033+0.0587531781817398i", + "=IMSEC(\"2-i\")": "-0.41314934426694-0.687527438655479i", + "=IMSEC(COMPLEX(1,-1))": "0.498337030555187-0.591083841721045i", // IMSECH "=IMSECH(0.5)": "0.886818883970074", - "=IMSECH(\"3+0.5i\")": "0.08736657796213027-0.047492549490160664i", - "=IMSECH(\"2-i\")": "0.1511762982655772+0.22697367539372157i", - "=IMSECH(COMPLEX(1,-1))": "0.49833703055518686+0.5910838417210451i", + "=IMSECH(\"3+0.5i\")": "0.0873665779621303-0.0474925494901607i", + "=IMSECH(\"2-i\")": "0.151176298265577+0.226973675393722i", + "=IMSECH(COMPLEX(1,-1))": "0.498337030555187+0.591083841721045i", // IMSIN "=IMSIN(0.5)": "0.479425538604203", - "=IMSIN(\"3+0.5i\")": "0.15913058529843999-0.5158804424525267i", - "=IMSIN(\"2-i\")": "1.4031192506220405+0.4890562590412937i", - "=IMSIN(COMPLEX(1,-1))": "1.2984575814159773-0.6349639147847361i", + "=IMSIN(\"3+0.5i\")": "0.15913058529844-0.515880442452527i", + "=IMSIN(\"2-i\")": "1.40311925062204+0.489056259041294i", + "=IMSIN(COMPLEX(1,-1))": "1.29845758141598-0.634963914784736i", // IMSINH "=IMSINH(-0)": "0", "=IMSINH(0.5)": "0.521095305493747", - "=IMSINH(\"3+0.5i\")": "8.791512343493714+4.82669427481082i", - "=IMSINH(\"2-i\")": "1.9596010414216063-3.165778513216168i", - "=IMSINH(COMPLEX(1,-1))": "0.6349639147847361-1.2984575814159773i", + "=IMSINH(\"3+0.5i\")": "8.79151234349371+4.82669427481082i", + "=IMSINH(\"2-i\")": "1.95960104142161-3.16577851321617i", + "=IMSINH(COMPLEX(1,-1))": "0.634963914784736-1.29845758141598i", // IMSQRT - "=IMSQRT(\"i\")": "0.7071067811865476+0.7071067811865476i", - "=IMSQRT(\"2-i\")": "1.455346690225355-0.34356074972251244i", - "=IMSQRT(\"5+2i\")": "2.27872385417085+0.4388421169022545i", + "=IMSQRT(\"i\")": "0.707106781186548+0.707106781186548i", + "=IMSQRT(\"2-i\")": "1.45534669022535-0.343560749722512i", + "=IMSQRT(\"5+2i\")": "2.27872385417085+0.438842116902254i", "=IMSQRT(6)": "2.44948974278318", - "=IMSQRT(\"-2-4i\")": "1.1117859405028423-1.7989074399478673i", + "=IMSQRT(\"-2-4i\")": "1.11178594050284-1.79890743994787i", // IMSUB "=IMSUB(\"5+i\",\"1+4i\")": "4-3i", "=IMSUB(\"9+2i\",6)": "3+2i", @@ -283,9 +283,9 @@ func TestCalcCellValue(t *testing.T) { // IMTAN "=IMTAN(-0)": "0", "=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", + "=IMTAN(\"3+0.5i\")": "-0.111621050771583+0.469469993425885i", + "=IMTAN(\"2-i\")": "-0.243458201185725-1.16673625724092i", + "=IMTAN(COMPLEX(1,-1))": "0.271752585319512-1.08392332733869i", // OCT2BIN "=OCT2BIN(\"5\")": "101", "=OCT2BIN(\"0000000001\")": "1", @@ -555,16 +555,16 @@ func TestCalcCellValue(t *testing.T) { "=LOG10(25)": "1.39794000867204", "=LOG10(LOG10(100))": "0.301029995663981", // IMLOG2 - "=IMLOG2(\"5+2i\")": "2.4289904975637864+0.5489546632866347i", - "=IMLOG2(\"2-i\")": "1.1609640474436813-0.6689021062254881i", + "=IMLOG2(\"5+2i\")": "2.42899049756379+0.548954663286635i", + "=IMLOG2(\"2-i\")": "1.16096404744368-0.668902106225488i", "=IMLOG2(6)": "2.58496250072116", - "=IMLOG2(\"3i\")": "1.584962500721156+2.266180070913597i", - "=IMLOG2(\"4+i\")": "2.04373142062517+0.3534295024167349i", + "=IMLOG2(\"3i\")": "1.58496250072116+2.2661800709136i", + "=IMLOG2(\"4+i\")": "2.04373142062517+0.353429502416735i", // IMPOWER - "=IMPOWER(\"2-i\",2)": "3.000000000000001-4i", - "=IMPOWER(\"2-i\",3)": "2.0000000000000018-11.000000000000002i", + "=IMPOWER(\"2-i\",2)": "3-4i", + "=IMPOWER(\"2-i\",3)": "2-11i", "=IMPOWER(9,0.5)": "3", - "=IMPOWER(\"2+4i\",-2)": "-0.029999999999999985-0.039999999999999994i", + "=IMPOWER(\"2+4i\",-2)": "-0.03-0.04i", // IMPRODUCT "=IMPRODUCT(3,6)": "18", `=IMPRODUCT("",3,SUM(6))`: "18", @@ -819,6 +819,19 @@ func TestCalcCellValue(t *testing.T) { "=BINOM.DIST(10,100,0.5,TRUE)": "1.53164508771899E-17", "=BINOM.DIST(50,100,0.5,TRUE)": "0.539794618693589", "=BINOM.DIST(65,100,0.5,TRUE)": "0.999105034804256", + // BINOM.DIST.RANGE + "=BINOM.DIST.RANGE(100,0.5,0,40)": "0.0284439668204904", + "=BINOM.DIST.RANGE(100,0.5,45,55)": "0.728746975926165", + "=BINOM.DIST.RANGE(100,0.5,50,100)": "0.539794618693589", + "=BINOM.DIST.RANGE(100,0.5,50)": "0.0795892373871787", + // BINOM.INV + "=BINOM.INV(0,0.5,0.75)": "0", + "=BINOM.INV(0.1,0.1,0.75)": "0", + "=BINOM.INV(0.6,0.4,0.75)": "0", + "=BINOM.INV(2,0.4,0.75)": "1", + "=BINOM.INV(100,0.5,20%)": "46", + "=BINOM.INV(100,0.5,50%)": "50", + "=BINOM.INV(100,0.5,90%)": "56", // CHIDIST "=CHIDIST(0.5,3)": "0.918891411654676", "=CHIDIST(8,3)": "0.0460117056892315", @@ -2436,6 +2449,30 @@ func TestCalcCellValue(t *testing.T) { "=BINOM.DIST(110,100,0.5,FALSE)": "#NUM!", "=BINOM.DIST(10,100,-1,FALSE)": "#NUM!", "=BINOM.DIST(10,100,2,FALSE)": "#NUM!", + // BINOM.DIST.RANGE + "=BINOM.DIST.RANGE()": "BINOM.DIST.RANGE requires at least 3 arguments", + "=BINOM.DIST.RANGE(100,0.5,0,40,0)": "BINOM.DIST.RANGE requires at most 4 arguments", + "=BINOM.DIST.RANGE(\"\",0.5,0,40)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.DIST.RANGE(100,\"\",0,40)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.DIST.RANGE(100,0.5,\"\",40)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.DIST.RANGE(100,0.5,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.DIST.RANGE(100,-1,0,40)": "#NUM!", + "=BINOM.DIST.RANGE(100,2,0,40)": "#NUM!", + "=BINOM.DIST.RANGE(100,0.5,-1,40)": "#NUM!", + "=BINOM.DIST.RANGE(100,0.5,110,40)": "#NUM!", + "=BINOM.DIST.RANGE(100,0.5,0,-1)": "#NUM!", + "=BINOM.DIST.RANGE(100,0.5,0,110)": "#NUM!", + // BINOM.INV + "=BINOM.INV()": "BINOM.INV requires 3 numeric arguments", + "=BINOM.INV(\"\",0.5,20%)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.INV(100,\"\",20%)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.INV(100,0.5,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.INV(-1,0.5,20%)": "#NUM!", + "=BINOM.INV(100,-1,20%)": "#NUM!", + "=BINOM.INV(100,2,20%)": "#NUM!", + "=BINOM.INV(100,0.5,-1)": "#NUM!", + "=BINOM.INV(100,0.5,2)": "#NUM!", + "=BINOM.INV(1,1,20%)": "#NUM!", // CHIDIST "=CHIDIST()": "CHIDIST requires 2 numeric arguments", "=CHIDIST(\"\",3)": "strconv.ParseFloat: parsing \"\": invalid syntax", -- cgit v1.2.1