ref #65, new formula function: DAVERAGE

3 files changed, 73 insertions, 57 deletions

diff --git a/.gitignore b/.gitignore
index e697544..44b8b09 100644
--- a/.gitignore
+++ b/.gitignore
@@ -12,3 +12,4 @@ test/excelize-*
 *.out
 *.test
 .idea
+.DS_Store
diff --git a/calc.go b/calc.go
index 2fe73b3..8476f8d 100644
--- a/calc.go
+++ b/calc.go
@@ -418,6 +418,7 @@ type formulaFuncs struct {
 //    DATE
 //    DATEDIF
 //    DATEVALUE
+//    DAVERAGE
 //    DAY
 //    DAYS
 //    DAYS360
@@ -6008,7 +6009,7 @@ func (fn *formulaFuncs) AVERAGE(argsList *list.List) formulaArg {
 	}
 	count, sum := fn.countSum(false, args)
 	if count == 0 {
-		return newErrorFormulaArg(formulaErrorDIV, "AVERAGE divide by zero")
+		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
 	}
 	return newNumberFormulaArg(sum / count)
 }
@@ -6025,7 +6026,7 @@ func (fn *formulaFuncs) AVERAGEA(argsList *list.List) formulaArg {
 	}
 	count, sum := fn.countSum(true, args)
 	if count == 0 {
-		return newErrorFormulaArg(formulaErrorDIV, "AVERAGEA divide by zero")
+		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
 	}
 	return newNumberFormulaArg(sum / count)
 }
@@ -6075,7 +6076,7 @@ func (fn *formulaFuncs) AVERAGEIF(argsList *list.List) formulaArg {
 	}
 	count, sum := fn.countSum(false, args)
 	if count == 0 {
-		return newErrorFormulaArg(formulaErrorDIV, "AVERAGEIF divide by zero")
+		return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
 	}
 	return newNumberFormulaArg(sum / count)
 }
@@ -18068,6 +18069,42 @@ func (db *calcDatabase) next() bool {
 	return matched
 }
 
