diff options
| -rw-r--r-- | calc.go | 61 | ||||
| -rw-r--r-- | calc_test.go | 14 |
2 files changed, 75 insertions, 0 deletions
@@ -310,6 +310,7 @@ type formulaFuncs struct { // ATAN // ATAN2 // ATANH +// AVEDEV // AVERAGE // AVERAGEA // BASE @@ -329,6 +330,7 @@ type formulaFuncs struct { // CEILING.MATH // CEILING.PRECISE // CHAR +// CHIDIST // CHOOSE // CLEAN // CODE @@ -4675,6 +4677,31 @@ func (fn *formulaFuncs) TRUNC(argsList *list.List) formulaArg { // Statistical Functions +// AVEDEV function calculates the average deviation of a supplied set of +// values. The syntax of the function is: +// +// AVEDEV(number1,[number2],...) +// +func (fn *formulaFuncs) AVEDEV(argsList *list.List) formulaArg { + if argsList.Len() == 0 { + return newErrorFormulaArg(formulaErrorVALUE, "AVEDEV requires at least 1 argument") + } + average := fn.AVERAGE(argsList) + if average.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + result, count := 0.0, 0.0 + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + num := arg.Value.(formulaArg).ToNumber() + if num.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + result += math.Abs(num.Number - average.Number) + count++ + } + return newNumberFormulaArg(result / count) +} + // AVERAGE function returns the arithmetic mean of a list of supplied numbers. // The syntax of the function is: // @@ -4709,6 +4736,40 @@ func (fn *formulaFuncs) AVERAGEA(argsList *list.List) formulaArg { return newNumberFormulaArg(sum / count) } +// incompleteGamma is an implementation of the incomplete gamma function. +func incompleteGamma(a, x float64) float64 { + max := 32 + summer := 0.0 + for n := 0; n <= max; n++ { + divisor := a + for i := 1; i <= n; i++ { + divisor *= (a + float64(i)) + } + summer += math.Pow(x, float64(n)) / divisor + } + return math.Pow(x, a) * math.Exp(0-x) * summer +} + +// CHIDIST function calculates the right-tailed probability of the chi-square +// distribution. The syntax of the function is: +// +// CHIDIST(x,degrees_freedom) +// +func (fn *formulaFuncs) CHIDIST(argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, "CHIDIST requires 2 numeric arguments") + } + x := argsList.Front().Value.(formulaArg).ToNumber() + if x.Type != ArgNumber { + return x + } + degress := argsList.Back().Value.(formulaArg).ToNumber() + if degress.Type != ArgNumber { + return degress + } + return newNumberFormulaArg(1 - (incompleteGamma(degress.Number/2, x.Number/2) / math.Gamma(degress.Number/2))) +} + // 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 != "" { diff --git a/calc_test.go b/calc_test.go index 38b5f5f..2412615 100644 --- a/calc_test.go +++ b/calc_test.go @@ -735,6 +735,9 @@ func TestCalcCellValue(t *testing.T) { "=TRUNC(-99.999,-1)": "-90", "=TRUNC(TRUNC(1),-1)": "0", // Statistical Functions + // AVEDEV + "=AVEDEV(1,2)": "0.5", + "=AVERAGE(A1:A4,B1:B4)": "2.5", // AVERAGE "=AVERAGE(INT(1))": "1", "=AVERAGE(A1)": "1", @@ -745,6 +748,9 @@ func TestCalcCellValue(t *testing.T) { "=AVERAGEA(A1)": "1", "=AVERAGEA(A1:A2)": "1.5", "=AVERAGEA(D2:F9)": "12671.375", + // CHIDIST + "=CHIDIST(0.5,3)": "0.918891411654676", + "=CHIDIST(8,3)": "0.0460117056892315", // COUNT "=COUNT()": "0", "=COUNT(E1:F2,\"text\",1,INT(2))": "3", @@ -1891,10 +1897,18 @@ func TestCalcCellValue(t *testing.T) { `=TRUNC("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", `=TRUNC(1,"X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", // Statistical Functions + // AVEDEV + "=AVEDEV()": "AVEDEV requires at least 1 argument", + "=AVEDEV(\"\")": "#VALUE!", + "=AVEDEV(1,\"\")": "#VALUE!", // AVERAGE "=AVERAGE(H1)": "AVERAGE divide by zero", // AVERAGE "=AVERAGEA(H1)": "AVERAGEA divide by zero", + // CHIDIST + "=CHIDIST()": "CHIDIST requires 2 numeric arguments", + "=CHIDIST(\"\",3)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=CHIDIST(0.5,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // COUNTBLANK "=COUNTBLANK()": "COUNTBLANK requires 1 argument", "=COUNTBLANK(1,2)": "COUNTBLANK requires 1 argument", |