ref #65, new formula functions: COVAR, COVARIANCE.P, EXPON.DIST and EXPONDIST

2 files changed, 157 insertions, 0 deletions

diff --git a/calc.go b/calc.go
index 0f44912..ab205ce 100644
--- a/calc.go
+++ b/calc.go
@@ -373,6 +373,8 @@ type formulaFuncs struct {
 //    COUPNCD
 //    COUPNUM
 //    COUPPCD
+//    COVAR
+//    COVARIANCE.P
 //    CSC
 //    CSCH
 //    CUMIPMT
@@ -405,6 +407,8 @@ type formulaFuncs struct {
 //    EVEN
 //    EXACT
 //    EXP
+//    EXPON.DIST
+//    EXPONDIST
 //    FACT
 //    FACTDOUBLE
 //    FALSE
@@ -5203,6 +5207,51 @@ func (fn *formulaFuncs) CONFIDENCEdotNORM(argsList *list.List) formulaArg {
 	return fn.confidence("CONFIDENCE.NORM", argsList)
 }
 
+// COVAR function calculates the covariance of two supplied sets of values. The
+// syntax of the function is:
+//
+//    COVAR(array1,array2)
+//
+func (fn *formulaFuncs) COVAR(argsList *list.List) formulaArg {
+	if argsList.Len() != 2 {
+		return newErrorFormulaArg(formulaErrorVALUE, "COVAR requires 2 arguments")
+	}
+	array1 := argsList.Front().Value.(formulaArg)
+	array2 := argsList.Back().Value.(formulaArg)
+	left, right := array1.ToList(), array2.ToList()
+	n := len(left)
+	if n != len(right) {
+		return newErrorFormulaArg(formulaErrorNA, formulaErrorNA)
+	}
+	l1, l2 := list.New(), list.New()
+	l1.PushBack(array1)
+	l2.PushBack(array2)
+	result, skip := 0.0, 0
+	mean1, mean2 := fn.AVERAGE(l1), fn.AVERAGE(l2)
+	for i := 0; i < n; i++ {
+		arg1 := left[i].ToNumber()
+		arg2 := right[i].ToNumber()
+		if arg1.Type == ArgError || arg2.Type == ArgError {
+			skip++
+			continue
+		}
+		result += (arg1.Number - mean1.Number) * (arg2.Number - mean2.Number)
+	}
+	return newNumberFormulaArg(result / float64(n-skip))
+}
+
+// COVARIANCEdotP function calculates the population covariance of two supplied
+// sets of values. The syntax of the function is:
+//
+//    COVARIANCE.P(array1,array2)
+//
+func (fn *formulaFuncs) COVARIANCEdotP(argsList *list.List) formulaArg {
+	if argsList.Len() != 2 {
+		return newErrorFormulaArg(formulaErrorVALUE, "COVARIANCE.P requires 2 arguments")
+	}
+	return fn.COVAR(argsList)
+}
+
 // calcStringCountSum is part of the implementation countSum.
 func calcStringCountSum(countText bool, count, sum float64, num, arg formulaArg) (float64, float64) {
 	if countText && num.Type == ArgError && arg.String != "" {
@@ -5628,6 +5677,53 @@ func (fn *formulaFuncs) KURT(argsList *list.List) formulaArg {
 	return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV)
 }
 
+// EXPONdotDIST function returns the value of the exponential distribution for
+// a give value of x. The user can specify whether the probability density
+// function or the cumulative distribution function is used. The syntax of the
+// Expondist function is:
+//
+//    EXPON.DIST(x,lambda,cumulative)
+//
+func (fn *formulaFuncs) EXPONdotDIST(argsList *list.List) formulaArg {
+	if argsList.Len() != 3 {
+		return newErrorFormulaArg(formulaErrorVALUE, "EXPON.DIST requires 3 arguments")
+	}
+	return fn.EXPONDIST(argsList)
+}
+
+// EXPONDIST function returns the value of the exponential distribution for a
+// give value of x. The user can specify whether the probability density
+// function or the cumulative distribution function is used. The syntax of the
+// Expondist function is:
+//
+//    EXPONDIST(x,lambda,cumulative)
+//
+func (fn *formulaFuncs) EXPONDIST(argsList *list.List) formulaArg {
+	if argsList.Len() != 3 {
+		return newErrorFormulaArg(formulaErrorVALUE, "EXPONDIST requires 3 arguments")
+	}
+	var x, lambda, cumulative formulaArg
+	if x = argsList.Front().Value.(formulaArg).ToNumber(); x.Type != ArgNumber {
+		return x
+	}
+	if lambda = argsList.Front().Next().Value.(formulaArg).ToNumber(); lambda.Type != ArgNumber {
+		return lambda
+	}
+	if cumulative = argsList.Back().Value.(formulaArg).ToBool(); cumulative.Type == ArgError {
+		return cumulative
+	}
+	if x.Number < 0 {
+		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+	}
+	if lambda.Number <= 0 {
+		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+	}
+	if cumulative.Number == 1 {
+		return newNumberFormulaArg(1 - math.Exp(-lambda.Number*x.Number))
+	}
+	return newNumberFormulaArg(lambda.Number * math.Exp(-lambda.Number*x.Number))
+}
+
 // NORMdotDIST function calculates the Normal Probability Density Function or
 // the Cumulative Normal Distribution. Function for a supplied set of
 // parameters. The syntax of the function is:
diff --git a/calc_test.go b/calc_test.go
index d01f31b..a804b58 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -834,6 +834,14 @@ func TestCalcCellValue(t *testing.T) {
 		"=KURT(F1:F9)":           "-1.03350350255137",
 		"=KURT(F1,F2:F9)":        "-1.03350350255137",
 		"=KURT(INT(1),MUNIT(2))": "-3.33333333333334",
+		// EXPON.DIST
+		"=EXPON.DIST(0.5,1,TRUE)":  "0.393469340287367",
+		"=EXPON.DIST(0.5,1,FALSE)": "0.606530659712633",
+		"=EXPON.DIST(2,1,TRUE)":    "0.864664716763387",
+		// EXPONDIST
+		"=EXPONDIST(0.5,1,TRUE)":  "0.393469340287367",
+		"=EXPONDIST(0.5,1,FALSE)": "0.606530659712633",
+		"=EXPONDIST(2,1,TRUE)":    "0.864664716763387",
 		// NORM.DIST
 		"=NORM.DIST(0.8,1,0.3,TRUE)": "0.252492537546923",
 		"=NORM.DIST(50,40,20,FALSE)": "0.017603266338215",
@@ -2315,6 +2323,20 @@ func TestCalcCellValue(t *testing.T) {
 		// KURT
 		"=KURT()":          "KURT requires at least 1 argument",
 		"=KURT(F1,INT(1))": "#DIV/0!",
+		// EXPON.DIST
+		"=EXPON.DIST()":            "EXPON.DIST requires 3 arguments",
+		"=EXPON.DIST(\"\",1,TRUE)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=EXPON.DIST(0,\"\",TRUE)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=EXPON.DIST(0,1,\"\")":    "strconv.ParseBool: parsing \"\": invalid syntax",
+		"=EXPON.DIST(-1,1,TRUE)":   "#NUM!",
+		"=EXPON.DIST(1,0,TRUE)":    "#NUM!",
+		// EXPONDIST
+		"=EXPONDIST()":            "EXPONDIST requires 3 arguments",
+		"=EXPONDIST(\"\",1,TRUE)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=EXPONDIST(0,\"\",TRUE)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=EXPONDIST(0,1,\"\")":    "strconv.ParseBool: parsing \"\": invalid syntax",
+		"=EXPONDIST(-1,1,TRUE)":   "#NUM!",
+		"=EXPONDIST(1,0,TRUE)":    "#NUM!",
 		// NORM.DIST
 		"=NORM.DIST()": "NORM.DIST requires 4 arguments",
 		// NORMDIST
@@ -3707,6 +3729,45 @@ func TestCalcAVERAGEIF(t *testing.T) {
 	}
 }
 
+func TestCalcCOVAR(t *testing.T) {
+	cellData := [][]interface{}{
+		{"array1", "array2"},
+		{2, 22.9},
+		{7, 33.49},
+		{8, 34.5},
+		{3, 27.61},
+		{4, 19.5},
+		{1, 10.11},
+		{6, 37.9},
+		{5, 31.08},
+	}
+	f := prepareCalcData(cellData)
+	formulaList := map[string]string{
+		"=COVAR(A1:A9,B1:B9)":        "16.633125",
+		"=COVAR(A2:A9,B2:B9)":        "16.633125",
+		"=COVARIANCE.P(A1:A9,B1:B9)": "16.633125",
+		"=COVARIANCE.P(A2:A9,B2:B9)": "16.633125",
+	}
+	for formula, expected := range formulaList {
+		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)
+	}
+	calcError := map[string]string{
+		"=COVAR()":                   "COVAR requires 2 arguments",
+		"=COVAR(A2:A9,B3:B3)":        "#N/A",
+		"=COVARIANCE.P()":            "COVARIANCE.P requires 2 arguments",
+		"=COVARIANCE.P(A2:A9,B3:B3)": "#N/A",
+	}
+	for formula, expected := range calcError {
+		assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
+		result, err := f.CalcCellValue("Sheet1", "C1")
+		assert.EqualError(t, err, expected, formula)
+		assert.Equal(t, "", result, formula)
+	}
+}
+
 func TestCalcFORMULATEXT(t *testing.T) {
 	f, formulaText := NewFile(), "=SUM(B1:C1)"
 	assert.NoError(t, f.SetCellFormula("Sheet1", "A1", formulaText))