diff options
| -rw-r--r-- | calc.go | 53 | ||||
| -rw-r--r-- | calc_test.go | 12 |
2 files changed, 65 insertions, 0 deletions
@@ -365,6 +365,7 @@ type formulaFuncs struct { // DEC2OCT // DECIMAL // DEGREES +// DEVSQ // DISC // DOLLARDE // DOLLARFR @@ -389,6 +390,7 @@ type formulaFuncs struct { // GAMMA // GAMMALN // GCD +// GEOMEAN // HARMEAN // HEX2BIN // HEX2DEC @@ -4924,6 +4926,36 @@ func (fn *formulaFuncs) COUNTBLANK(argsList *list.List) formulaArg { return newNumberFormulaArg(float64(count)) } +// DEVSQ function calculates the sum of the squared deviations from the sample +// mean. The syntax of the function is: +// +// DEVSQ(number1,[number2],...) +// +func (fn *formulaFuncs) DEVSQ(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "DEVSQ requires at least 1 numeric argument") + } + avg, count, result := fn.AVERAGE(argsList), -1, 0.0 + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + for _, number := range arg.Value.(formulaArg).ToList() { + num := number.ToNumber() + if num.Type != ArgNumber { + continue + } + count++ + if count == 0 { + result = math.Pow(num.Number-avg.Number, 2) + continue + } + result += math.Pow(num.Number-avg.Number, 2) + } + } + if count == -1 { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + return newNumberFormulaArg(result) +} + // FISHER function calculates the Fisher Transformation for a supplied value. // The syntax of the function is: // @@ -5030,6 +5062,27 @@ func (fn *formulaFuncs) GAMMALN(argsList *list.List) formulaArg { return newErrorFormulaArg(formulaErrorVALUE, "GAMMALN requires 1 numeric argument") } +// GEOMEAN function calculates the geometric mean of a supplied set of values. +// The syntax of the function is: +// +// GEOMEAN(number1,[number2],...) +// +func (fn *formulaFuncs) GEOMEAN(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "GEOMEAN requires at least 1 numeric argument") + } + product := fn.PRODUCT(argsList) + if product.Type != ArgNumber { + return product + } + count := fn.COUNT(argsList) + min := fn.MIN(argsList) + if product.Number > 0 && min.Number > 0 { + return newNumberFormulaArg(math.Pow(product.Number, (1 / count.Number))) + } + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) +} + // HARMEAN function calculates the harmonic mean of a supplied set of values. // The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index ffaec1a..6d46154 100644 --- a/calc_test.go +++ b/calc_test.go @@ -763,6 +763,9 @@ func TestCalcCellValue(t *testing.T) { "=COUNTBLANK(1)": "0", "=COUNTBLANK(B1:C1)": "1", "=COUNTBLANK(C1)": "1", + // DEVSQ + "=DEVSQ(1,3,5,2,9,7)": "47.5", + "=DEVSQ(A1:D2)": "10", // FISHER "=FISHER(-0.9)": "-1.47221948958322", "=FISHER(-0.25)": "-0.255412811882995", @@ -780,6 +783,8 @@ func TestCalcCellValue(t *testing.T) { // GAMMALN "=GAMMALN(4.5)": "2.45373657084244", "=GAMMALN(INT(1))": "0", + // GEOMEAN + "=GEOMEAN(2.5,3,0.5,1,3)": "1.6226711115996", // HARMEAN "=HARMEAN(2.5,3,0.5,1,3)": "1.22950819672131", "=HARMEAN(\"2.5\",3,0.5,1,INT(3),\"\")": "1.22950819672131", @@ -1977,6 +1982,9 @@ func TestCalcCellValue(t *testing.T) { // COUNTBLANK "=COUNTBLANK()": "COUNTBLANK requires 1 argument", "=COUNTBLANK(1,2)": "COUNTBLANK requires 1 argument", + // DEVSQ + "=DEVSQ()": "DEVSQ requires at least 1 numeric argument", + "=DEVSQ(D1:D2)": "#N/A", // FISHER "=FISHER()": "FISHER requires 1 numeric argument", "=FISHER(2)": "#N/A", @@ -1995,6 +2003,10 @@ func TestCalcCellValue(t *testing.T) { "=GAMMALN(F1)": "GAMMALN requires 1 numeric argument", "=GAMMALN(0)": "#N/A", "=GAMMALN(INT(0))": "#N/A", + // GEOMEAN + "=GEOMEAN()": "GEOMEAN requires at least 1 numeric argument", + "=GEOMEAN(0)": "#NUM!", + "=GEOMEAN(D1:D2)": "strconv.ParseFloat: parsing \"Month\": invalid syntax", // HARMEAN "=HARMEAN()": "HARMEAN requires at least 1 argument", "=HARMEAN(-1)": "#N/A", |