summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go257
-rw-r--r--calc_test.go123
2 files changed, 282 insertions, 98 deletions
diff --git a/calc.go b/calc.go
index 4463373..38be45c 100644
--- a/calc.go
+++ b/calc.go
@@ -342,6 +342,8 @@ type formulaFuncs struct {
// BIN2OCT
// BINOMDIST
// BINOM.DIST
+// BINOM.DIST.RANGE
+// BINOM.INV
// BITAND
// BITLSHIFT
// BITOR
@@ -1985,13 +1987,27 @@ func (fn *formulaFuncs) COMPLEX(argsList *list.List) formulaArg {
return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
}
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(complex(real.Number, i.Number)), suffix))
+ return newStringFormulaArg(cmplx2str(complex(real.Number, i.Number), suffix))
}
// cmplx2str replace complex number string characters.
-func cmplx2str(c, suffix string) string {
- if c == "(0+0i)" || c == "(-0+0i)" || c == "(0-0i)" || c == "(-0-0i)" {
- return "0"
+func cmplx2str(num complex128, suffix string) string {
+ c := fmt.Sprint(num)
+ realPart, imagPart := fmt.Sprint(real(num)), fmt.Sprint(imag(num))
+ isNum, i := isNumeric(realPart)
+ if isNum && i > 15 {
+ realPart = roundPrecision(realPart, -1)
+ }
+ isNum, i = isNumeric(imagPart)
+ if isNum && i > 15 {
+ imagPart = roundPrecision(imagPart, -1)
+ }
+ c = realPart
+ if imag(num) > 0 {
+ c += "+"
+ }
+ if imag(num) != 0 {
+ c += imagPart + "i"
}
c = strings.TrimPrefix(c, "(")
c = strings.TrimPrefix(c, "+0+")
@@ -2325,7 +2341,8 @@ func (fn *formulaFuncs) IMABS(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMABS requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2341,7 +2358,8 @@ func (fn *formulaFuncs) IMAGINARY(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMAGINARY requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2357,7 +2375,8 @@ func (fn *formulaFuncs) IMARGUMENT(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMARGUMENT requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2373,11 +2392,12 @@ func (fn *formulaFuncs) IMCONJUGATE(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMCONJUGATE requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(cmplx.Conj(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(cmplx.Conj(inumber), value[len(value)-1:]))
}
// IMCOS function returns the cosine of a supplied complex number. The syntax
@@ -2389,11 +2409,12 @@ func (fn *formulaFuncs) IMCOS(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMCOS requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(cmplx.Cos(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(cmplx.Cos(inumber), value[len(value)-1:]))
}
// IMCOSH function returns the hyperbolic cosine of a supplied complex number. The syntax
@@ -2405,11 +2426,12 @@ func (fn *formulaFuncs) IMCOSH(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMCOSH requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(cmplx.Cosh(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(cmplx.Cosh(inumber), value[len(value)-1:]))
}
// IMCOT function returns the cotangent of a supplied complex number. The syntax
@@ -2421,11 +2443,12 @@ func (fn *formulaFuncs) IMCOT(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMCOT requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(cmplx.Cot(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(cmplx.Cot(inumber), value[len(value)-1:]))
}
// IMCSC function returns the cosecant of a supplied complex number. The syntax
@@ -2437,7 +2460,8 @@ func (fn *formulaFuncs) IMCSC(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMCSC requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2445,7 +2469,7 @@ func (fn *formulaFuncs) IMCSC(argsList *list.List) formulaArg {
if cmplx.IsInf(num) {
return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(num), "i"))
+ return newStringFormulaArg(cmplx2str(num, value[len(value)-1:]))
}
// IMCSCH function returns the hyperbolic cosecant of a supplied complex
@@ -2457,7 +2481,8 @@ func (fn *formulaFuncs) IMCSCH(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMCSCH requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2465,7 +2490,7 @@ func (fn *formulaFuncs) IMCSCH(argsList *list.List) formulaArg {
if cmplx.IsInf(num) {
return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(num), "i"))
+ return newStringFormulaArg(cmplx2str(num, value[len(value)-1:]))
}
// IMDIV function calculates the quotient of two complex numbers (i.e. divides
@@ -2477,7 +2502,8 @@ func (fn *formulaFuncs) IMDIV(argsList *list.List) formulaArg {
if argsList.Len() != 2 {
return newErrorFormulaArg(formulaErrorVALUE, "IMDIV requires 2 arguments")
}
- inumber1, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber1, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2489,7 +2515,7 @@ func (fn *formulaFuncs) IMDIV(argsList *list.List) formulaArg {
if cmplx.IsInf(num) {
return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(num), "i"))
+ return newStringFormulaArg(cmplx2str(num, value[len(value)-1:]))
}
// IMEXP function returns the exponential of a supplied complex number. The
@@ -2501,11 +2527,12 @@ func (fn *formulaFuncs) IMEXP(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMEXP requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(cmplx.Exp(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(cmplx.Exp(inumber), value[len(value)-1:]))
}
// IMLN function returns the natural logarithm of a supplied complex number.
@@ -2517,7 +2544,8 @@ func (fn *formulaFuncs) IMLN(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMLN requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2525,7 +2553,7 @@ func (fn *formulaFuncs) IMLN(argsList *list.List) formulaArg {
if cmplx.IsInf(num) {
return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(num), "i"))
+ return newStringFormulaArg(cmplx2str(num, value[len(value)-1:]))
}
// IMLOG10 function returns the common (base 10) logarithm of a supplied
@@ -2537,7 +2565,8 @@ func (fn *formulaFuncs) IMLOG10(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMLOG10 requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2545,7 +2574,7 @@ func (fn *formulaFuncs) IMLOG10(argsList *list.List) formulaArg {
if cmplx.IsInf(num) {
return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(num), "i"))
+ return newStringFormulaArg(cmplx2str(num, value[len(value)-1:]))
}
// IMLOG2 function calculates the base 2 logarithm of a supplied complex
@@ -2557,7 +2586,8 @@ func (fn *formulaFuncs) IMLOG2(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMLOG2 requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2565,7 +2595,7 @@ func (fn *formulaFuncs) IMLOG2(argsList *list.List) formulaArg {
if cmplx.IsInf(num) {
return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(num/cmplx.Log(2)), "i"))
+ return newStringFormulaArg(cmplx2str(num/cmplx.Log(2), value[len(value)-1:]))
}
// IMPOWER function returns a supplied complex number, raised to a given
@@ -2577,7 +2607,8 @@ func (fn *formulaFuncs) IMPOWER(argsList *list.List) formulaArg {
if argsList.Len() != 2 {
return newErrorFormulaArg(formulaErrorVALUE, "IMPOWER requires 2 arguments")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
@@ -2592,7 +2623,7 @@ func (fn *formulaFuncs) IMPOWER(argsList *list.List) formulaArg {
if cmplx.IsInf(num) {
return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(num), "i"))
+ return newStringFormulaArg(cmplx2str(num, value[len(value)-1:]))
}
// IMPRODUCT function calculates the product of two or more complex numbers.
@@ -2631,7 +2662,7 @@ func (fn *formulaFuncs) IMPRODUCT(argsList *list.List) formulaArg {
}
}
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(product), "i"))
+ return newStringFormulaArg(cmplx2str(product, "i"))
}
// IMREAL function returns the real coefficient of a supplied complex number.
@@ -2643,11 +2674,12 @@ func (fn *formulaFuncs) IMREAL(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMREAL requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(real(inumber)), "i"))
+ return newStringFormulaArg(fmt.Sprint(real(inumber)))
}
// IMSEC function returns the secant of a supplied complex number. The syntax
@@ -2659,11 +2691,12 @@ func (fn *formulaFuncs) IMSEC(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMSEC requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(1/cmplx.Cos(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(1/cmplx.Cos(inumber), value[len(value)-1:]))
}
// IMSECH function returns the hyperbolic secant of a supplied complex number.
@@ -2675,11 +2708,12 @@ func (fn *formulaFuncs) IMSECH(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMSECH requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(1/cmplx.Cosh(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(1/cmplx.Cosh(inumber), value[len(value)-1:]))
}
// IMSIN function returns the Sine of a supplied complex number. The syntax of
@@ -2691,11 +2725,12 @@ func (fn *formulaFuncs) IMSIN(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMSIN requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(cmplx.Sin(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(cmplx.Sin(inumber), value[len(value)-1:]))
}
// IMSINH function returns the hyperbolic sine of a supplied complex number.
@@ -2707,11 +2742,12 @@ func (fn *formulaFuncs) IMSINH(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMSINH requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(cmplx.Sinh(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(cmplx.Sinh(inumber), value[len(value)-1:]))
}
// IMSQRT function returns the square root of a supplied complex number. The
@@ -2723,11 +2759,12 @@ func (fn *formulaFuncs) IMSQRT(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMSQRT requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(cmplx.Sqrt(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(cmplx.Sqrt(inumber), value[len(value)-1:]))
}
// IMSUB function calculates the difference between two complex numbers
@@ -2748,7 +2785,7 @@ func (fn *formulaFuncs) IMSUB(argsList *list.List) formulaArg {
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(i1-i2), "i"))
+ return newStringFormulaArg(cmplx2str(i1-i2, "i"))
}
// IMSUM function calculates the sum of two or more complex numbers. The
@@ -2769,7 +2806,7 @@ func (fn *formulaFuncs) IMSUM(argsList *list.List) formulaArg {
}
result += num
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(result), "i"))
+ return newStringFormulaArg(cmplx2str(result, "i"))
}
// IMTAN function returns the tangent of a supplied complex number. The syntax
@@ -2781,11 +2818,12 @@ func (fn *formulaFuncs) IMTAN(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "IMTAN requires 1 argument")
}
- inumber, err := strconv.ParseComplex(str2cmplx(argsList.Front().Value.(formulaArg).Value()), 128)
+ value := argsList.Front().Value.(formulaArg).Value()
+ inumber, err := strconv.ParseComplex(str2cmplx(value), 128)
if err != nil {
return newErrorFormulaArg(formulaErrorNUM, err.Error())
}
- return newStringFormulaArg(cmplx2str(fmt.Sprint(cmplx.Tan(inumber)), "i"))
+ return newStringFormulaArg(cmplx2str(cmplx.Tan(inumber), value[len(value)-1:]))
}
// OCT2BIN function converts an Octal (Base 8) number into a Binary (Base 2)
@@ -5652,7 +5690,8 @@ func logBeta(a, b float64) float64 {
}
if p >= 10.0 {
corr = lgammacor(p) + lgammacor(q) - lgammacor(p+q)
- return math.Log(q)*-0.5 + 0.918938533204672741780329736406 + corr + (p-0.5)*math.Log(p/(p+q)) + q*logrelerr(-p/(p+q))
+ f1 := q * logrelerr(-p/(p+q))
+ return math.Log(q)*-0.5 + 0.918938533204672741780329736406 + corr + (p-0.5)*math.Log(p/(p+q)) + math.Nextafter(f1, f1)
}
if q >= 10 {
corr = lgammacor(q) - lgammacor(p+q)
@@ -5970,6 +6009,108 @@ func (fn *formulaFuncs) BINOMDIST(argsList *list.List) formulaArg {
return newNumberFormulaArg(binomdist(s.Number, trials.Number, probability.Number))
}
+// BINOMdotDISTdotRANGE function returns the Binomial Distribution probability
+// for the number of successes from a specified number of trials falling into
+// a specified range.
+//
+// BINOM.DIST.RANGE(trials,probability_s,number_s,[number_s2])
+//
+func (fn *formulaFuncs) BINOMdotDISTdotRANGE(argsList *list.List) formulaArg {
+ if argsList.Len() < 3 {
+ return newErrorFormulaArg(formulaErrorVALUE, "BINOM.DIST.RANGE requires at least 3 arguments")
+ }
+ if argsList.Len() > 4 {
+ return newErrorFormulaArg(formulaErrorVALUE, "BINOM.DIST.RANGE requires at most 4 arguments")
+ }
+ trials := argsList.Front().Value.(formulaArg).ToNumber()
+ if trials.Type != ArgNumber {
+ return trials
+ }
+ probability := argsList.Front().Next().Value.(formulaArg).ToNumber()
+ if probability.Type != ArgNumber {
+ return probability
+ }
+ if probability.Number < 0 || probability.Number > 1 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ num1 := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
+ if num1.Type != ArgNumber {
+ return num1
+ }
+ if num1.Number < 0 || num1.Number > trials.Number {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ num2 := num1
+ if argsList.Len() > 3 {
+ if num2 = argsList.Back().Value.(formulaArg).ToNumber(); num2.Type != ArgNumber {
+ return num2
+ }
+ }
+ if num2.Number < 0 || num2.Number > trials.Number {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ sumn := 0.0
+ for i := num1.Number; i <= num2.Number; i++ {
+ sumn += binomdist(i, trials.Number, probability.Number)
+ }
+ return newNumberFormulaArg(sumn)
+}
+
+// binominv implement inverse of the binomial distribution calcuation.
+func binominv(n, p, alpha float64) float64 {
+ q, i, sum, max := 1-p, 0.0, 0.0, 0.0
+ n = math.Floor(n)
+ if q > p {
+ factor := math.Pow(q, n)
+ sum = factor
+ for i = 0; i < n && sum < alpha; i++ {
+ factor *= (n - i) / (i + 1) * p / q
+ sum += factor
+ }
+ return i
+ }
+ factor := math.Pow(p, n)
+ sum, max = 1-factor, n
+ for i = 0; i < max && sum >= alpha; i++ {
+ factor *= (n - i) / (i + 1) * q / p
+ sum -= factor
+ }
+ return n - i
+}
+
+// BINOMdotINV function returns the inverse of the Cumulative Binomial
+// Distribution. The syntax of the function is:
+//
+// BINOM.INV(trials,probability_s,alpha)
+//
+func (fn *formulaFuncs) BINOMdotINV(argsList *list.List) formulaArg {
+ if argsList.Len() != 3 {
+ return newErrorFormulaArg(formulaErrorVALUE, "BINOM.INV requires 3 numeric arguments")
+ }
+ trials := argsList.Front().Value.(formulaArg).ToNumber()
+ if trials.Type != ArgNumber {
+ return trials
+ }
+ if trials.Number < 0 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ probability := argsList.Front().Next().Value.(formulaArg).ToNumber()
+ if probability.Type != ArgNumber {
+ return probability
+ }
+ if probability.Number <= 0 || probability.Number >= 1 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ alpha := argsList.Back().Value.(formulaArg).ToNumber()
+ if alpha.Type != ArgNumber {
+ return alpha
+ }
+ if alpha.Number <= 0 || alpha.Number >= 1 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ return newNumberFormulaArg(binominv(trials.Number, probability.Number, alpha.Number))
+}
+
// CHIDIST function calculates the right-tailed probability of the chi-square
// distribution. The syntax of the function is:
//
@@ -7143,8 +7284,12 @@ func norminv(p float64) (float64, error) {
// Rational approximation for central region.
q := p - 0.5
r := q * q
- return (((((a[1]*r+a[2])*r+a[3])*r+a[4])*r+a[5])*r + a[6]) * q /
- (((((b[1]*r+b[2])*r+b[3])*r+b[4])*r+b[5])*r + 1), nil
+ f1 := ((((a[1]*r+a[2])*r+a[3])*r+a[4])*r + a[5]) * r
+ f2 := (b[1]*r + b[2]) * r
+ f3 := ((math.Nextafter(f2, f2)+b[3])*r + b[4]) * r
+ f4 := (math.Nextafter(f3, f3) + b[5]) * r
+ return (math.Nextafter(f1, f1) + a[6]) * q /
+ (math.Nextafter(f4, f4) + 1), nil
} else if pHigh < p && p < 1 {
// Rational approximation for upper region.
q := math.Sqrt(-2 * math.Log(1-p))
@@ -7506,7 +7651,7 @@ func (fn *formulaFuncs) PERCENTILEdotEXC(argsList *list.List) formulaArg {
idx := k.Number * (float64(cnt) + 1)
base := math.Floor(idx)
next := base - 1
- proportion := idx - base
+ proportion := math.Nextafter(idx, idx) - base
return newNumberFormulaArg(numbers[int(next)] + ((numbers[int(base)] - numbers[int(next)]) * proportion))
}
@@ -7559,7 +7704,7 @@ func (fn *formulaFuncs) PERCENTILE(argsList *list.List) formulaArg {
return newNumberFormulaArg(numbers[int(idx)])
}
next := base + 1
- proportion := idx - base
+ proportion := math.Nextafter(idx, idx) - base
return newNumberFormulaArg(numbers[int(base)] + ((numbers[int(next)] - numbers[int(base)]) * proportion))
}
@@ -14052,7 +14197,8 @@ func (fn *formulaFuncs) yield(settlement, maturity, rate, pr, redemption, freque
yield2 = yieldN
price2 = priceN
}
- yieldN.Number = yield2.Number - (yield2.Number-yield1.Number)*((pr.Number-price2.Number)/(price1.Number-price2.Number))
+ f1 := (yield2.Number - yield1.Number) * ((pr.Number - price2.Number) / (price1.Number - price2.Number))
+ yieldN.Number = yield2.Number - math.Nextafter(f1, f1)
}
}
return yieldN
@@ -14202,7 +14348,8 @@ func (fn *formulaFuncs) YIELDMAT(argsList *list.List) formulaArg {
}
dis := yearFrac(issue.Number, settlement.Number, int(basis.Number))
dsm := yearFrac(settlement.Number, maturity.Number, int(basis.Number))
- result := 1 + dim.Number*rate.Number
+ f1 := dim.Number * rate.Number
+ result := 1 + math.Nextafter(f1, f1)
result /= pr.Number/100 + dis.Number*rate.Number
result--
result /= dsm.Number
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",
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 04:31:03 +0000