diff options
| -rw-r--r-- | calc.go | 44 | ||||
| -rw-r--r-- | calc_test.go | 58 |
2 files changed, 101 insertions, 1 deletions
@@ -385,6 +385,7 @@ type formulaFuncs struct { // COUPPCD // COVAR // COVARIANCE.P +// CRITBINOM // CSC // CSCH // CUMIPMT @@ -624,6 +625,7 @@ type formulaFuncs struct { // SUBSTITUTE // SUM // SUMIF +// SUMIFS // SUMSQ // SUMX2MY2 // SUMX2PY2 @@ -4968,6 +4970,31 @@ func (fn *formulaFuncs) SUMIF(argsList *list.List) formulaArg { return newNumberFormulaArg(sum) } +// SUMIFS function finds values in one or more supplied arrays, that satisfy a +// set of criteria, and returns the sum of the corresponding values in a +// further supplied array. The syntax of the function is: +// +// SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...) +// +func (fn *formulaFuncs) SUMIFS(argsList *list.List) formulaArg { + if argsList.Len() < 3 { + return newErrorFormulaArg(formulaErrorVALUE, "SUMIFS requires at least 3 arguments") + } + if argsList.Len()%2 != 1 { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + sum, sumRange, args := 0.0, argsList.Front().Value.(formulaArg).Matrix, []formulaArg{} + for arg := argsList.Front().Next(); arg != nil; arg = arg.Next() { + args = append(args, arg.Value.(formulaArg)) + } + for _, ref := range formulaIfsMatch(args) { + if num := sumRange[ref.Row][ref.Col].ToNumber(); num.Type == ArgNumber { + sum += num.Number + } + } + return newNumberFormulaArg(sum) +} + // SUMSQ function returns the sum of squares of a supplied set of values. The // syntax of the function is: // @@ -5956,7 +5983,7 @@ 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 +// BINOMdotDIST function returns the Binomial Distribution probability for a // given number of successes from a specified number of trials. The syntax of // the function is: // @@ -6492,6 +6519,21 @@ func (fn *formulaFuncs) COUNTIFS(argsList *list.List) formulaArg { return newNumberFormulaArg(float64(len(formulaIfsMatch(args)))) } +// CRITBINOM function returns the inverse of the Cumulative Binomial +// Distribution. I.e. for a specific number of independent trials, the +// function returns the smallest value (number of successes) for which the +// cumulative binomial distribution is greater than or equal to a specified +// value. The syntax of the function is: +// +// CRITBINOM(trials,probability_s,alpha) +// +func (fn *formulaFuncs) CRITBINOM(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "CRITBINOM requires 3 numeric arguments") + } + return fn.BINOMdotINV(argsList) +} + // DEVSQ function calculates the sum of the squared deviations from the sample // mean. The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index cb09d26..23af173 100644 --- a/calc_test.go +++ b/calc_test.go @@ -867,6 +867,14 @@ func TestCalcCellValue(t *testing.T) { "=COUNTIFS(A1:A9,2,D1:D9,\"Jan\")": "1", "=COUNTIFS(F1:F9,\">20000\",D1:D9,\"Jan\")": "4", "=COUNTIFS(F1:F9,\">60000\",D1:D9,\"Jan\")": "0", + // CRITBINOM + "=CRITBINOM(0,0.5,0.75)": "0", + "=CRITBINOM(0.1,0.1,0.75)": "0", + "=CRITBINOM(0.6,0.4,0.75)": "0", + "=CRITBINOM(2,0.4,0.75)": "1", + "=CRITBINOM(100,0.5,20%)": "46", + "=CRITBINOM(100,0.5,50%)": "50", + "=CRITBINOM(100,0.5,90%)": "56", // DEVSQ "=DEVSQ(1,3,5,2,9,7)": "47.5", "=DEVSQ(A1:D2)": "10", @@ -2514,6 +2522,17 @@ func TestCalcCellValue(t *testing.T) { // COUNTIFS "=COUNTIFS()": "COUNTIFS requires at least 2 arguments", "=COUNTIFS(A1:A9,2,D1:D9)": "#N/A", + // CRITBINOM + "=CRITBINOM()": "CRITBINOM requires 3 numeric arguments", + "=CRITBINOM(\"\",0.5,20%)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CRITBINOM(100,\"\",20%)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CRITBINOM(100,0.5,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CRITBINOM(-1,0.5,20%)": "#NUM!", + "=CRITBINOM(100,-1,20%)": "#NUM!", + "=CRITBINOM(100,2,20%)": "#NUM!", + "=CRITBINOM(100,0.5,-1)": "#NUM!", + "=CRITBINOM(100,0.5,2)": "#NUM!", + "=CRITBINOM(1,1,20%)": "#NUM!", // DEVSQ "=DEVSQ()": "DEVSQ requires at least 1 numeric argument", "=DEVSQ(D1:D2)": "#N/A", @@ -4212,6 +4231,45 @@ func TestCalcMIRR(t *testing.T) { } } +func TestCalcSUMIFS(t *testing.T) { + cellData := [][]interface{}{ + {"Quarter", "Area", "Sales Rep.", "Sales"}, + {1, "North", "Jeff", 223000}, + {1, "North", "Chris", 125000}, + {1, "South", "Carol", 456000}, + {2, "North", "Jeff", 322000}, + {2, "North", "Chris", 340000}, + {2, "South", "Carol", 198000}, + {3, "North", "Jeff", 310000}, + {3, "North", "Chris", 250000}, + {3, "South", "Carol", 460000}, + {4, "North", "Jeff", 261000}, + {4, "North", "Chris", 389000}, + {4, "South", "Carol", 305000}, + } + f := prepareCalcData(cellData) + formulaList := map[string]string{ + "=SUMIFS(D2:D13,A2:A13,1,B2:B13,\"North\")": "348000", + "=SUMIFS(D2:D13,A2:A13,\">2\",C2:C13,\"Jeff\")": "571000", + } + for formula, expected := range formulaList { + assert.NoError(t, f.SetCellFormula("Sheet1", "E1", formula)) + result, err := f.CalcCellValue("Sheet1", "E1") + assert.NoError(t, err, formula) + assert.Equal(t, expected, result, formula) + } + calcError := map[string]string{ + "=SUMIFS()": "SUMIFS requires at least 3 arguments", + "=SUMIFS(D2:D13,A2:A13,1,B2:B13)": "#N/A", + } + for formula, expected := range calcError { + assert.NoError(t, f.SetCellFormula("Sheet1", "E1", formula)) + result, err := f.CalcCellValue("Sheet1", "E1") + assert.EqualError(t, err, expected, formula) + assert.Equal(t, "", result, formula) + } +} + func TestCalcXIRR(t *testing.T) { cellData := [][]interface{}{ {-100.00, "01/01/2016"}, |