diff options
| -rw-r--r-- | calc.go | 96 | ||||
| -rw-r--r-- | calc_test.go | 61 |
2 files changed, 157 insertions, 0 deletions
@@ -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)) |