summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go110
-rw-r--r--calc_test.go48
2 files changed, 133 insertions, 25 deletions
diff --git a/calc.go b/calc.go
index bd62d94..13d0ac2 100644
--- a/calc.go
+++ b/calc.go
@@ -52,6 +52,7 @@ const (
criteriaEq
criteriaLe
criteriaGe
+ criteriaNe
criteriaL
criteriaG
criteriaErr
@@ -315,6 +316,7 @@ type formulaFuncs struct {
// AVEDEV
// AVERAGE
// AVERAGEA
+// AVERAGEIF
// BASE
// BESSELI
// BESSELJ
@@ -352,6 +354,7 @@ type formulaFuncs struct {
// COUNT
// COUNTA
// COUNTBLANK
+// COUNTIF
// COUPDAYBS
// COUPDAYS
// COUPDAYSNC
@@ -1419,6 +1422,10 @@ func formulaCriteriaParser(exp string) (fc *formulaCriteria) {
fc.Type, fc.Condition = criteriaEq, match[1]
return
}
+ if match := regexp.MustCompile(`^<>(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
+ fc.Type, fc.Condition = criteriaNe, match[1]
+ return
+ }
if match := regexp.MustCompile(`^<=(.*)$`).FindStringSubmatch(exp); len(match) > 1 {
fc.Type, fc.Condition = criteriaLe, match[1]
return
@@ -1467,6 +1474,8 @@ func formulaCriteriaEval(val string, criteria *formulaCriteria) (result bool, er
case criteriaGe:
value, expected, e = prepareValue(val, criteria.Condition)
return value >= expected && e == nil, err
+ case criteriaNe:
+ return val != criteria.Condition, err
case criteriaL:
value, expected, e = prepareValue(val, criteria.Condition)
return value < expected && e == nil, err
@@ -4723,7 +4732,7 @@ func (fn *formulaFuncs) SUM(argsList *list.List) formulaArg {
//
func (fn *formulaFuncs) SUMIF(argsList *list.List) formulaArg {
if argsList.Len() < 2 {
- return newErrorFormulaArg(formulaErrorVALUE, "SUMIF requires at least 2 argument")
+ return newErrorFormulaArg(formulaErrorVALUE, "SUMIF requires at least 2 arguments")
}
var criteria = formulaCriteriaParser(argsList.Front().Next().Value.(formulaArg).String)
var rangeMtx = argsList.Front().Value.(formulaArg).Matrix
@@ -4740,9 +4749,7 @@ func (fn *formulaFuncs) SUMIF(argsList *list.List) formulaArg {
if col.String == "" {
continue
}
- if ok, err = formulaCriteriaEval(fromVal, criteria); err != nil {
- return newErrorFormulaArg(formulaErrorVALUE, err.Error())
- }
+ ok, _ = formulaCriteriaEval(fromVal, criteria)
if ok {
if argsList.Len() == 3 {
if len(sumRange) <= rowIdx || len(sumRange[rowIdx]) <= colIdx {
@@ -4751,7 +4758,7 @@ func (fn *formulaFuncs) SUMIF(argsList *list.List) formulaArg {
fromVal = sumRange[rowIdx][colIdx].String
}
if val, err = strconv.ParseFloat(fromVal, 64); err != nil {
- return newErrorFormulaArg(formulaErrorVALUE, err.Error())
+ continue
}
sum += val
}
@@ -4927,6 +4934,57 @@ func (fn *formulaFuncs) AVERAGEA(argsList *list.List) formulaArg {
return newNumberFormulaArg(sum / count)
}
+// AVERAGEIF function finds the values in a supplied array that satisfy a
+// specified criteria, and returns the average (i.e. the statistical mean) of
+// the corresponding values in a second supplied array. The syntax of the
+// function is:
+//
+// AVERAGEIF(range,criteria,[average_range])
+//
+func (fn *formulaFuncs) AVERAGEIF(argsList *list.List) formulaArg {
+ if argsList.Len() < 2 {
+ return newErrorFormulaArg(formulaErrorVALUE, "AVERAGEIF requires at least 2 arguments")
+ }
+ var (
+ criteria = formulaCriteriaParser(argsList.Front().Next().Value.(formulaArg).String)
+ rangeMtx = argsList.Front().Value.(formulaArg).Matrix
+ cellRange [][]formulaArg
+ args []formulaArg
+ val float64
+ err error
+ ok bool
+ )
+ if argsList.Len() == 3 {
+ cellRange = argsList.Back().Value.(formulaArg).Matrix
+ }
+ for rowIdx, row := range rangeMtx {
+ for colIdx, col := range row {
+ fromVal := col.String
+ if col.String == "" {
+ continue
+ }
+ ok, _ = formulaCriteriaEval(fromVal, criteria)
+ if ok {
+ if argsList.Len() == 3 {
+ if len(cellRange) <= rowIdx || len(cellRange[rowIdx]) <= colIdx {
+ continue
+ }
+ fromVal = cellRange[rowIdx][colIdx].String
+ }
+ if val, err = strconv.ParseFloat(fromVal, 64); err != nil {
+ continue
+ }
+ args = append(args, newNumberFormulaArg(val))
+ }
+ }
+ }
+ count, sum := fn.countSum(false, args)
+ if count == 0 {
+ return newErrorFormulaArg(formulaErrorDIV, "AVERAGEIF divide by zero")
+ }
+ return newNumberFormulaArg(sum / count)
+}
+
// incompleteGamma is an implementation of the incomplete gamma function.
func incompleteGamma(a, x float64) float64 {
max := 32
@@ -5134,28 +5192,34 @@ func (fn *formulaFuncs) COUNTBLANK(argsList *list.List) formulaArg {
if argsList.Len() != 1 {
return newErrorFormulaArg(formulaErrorVALUE, "COUNTBLANK requires 1 argument")
}
- var count int
- token := argsList.Front().Value.(formulaArg)
- switch token.Type {
- case ArgString:
- if token.String == "" {
+ var count float64
+ for _, cell := range argsList.Front().Value.(formulaArg).ToList() {
+ if cell.Value() == "" {
count++
}
- case ArgList, ArgMatrix:
- for _, row := range token.ToList() {
- switch row.Type {
- case ArgString:
- if row.String == "" {
- count++
- }
- case ArgEmpty:
- count++
- }
+ }
+ return newNumberFormulaArg(count)
+}
+
+// COUNTIF function returns the number of cells within a supplied range, that
+// satisfy a given criteria. The syntax of the function is:
+//
+// COUNTIF(range,criteria)
+//
+func (fn *formulaFuncs) COUNTIF(argsList *list.List) formulaArg {
+ if argsList.Len() != 2 {
+ return newErrorFormulaArg(formulaErrorVALUE, "COUNTIF requires 2 arguments")
+ }
+ var (
+ criteria = formulaCriteriaParser(argsList.Front().Next().Value.(formulaArg).String)
+ count float64
+ )
+ for _, cell := range argsList.Front().Value.(formulaArg).ToList() {
+ if ok, _ := formulaCriteriaEval(cell.Value(), criteria); ok {
+ count++
}
- case ArgEmpty:
- count++
}
- return newNumberFormulaArg(float64(count))
+ return newNumberFormulaArg(count)
}
// DEVSQ function calculates the sum of the squared deviations from the sample
diff --git a/calc_test.go b/calc_test.go
index 0aeff70..91e71d7 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -737,6 +737,7 @@ func TestCalcCellValue(t *testing.T) {
`=SUMIF(D2:D9,"Feb",F2:F9)`: "157559",
`=SUMIF(E2:E9,"North 1",F2:F9)`: "66582",
`=SUMIF(E2:E9,"North*",F2:F9)`: "138772",
+ "=SUMIF(D1:D3,\"Month\",D1:D3)": "0",
// SUMSQ
"=SUMSQ(A1:A4)": "14",
"=SUMSQ(A1,B1,A2,B2,6)": "82",
@@ -793,6 +794,11 @@ func TestCalcCellValue(t *testing.T) {
"=COUNTBLANK(1)": "0",
"=COUNTBLANK(B1:C1)": "1",
"=COUNTBLANK(C1)": "1",
+ // COUNTIF
+ "=COUNTIF(D1:D9,\"Jan\")": "4",
+ "=COUNTIF(D1:D9,\"<>Jan\")": "5",
+ "=COUNTIF(A1:F9,\">=50000\")": "2",
+ "=COUNTIF(A1:F9,TRUE)": "0",
// DEVSQ
"=DEVSQ(1,3,5,2,9,7)": "47.5",
"=DEVSQ(A1:D2)": "10",
@@ -2150,7 +2156,7 @@ func TestCalcCellValue(t *testing.T) {
"=SUM(1*)": ErrInvalidFormula.Error(),
"=SUM(1/)": ErrInvalidFormula.Error(),
// SUMIF
- "=SUMIF()": "SUMIF requires at least 2 argument",
+ "=SUMIF()": "SUMIF requires at least 2 arguments",
// SUMSQ
`=SUMSQ("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax",
"=SUMSQ(C1:D2)": "strconv.ParseFloat: parsing \"Month\": invalid syntax",
@@ -2171,8 +2177,13 @@ func TestCalcCellValue(t *testing.T) {
"=AVEDEV(1,\"\")": "#VALUE!",
// AVERAGE
"=AVERAGE(H1)": "AVERAGE divide by zero",
- // AVERAGE
+ // 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",
// CHIDIST
"=CHIDIST()": "CHIDIST requires 2 numeric arguments",
"=CHIDIST(\"\",3)": "strconv.ParseFloat: parsing \"\": invalid syntax",
@@ -2198,6 +2209,8 @@ func TestCalcCellValue(t *testing.T) {
// COUNTBLANK
"=COUNTBLANK()": "COUNTBLANK requires 1 argument",
"=COUNTBLANK(1,2)": "COUNTBLANK requires 1 argument",
+ // COUNTIF
+ "=COUNTIF()": "COUNTIF requires 2 arguments",
// DEVSQ
"=DEVSQ()": "DEVSQ requires at least 1 numeric argument",
"=DEVSQ(D1:D2)": "#N/A",
@@ -3544,6 +3557,37 @@ func TestCalcBoolean(t *testing.T) {
}
}
+func TestCalcAVERAGEIF(t *testing.T) {
+ f := prepareCalcData([][]interface{}{
+ {"Monday", 500},
+ {"Tuesday", 50},
+ {"Thursday", 100},
+ {"Friday", 100},
+ {"Thursday", 200},
+ {5, 300},
+ {2, 200},
+ {3, 100},
+ {4, 50},
+ {5, 100},
+ {1, 50},
+ {"TRUE", 200},
+ {"TRUE", 250},
+ {"FALSE", 50},
+ })
+ for formula, expected := range map[string]string{
+ "=AVERAGEIF(A1:A14,\"Thursday\",B1:B14)": "150",
+ "=AVERAGEIF(A1:A14,5,B1:B14)": "200",
+ "=AVERAGEIF(A1:A14,\">2\",B1:B14)": "137.5",
+ "=AVERAGEIF(A1:A14,TRUE,B1:B14)": "225",
+ "=AVERAGEIF(A1:A14,\"<>TRUE\",B1:B14)": "150",
+ } {
+ assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
+ result, err := f.CalcCellValue("Sheet1", "C1")
+ assert.NoError(t, err, formula)
+ assert.Equal(t, expected, result, formula)
+ }
+}
+
func TestCalcHLOOKUP(t *testing.T) {
cellData := [][]interface{}{
{"Example Result Table"},
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-19 19:51:10 +0000