+// database is an implementation of the formula functions DAVERAGE, DMAX and DMIN.
+func (fn *formulaFuncs) database(name string, argsList *list.List) formulaArg {
+	if argsList.Len() != 3 {
+		return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 3 arguments", name))
+	}
+	database := argsList.Front().Value.(formulaArg)
+	field := argsList.Front().Next().Value.(formulaArg)
+	criteria := argsList.Back().Value.(formulaArg)
+	db := newCalcDatabase(database, field, criteria)
+	if db == nil {
+		return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+	}
+	args := list.New()
+	for db.next() {
+		args.PushBack(db.value())
+	}
+	switch name {
+	case "DMAX":
+		return fn.MAX(args)
+	case "DMIN":
+		return fn.MIN(args)
+	default:
+		return fn.AVERAGE(args)
+	}
+}
+
+// DAVERAGE function calculates the average (statistical mean) of values in a
+// field (column) in a database for selected records, that satisfy
+// user-specified criteria. The syntax of the Excel Daverage function is:
+//
+//    DAVERAGE(database,field,criteria)
+//
+func (fn *formulaFuncs) DAVERAGE(argsList *list.List) formulaArg {
+	return fn.database("DAVERAGE", argsList)
+}
+
 // dcount is an implementation of the formula functions DCOUNT and DCOUNTA.
 func (fn *formulaFuncs) dcount(name string, argsList *list.List) formulaArg {
 	if argsList.Len() < 2 {
@@ -18081,23 +18118,19 @@ func (fn *formulaFuncs) dcount(name string, argsList *list.List) formulaArg {
 	if argsList.Len() > 2 {
 		field = argsList.Front().Next().Value.(formulaArg)
 	}
-	var count float64
 	database := argsList.Front().Value.(formulaArg)
 	db := newCalcDatabase(database, field, criteria)
 	if db == nil {
 		return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
 	}
+	args := list.New()
 	for db.next() {
-		cell := db.value()
-		if cell.Value() == "" {
-			continue
-		}
-		if num := cell.ToNumber(); name == "DCOUNT" && num.Type != ArgNumber {
-			continue
-		}
-		count++
+		args.PushBack(db.value())
 	}
-	return newNumberFormulaArg(count)
+	if name == "DCOUNT" {
+		return fn.COUNT(args)
+	}
+	return fn.COUNTA(args)
 }
 
 // DCOUNT function returns the number of cells containing numeric values, in a
@@ -18122,28 +18155,6 @@ func (fn *formulaFuncs) DCOUNTA(argsList *list.List) formulaArg {
 	return fn.dcount("DCOUNTA", argsList)
 }
 
-// dmaxmin is an implementation of the formula functions DMAX and DMIN.
-func (fn *formulaFuncs) dmaxmin(name string, argsList *list.List) formulaArg {
-	if argsList.Len() != 3 {
-		return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 3 arguments", name))
-	}
-	database := argsList.Front().Value.(formulaArg)
-	field := argsList.Front().Next().Value.(formulaArg)
-	criteria := argsList.Back().Value.(formulaArg)
-	db := newCalcDatabase(database, field, criteria)
-	if db == nil {
-		return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
-	}
-	args := list.New()
-	for db.next() {
-		args.PushBack(db.value())
-	}
-	if name == "DMAX" {
-		return fn.MAX(args)
-	}
-	return fn.MIN(args)
-}
-
 // DMAX function finds the maximum value in a field (column) in a database for
 // selected records only. The records to be included in the calculation are
 // defined by a set of one or more user-specified criteria. The syntax of the
@@ -18152,7 +18163,7 @@ func (fn *formulaFuncs) dmaxmin(name string, argsList *list.List) formulaArg {
 //    DMAX(database,field,criteria)
 //
 func (fn *formulaFuncs) DMAX(argsList *list.List) formulaArg {
-	return fn.dmaxmin("DMAX", argsList)
+	return fn.database("DMAX", argsList)
 }
 
 // DMIN function finds the minimum value in a field (column) in a database for
@@ -18163,5 +18174,5 @@ func (fn *formulaFuncs) DMAX(argsList *list.List) formulaArg {
 //    DMIN(database,field,criteria)
 //
 func (fn *formulaFuncs) DMIN(argsList *list.List) formulaArg {
-	return fn.dmaxmin("DMIN", argsList)
+	return fn.database("DMIN", argsList)
 }
diff --git a/calc_test.go b/calc_test.go
index 089bfd3..8ad3c77 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -2655,14 +2655,14 @@ func TestCalcCellValue(t *testing.T) {
 		"=AVEDEV(\"\")":   "#VALUE!",
 		"=AVEDEV(1,\"\")": "#VALUE!",
 		// AVERAGE
-		"=AVERAGE(H1)": "AVERAGE divide by zero",
+		"=AVERAGE(H1)": "#DIV/0!",
 		// AVERAGEA
-		"=AVERAGEA(H1)": "AVERAGEA divide by zero",
+		"=AVERAGEA(H1)": "#DIV/0!",
 		// 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",
+		"=AVERAGEIF(H1,\"\")":               "#DIV/0!",
+		"=AVERAGEIF(D1:D3,\"Month\",D1:D3)": "#DIV/0!",
+		"=AVERAGEIF(C1:C3,\"Month\",D1:D3)": "#DIV/0!",
 		// 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",
@@ -4603,7 +4603,7 @@ func TestCalcCOVAR(t *testing.T) {
 	}
 }
 
-func TestCalcDCOUNTandDCOUNTAandDMAXandDMIN(t *testing.T) {
+func TestCalcDatabase(t *testing.T) {
 	cellData := [][]interface{}{
 		{"Tree", "Height", "Age", "Yield", "Profit", "Height"},
 		{"=Apple", ">1000%", nil, nil, nil, "<16"},
@@ -4621,20 +4621,21 @@ func TestCalcDCOUNTandDCOUNTAandDMAXandDMIN(t *testing.T) {
 	assert.NoError(t, f.SetCellFormula("Sheet1", "A3", "=\"=Pear\""))
 	assert.NoError(t, f.SetCellFormula("Sheet1", "C8", "=NA()"))
 	formulaList := map[string]string{
-		"=DCOUNT(A4:E10,\"Age\",A1:F2)":     "1",
-		"=DCOUNT(A4:E10,,A1:F2)":            "2",
-		"=DCOUNT(A4:E10,\"Profit\",A1:F2)":  "2",
-		"=DCOUNT(A4:E10,\"Tree\",A1:F2)":    "0",
-		"=DCOUNT(A4:E10,\"Age\",A2:F3)":     "0",
-		"=DCOUNTA(A4:E10,\"Age\",A1:F2)":    "1",
-		"=DCOUNTA(A4:E10,,A1:F2)":           "2",
-		"=DCOUNTA(A4:E10,\"Profit\",A1:F2)": "2",
-		"=DCOUNTA(A4:E10,\"Tree\",A1:F2)":   "2",
-		"=DCOUNTA(A4:E10,\"Age\",A2:F3)":    "0",
-		"=DMAX(A4:E10,\"Tree\",A1:F3)":      "0",
-		"=DMAX(A4:E10,\"Profit\",A1:F3)":    "96",
-		"=DMIN(A4:E10,\"Tree\",A1:F3)":      "0",
-		"=DMIN(A4:E10,\"Profit\",A1:F3)":    "45",
+		"=DCOUNT(A4:E10,\"Age\",A1:F2)":      "1",
+		"=DCOUNT(A4:E10,,A1:F2)":             "2",
+		"=DCOUNT(A4:E10,\"Profit\",A1:F2)":   "2",
+		"=DCOUNT(A4:E10,\"Tree\",A1:F2)":     "0",
+		"=DCOUNT(A4:E10,\"Age\",A2:F3)":      "0",
+		"=DCOUNTA(A4:E10,\"Age\",A1:F2)":     "1",
+		"=DCOUNTA(A4:E10,,A1:F2)":            "2",
+		"=DCOUNTA(A4:E10,\"Profit\",A1:F2)":  "2",
+		"=DCOUNTA(A4:E10,\"Tree\",A1:F2)":    "2",
+		"=DCOUNTA(A4:E10,\"Age\",A2:F3)":     "0",
+		"=DMAX(A4:E10,\"Tree\",A1:F3)":       "0",
+		"=DMAX(A4:E10,\"Profit\",A1:F3)":     "96",
+		"=DMIN(A4:E10,\"Tree\",A1:F3)":       "0",
+		"=DMIN(A4:E10,\"Profit\",A1:F3)":     "45",
+		"=DAVERAGE(A4:E10,\"Profit\",A1:F3)": "73.25",
 	}
 	for formula, expected := range formulaList {
 		assert.NoError(t, f.SetCellFormula("Sheet1", "A11", formula))
@@ -4659,6 +4660,9 @@ func TestCalcDCOUNTandDCOUNTAandDMAXandDMIN(t *testing.T) {
 		"=DMAX(A4:E10,\"x\",A1:F3)":           "#VALUE!",
 		"=DMIN()":                             "DMIN requires 3 arguments",
 		"=DMIN(A4:E10,\"x\",A1:F3)":           "#VALUE!",
+		"=DAVERAGE()":                         "DAVERAGE requires 3 arguments",
+		"=DAVERAGE(A4:E10,\"x\",A1:F3)":       "#VALUE!",
+		"=DAVERAGE(A4:E10,\"Tree\",A1:F3)":    "#DIV/0!",
 	}
 	for formula, expected := range calcError {
 		assert.NoError(t, f.SetCellFormula("Sheet1", "A11", formula))