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

1 files changed, 80 insertions, 43 deletions

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",