diff options
| -rw-r--r-- | calc.go | 100 | ||||
| -rw-r--r-- | calc_test.go | 25 |
2 files changed, 122 insertions, 3 deletions
@@ -328,6 +328,7 @@ type formulaFuncs struct { // AVERAGE // AVERAGEA // AVERAGEIF +// AVERAGEIFS // BASE // BESSELI // BESSELJ @@ -626,6 +627,7 @@ type formulaFuncs struct { // SUM // SUMIF // SUMIFS +// SUMPRODUCT // SUMSQ // SUMX2MY2 // SUMX2PY2 @@ -4995,6 +4997,73 @@ func (fn *formulaFuncs) SUMIFS(argsList *list.List) formulaArg { return newNumberFormulaArg(sum) } +// sumproduct is an implementation of the formula function SUMPRODUCT. +func (fn *formulaFuncs) sumproduct(argsList *list.List) formulaArg { + var ( + argType ArgType + n int + res []float64 + sum float64 + ) + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg) + if argType == ArgUnknown { + argType = token.Type + } + if token.Type != argType { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + switch token.Type { + case ArgString, ArgNumber: + if num := token.ToNumber(); num.Type == ArgNumber { + sum = fn.PRODUCT(argsList).Number + continue + } + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + case ArgMatrix: + args := token.ToList() + if res == nil { + n = len(args) + res = make([]float64, n) + for i := range res { + res[i] = 1.0 + } + } + if len(args) != n { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + for i, value := range args { + num := value.ToNumber() + if num.Type != ArgNumber && value.Value() != "" { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + res[i] = res[i] * num.Number + } + } + } + for _, r := range res { + sum += r + } + return newNumberFormulaArg(sum) +} + +// SUMPRODUCT function returns the sum of the products of the corresponding +// values in a set of supplied arrays. The syntax of the function is: +// +// SUMPRODUCT(array1,[array2],[array3],...) +// +func (fn *formulaFuncs) SUMPRODUCT(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "SUMPRODUCT requires at least 1 argument") + } + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + if token := arg.Value.(formulaArg); token.Type == ArgError { + return token + } + } + return fn.sumproduct(argsList) +} + // SUMSQ function returns the sum of squares of a supplied set of values. The // syntax of the function is: // @@ -5270,6 +5339,37 @@ func (fn *formulaFuncs) AVERAGEIF(argsList *list.List) formulaArg { return newNumberFormulaArg(sum / count) } +// AVERAGEIFS function finds entries in one or more arrays, that satisfy a set +// of supplied criteria, and returns the average (i.e. the statistical mean) +// of the corresponding values in a further supplied array. The syntax of the +// function is: +// +// AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...) +// +func (fn *formulaFuncs) AVERAGEIFS(argsList *list.List) formulaArg { + if argsList.Len() < 3 { + return newErrorFormulaArg(formulaErrorVALUE, "AVERAGEIFS 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)) + } + count := 0.0 + for _, ref := range formulaIfsMatch(args) { + if num := sumRange[ref.Row][ref.Col].ToNumber(); num.Type == ArgNumber { + sum += num.Number + count++ + } + } + if count == 0 { + return newErrorFormulaArg(formulaErrorDIV, "AVERAGEIF divide by zero") + } + return newNumberFormulaArg(sum / count) +} + // getBetaHelperContFrac continued fractions for the beta function. func getBetaHelperContFrac(fX, fA, fB float64) float64 { var a1, b1, a2, b2, fnorm, cfnew, cf, rm float64 diff --git a/calc_test.go b/calc_test.go index 23af173..4025cec 100644 --- a/calc_test.go +++ b/calc_test.go @@ -741,6 +741,12 @@ func TestCalcCellValue(t *testing.T) { `=SUMIF(E2:E9,"North 1",F2:F9)`: "66582", `=SUMIF(E2:E9,"North*",F2:F9)`: "138772", "=SUMIF(D1:D3,\"Month\",D1:D3)": "0", + // SUMPRODUCT + "=SUMPRODUCT(A1,B1)": "4", + "=SUMPRODUCT(A1:A2,B1:B2)": "14", + "=SUMPRODUCT(A1:A3,B1:B3)": "14", + "=SUMPRODUCT(A1:B3)": "15", + "=SUMPRODUCT(A1:A3,B1:B3,B2:B4)": "20", // SUMSQ "=SUMSQ(A1:A4)": "14", "=SUMSQ(A1,B1,A2,B2,6)": "82", @@ -2351,6 +2357,13 @@ func TestCalcCellValue(t *testing.T) { // SUMSQ `=SUMSQ("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", "=SUMSQ(C1:D2)": "strconv.ParseFloat: parsing \"Month\": invalid syntax", + // SUMPRODUCT + "=SUMPRODUCT()": "SUMPRODUCT requires at least 1 argument", + "=SUMPRODUCT(A1,B1:B2)": "#VALUE!", + "=SUMPRODUCT(A1,D1)": "#VALUE!", + "=SUMPRODUCT(A1:A3,D1:D3)": "#VALUE!", + "=SUMPRODUCT(A1:A2,B1:B3)": "#VALUE!", + "=SUMPRODUCT(A1,NA())": "#N/A", // SUMX2MY2 "=SUMX2MY2()": "SUMX2MY2 requires 2 arguments", "=SUMX2MY2(A1,B1:B2)": "#N/A", @@ -4231,7 +4244,7 @@ func TestCalcMIRR(t *testing.T) { } } -func TestCalcSUMIFS(t *testing.T) { +func TestCalcSUMIFSAndAVERAGEIFS(t *testing.T) { cellData := [][]interface{}{ {"Quarter", "Area", "Sales Rep.", "Sales"}, {1, "North", "Jeff", 223000}, @@ -4249,8 +4262,10 @@ func TestCalcSUMIFS(t *testing.T) { } 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", + "=AVERAGEIFS(D2:D13,A2:A13,1,B2:B13,\"North\")": "174000", + "=AVERAGEIFS(D2:D13,A2:A13,\">2\",C2:C13,\"Jeff\")": "285500", + "=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)) @@ -4259,6 +4274,10 @@ func TestCalcSUMIFS(t *testing.T) { assert.Equal(t, expected, result, formula) } calcError := map[string]string{ + "=AVERAGEIFS()": "AVERAGEIFS requires at least 3 arguments", + "=AVERAGEIFS(H1,\"\")": "AVERAGEIFS requires at least 3 arguments", + "=AVERAGEIFS(H1,\"\",TRUE,1)": "#N/A", + "=AVERAGEIFS(H1,\"\",TRUE)": "AVERAGEIF divide by zero", "=SUMIFS()": "SUMIFS requires at least 3 arguments", "=SUMIFS(D2:D13,A2:A13,1,B2:B13)": "#N/A", } |