diff options
| -rw-r--r-- | calc.go | 142 | ||||
| -rw-r--r-- | calc_test.go | 65 |
2 files changed, 197 insertions, 10 deletions
@@ -334,11 +334,14 @@ type formulaFuncs struct { // BESSELK // BESSELY // BETADIST +// BETA.DIST // BETAINV // BETA.INV // BIN2DEC // BIN2HEX // BIN2OCT +// BINOMDIST +// BINOM.DIST // BITAND // BITLSHIFT // BITOR @@ -686,7 +689,7 @@ func (f *File) CalcCellValue(sheet, cell string) (result string, err error) { } result = token.TValue isNum, precision := isNumeric(result) - if isNum && precision > 15 { + if isNum && (precision > 15 || precision == 0) { num := roundPrecision(result, -1) result = strings.ToUpper(num) } @@ -5406,6 +5409,74 @@ func getBetaDist(fXin, fAlpha, fBeta float64) float64 { return fResult } +// prepareBETAdotDISTArgs checking and prepare arguments for the formula +// function BETA.DIST. +func (fn *formulaFuncs) prepareBETAdotDISTArgs(argsList *list.List) formulaArg { + if argsList.Len() < 4 { + return newErrorFormulaArg(formulaErrorVALUE, "BETA.DIST requires at least 4 arguments") + } + if argsList.Len() > 6 { + return newErrorFormulaArg(formulaErrorVALUE, "BETA.DIST requires at most 6 arguments") + } + x := argsList.Front().Value.(formulaArg).ToNumber() + if x.Type != ArgNumber { + return x + } + alpha := argsList.Front().Next().Value.(formulaArg).ToNumber() + if alpha.Type != ArgNumber { + return alpha + } + beta := argsList.Front().Next().Next().Value.(formulaArg).ToNumber() + if beta.Type != ArgNumber { + return beta + } + if alpha.Number <= 0 || beta.Number <= 0 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + cumulative := argsList.Front().Next().Next().Next().Value.(formulaArg).ToBool() + if cumulative.Type != ArgNumber { + return cumulative + } + a, b := newNumberFormulaArg(0), newNumberFormulaArg(1) + if argsList.Len() > 4 { + if a = argsList.Front().Next().Next().Next().Next().Value.(formulaArg).ToNumber(); a.Type != ArgNumber { + return a + } + } + if argsList.Len() == 6 { + if b = argsList.Back().Value.(formulaArg).ToNumber(); b.Type != ArgNumber { + return b + } + } + return newListFormulaArg([]formulaArg{x, alpha, beta, cumulative, a, b}) +} + +// BETAdotDIST function calculates the cumulative beta distribution function +// or the probability density function of the Beta distribution, for a +// supplied set of parameters. The syntax of the function is: +// +// BETA.DIST(x,alpha,beta,cumulative,[A],[B]) +// +func (fn *formulaFuncs) BETAdotDIST(argsList *list.List) formulaArg { + args := fn.prepareBETAdotDISTArgs(argsList) + if args.Type != ArgList { + return args + } + x, alpha, beta, cumulative, a, b := args.List[0], args.List[1], args.List[2], args.List[3], args.List[4], args.List[5] + if x.Number < a.Number || x.Number > b.Number { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if a.Number == b.Number { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + fScale := b.Number - a.Number + x.Number = (x.Number - a.Number) / fScale + if cumulative.Number == 1 { + return newNumberFormulaArg(getBetaDist(x.Number, alpha.Number, beta.Number)) + } + return newNumberFormulaArg(getBetaDistPDF(x.Number, alpha.Number, beta.Number) / fScale) +} + // BETADIST function calculates the cumulative beta probability density // function for a supplied set of parameters. The syntax of the function is: // @@ -5836,6 +5907,69 @@ func incompleteGamma(a, x float64) float64 { return math.Pow(x, a) * math.Exp(0-x) * summer } +// binomCoeff implement binomial coefficient calcuation. +func binomCoeff(n, k float64) float64 { + return fact(n) / (fact(k) * fact(n-k)) +} + +// binomdist implement binomial distribution calcuation. +func binomdist(x, n, p float64) float64 { + return binomCoeff(n, x) * math.Pow(p, x) * math.Pow(1-p, n-x) +} + +// BINOMfotDIST function returns the Binomial Distribution probability for a +// given number of successes from a specified number of trials. The syntax of +// the function is: +// +// BINOM.DIST(number_s,trials,probability_s,cumulative) +// +func (fn *formulaFuncs) BINOMdotDIST(argsList *list.List) formulaArg { + if argsList.Len() != 4 { + return newErrorFormulaArg(formulaErrorVALUE, "BINOM.DIST requires 4 arguments") + } + return fn.BINOMDIST(argsList) +} + +// BINOMDIST function returns the Binomial Distribution probability of a +// specified number of successes out of a specified number of trials. The +// syntax of the function is: +// +// BINOMDIST(number_s,trials,probability_s,cumulative) +// +func (fn *formulaFuncs) BINOMDIST(argsList *list.List) formulaArg { + if argsList.Len() != 4 { + return newErrorFormulaArg(formulaErrorVALUE, "BINOMDIST requires 4 arguments") + } + var s, trials, probability, cumulative formulaArg + if s = argsList.Front().Value.(formulaArg).ToNumber(); s.Type != ArgNumber { + return s + } + if trials = argsList.Front().Next().Value.(formulaArg).ToNumber(); trials.Type != ArgNumber { + return trials + } + if s.Number < 0 || s.Number > trials.Number { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if probability = argsList.Back().Prev().Value.(formulaArg).ToNumber(); probability.Type != ArgNumber { + return probability + } + + if probability.Number < 0 || probability.Number > 1 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if cumulative = argsList.Back().Value.(formulaArg).ToBool(); cumulative.Type == ArgError { + return cumulative + } + if cumulative.Number == 1 { + bm := 0.0 + for i := 0; i <= int(s.Number); i++ { + bm += binomdist(float64(i), trials.Number, probability.Number) + } + return newNumberFormulaArg(bm) + } + return newNumberFormulaArg(binomdist(s.Number, trials.Number, probability.Number)) +} + // CHIDIST function calculates the right-tailed probability of the chi-square // distribution. The syntax of the function is: // @@ -11641,7 +11775,7 @@ func (fn *formulaFuncs) AMORLINC(argsList *list.List) formulaArg { if int(period.Number) <= periods { return newNumberFormulaArg(rate2) } else if int(period.Number)-1 == periods { - return newNumberFormulaArg(delta - rate2*float64(periods) - rate1) + return newNumberFormulaArg(delta - rate2*float64(periods) - math.Nextafter(rate1, rate1)) } return newNumberFormulaArg(0) } @@ -13334,7 +13468,9 @@ func (fn *formulaFuncs) rate(nper, pmt, pv, fv, t, guess formulaArg, argsList *l rt := rate*t.Number + 1 p0 := pmt.Number * (t1 - 1) f1 := fv.Number + t1*pv.Number + p0*rt/rate - f2 := nper.Number*t2*pv.Number - p0*rt/math.Pow(rate, 2) + n1 := nper.Number * t2 * pv.Number + n2 := p0 * rt / math.Pow(rate, 2) + f2 := math.Nextafter(n1, n1) - math.Nextafter(n2, n2) f3 := (nper.Number*pmt.Number*t2*rt + p0*t.Number) / rate delta := f1 / (f2 + f3) if math.Abs(delta) < epsMax { diff --git a/calc_test.go b/calc_test.go index d350037..e0cd0d0 100644 --- a/calc_test.go +++ b/calc_test.go @@ -784,6 +784,9 @@ func TestCalcCellValue(t *testing.T) { "=AVERAGEA(A1)": "1", "=AVERAGEA(A1:A2)": "1.5", "=AVERAGEA(D2:F9)": "12671.375", + // BETA.DIST + "=BETA.DIST(0.4,4,5,TRUE,0,1)": "0.4059136", + "=BETA.DIST(0.6,4,5,FALSE,0,1)": "1.548288", // BETADIST "=BETADIST(0.4,4,5)": "0.4059136", "=BETADIST(0.4,4,5,0,1)": "0.4059136", @@ -796,12 +799,26 @@ func TestCalcCellValue(t *testing.T) { "=BETADIST(0.4,2,100)": "1", "=BETADIST(0.75,3,4)": "0.96240234375", "=BETADIST(0.2,0.7,4)": "0.71794309318323", - "=BETADIST(0.01,3,4)": "1.9553589999999998e-05", + "=BETADIST(0.01,3,4)": "1.955359E-05", "=BETADIST(0.75,130,140)": "1", // BETAINV "=BETAINV(0.2,4,5,0,1)": "0.303225844664082", // BETA.INV "=BETA.INV(0.2,4,5,0,1)": "0.303225844664082", + // BINOMDIST + "=BINOMDIST(10,100,0.5,FALSE)": "1.36554263874631E-17", + "=BINOMDIST(50,100,0.5,FALSE)": "0.0795892373871787", + "=BINOMDIST(65,100,0.5,FALSE)": "0.000863855665741652", + "=BINOMDIST(10,100,0.5,TRUE)": "1.53164508771899E-17", + "=BINOMDIST(50,100,0.5,TRUE)": "0.539794618693589", + "=BINOMDIST(65,100,0.5,TRUE)": "0.999105034804256", + // BINOM.DIST + "=BINOM.DIST(10,100,0.5,FALSE)": "1.36554263874631E-17", + "=BINOM.DIST(50,100,0.5,FALSE)": "0.0795892373871787", + "=BINOM.DIST(65,100,0.5,FALSE)": "0.000863855665741652", + "=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", // CHIDIST "=CHIDIST(0.5,3)": "0.918891411654676", "=CHIDIST(8,3)": "0.0460117056892315", @@ -1468,7 +1485,7 @@ func TestCalcCellValue(t *testing.T) { "=UPPER(\"TEST 123\")": "TEST 123", // VALUE "=VALUE(\"50\")": "50", - "=VALUE(\"1.0E-07\")": "1e-07", + "=VALUE(\"1.0E-07\")": "1E-07", "=VALUE(\"5,000\")": "5000", "=VALUE(\"20%\")": "0.2", "=VALUE(\"12:00:00\")": "0.5", @@ -2341,6 +2358,25 @@ func TestCalcCellValue(t *testing.T) { "=AVERAGE(H1)": "AVERAGE divide by zero", // AVERAGEA "=AVERAGEA(H1)": "AVERAGEA divide by zero", + // AVERAGEIF + "=AVERAGEIF()": "AVERAGEIF requires at least 2 arguments", + "=AVERAGEIF(H1,\"\")": "AVERAGEIF divide by zero", + "=AVERAGEIF(D1:D3,\"Month\",D1:D3)": "AVERAGEIF divide by zero", + "=AVERAGEIF(C1:C3,\"Month\",D1:D3)": "AVERAGEIF divide by zero", + // BETA.DIST + "=BETA.DIST()": "BETA.DIST requires at least 4 arguments", + "=BETA.DIST(0.4,4,5,TRUE,0,1,0)": "BETA.DIST requires at most 6 arguments", + "=BETA.DIST(\"\",4,5,TRUE,0,1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BETA.DIST(0.4,\"\",5,TRUE,0,1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BETA.DIST(0.4,4,\"\",TRUE,0,1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BETA.DIST(0.4,4,5,\"\",0,1)": "strconv.ParseBool: parsing \"\": invalid syntax", + "=BETA.DIST(0.4,4,5,TRUE,\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BETA.DIST(0.4,4,5,TRUE,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BETA.DIST(0.4,0,5,TRUE,0,1)": "#NUM!", + "=BETA.DIST(0.4,4,0,TRUE,0,0)": "#NUM!", + "=BETA.DIST(0.4,4,5,TRUE,0.5,1)": "#NUM!", + "=BETA.DIST(0.4,4,5,TRUE,0,0.3)": "#NUM!", + "=BETA.DIST(0.4,4,5,TRUE,0.4,0.4)": "#NUM!", // BETADIST "=BETADIST()": "BETADIST requires at least 3 arguments", "=BETADIST(0.4,4,5,0,1,0)": "BETADIST requires at most 5 arguments", @@ -2380,11 +2416,26 @@ func TestCalcCellValue(t *testing.T) { "=BETA.INV(0.2,0,5,0,1)": "#NUM!", "=BETA.INV(0.2,4,0,0,1)": "#NUM!", "=BETA.INV(0.2,4,5,2,2)": "#NUM!", - // AVERAGEIF - "=AVERAGEIF()": "AVERAGEIF requires at least 2 arguments", - "=AVERAGEIF(H1,\"\")": "AVERAGEIF divide by zero", - "=AVERAGEIF(D1:D3,\"Month\",D1:D3)": "AVERAGEIF divide by zero", - "=AVERAGEIF(C1:C3,\"Month\",D1:D3)": "AVERAGEIF divide by zero", + // BINOMDIST + "=BINOMDIST()": "BINOMDIST requires 4 arguments", + "=BINOMDIST(\"\",100,0.5,FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOMDIST(10,\"\",0.5,FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOMDIST(10,100,\"\",FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOMDIST(10,100,0.5,\"\")": "strconv.ParseBool: parsing \"\": invalid syntax", + "=BINOMDIST(-1,100,0.5,FALSE)": "#NUM!", + "=BINOMDIST(110,100,0.5,FALSE)": "#NUM!", + "=BINOMDIST(10,100,-1,FALSE)": "#NUM!", + "=BINOMDIST(10,100,2,FALSE)": "#NUM!", + // BINOM.DIST + "=BINOM.DIST()": "BINOM.DIST requires 4 arguments", + "=BINOM.DIST(\"\",100,0.5,FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.DIST(10,\"\",0.5,FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.DIST(10,100,\"\",FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=BINOM.DIST(10,100,0.5,\"\")": "strconv.ParseBool: parsing \"\": invalid syntax", + "=BINOM.DIST(-1,100,0.5,FALSE)": "#NUM!", + "=BINOM.DIST(110,100,0.5,FALSE)": "#NUM!", + "=BINOM.DIST(10,100,-1,FALSE)": "#NUM!", + "=BINOM.DIST(10,100,2,FALSE)": "#NUM!", // CHIDIST "=CHIDIST()": "CHIDIST requires 2 numeric arguments", "=CHIDIST(\"\",3)": "strconv.ParseFloat: parsing \"\": invalid syntax", |