From b3493c54168c0f05b8623775640304128fd472e9 Mon Sep 17 00:00:00 2001 From: xuri Date: Wed, 17 Feb 2021 00:51:06 +0800 Subject: #65 fn: KURT, STDEV, STDEVA --- calc.go | 164 +++++++++++++++++++++++++++++++++++++++++++++++++++++------ calc_test.go | 59 +++++++++++++++------ 2 files changed, 191 insertions(+), 32 deletions(-) diff --git a/calc.go b/calc.go index 72ed876..ca44bf5 100644 --- a/calc.go +++ b/calc.go @@ -271,6 +271,7 @@ var tokenPriority = map[string]int{ // ISODD // ISTEXT // ISO.CEILING +// KURT // LCM // LEN // LENB @@ -314,6 +315,8 @@ var tokenPriority = map[string]int{ // SINH // SQRT // SQRTPI +// STDEV +// STDEVA // SUM // SUMIF // SUMSQ @@ -2872,41 +2875,118 @@ func (fn *formulaFuncs) SQRTPI(argsList *list.List) formulaArg { return newNumberFormulaArg(math.Sqrt(number.Number * math.Pi)) } +// STDEV function calculates the sample standard deviation of a supplied set +// of values. The syntax of the function is: +// +// STDEV(number1,[number2],...) +// +func (fn *formulaFuncs) STDEV(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "STDEV requires at least 1 argument") + } + return fn.stdev(false, argsList) +} + +// STDEVA function estimates standard deviation based on a sample. The +// standard deviation is a measure of how widely values are dispersed from +// the average value (the mean). The syntax of the function is: +// +// STDEVA(number1,[number2],...) +// +func (fn *formulaFuncs) STDEVA(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "STDEVA requires at least 1 argument") + } + return fn.stdev(true, argsList) +} + +// stdev is an implementation of the formula function STDEV and STDEVA. +func (fn *formulaFuncs) stdev(stdeva bool, argsList *list.List) formulaArg { + pow := func(result, count float64, n, m formulaArg) (float64, float64) { + if result == -1 { + result = math.Pow((n.Number - m.Number), 2) + } else { + result += math.Pow((n.Number - m.Number), 2) + } + count++ + return result, count + } + count, result := -1.0, -1.0 + var mean formulaArg + if stdeva { + mean = fn.AVERAGEA(argsList) + } else { + mean = fn.AVERAGE(argsList) + } + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg) + switch token.Type { + case ArgString, ArgNumber: + if !stdeva && (token.Value() == "TRUE" || token.Value() == "FALSE") { + continue + } else if stdeva && (token.Value() == "TRUE" || token.Value() == "FALSE") { + num := token.ToBool() + if num.Type == ArgNumber { + result, count = pow(result, count, num, mean) + continue + } + } else { + num := token.ToNumber() + if num.Type == ArgNumber { + result, count = pow(result, count, num, mean) + } + } + case ArgList, ArgMatrix: + for _, row := range token.ToList() { + if row.Type == ArgNumber || row.Type == ArgString { + if !stdeva && (row.Value() == "TRUE" || row.Value() == "FALSE") { + continue + } else if stdeva && (row.Value() == "TRUE" || row.Value() == "FALSE") { + num := row.ToBool() + if num.Type == ArgNumber { + result, count = pow(result, count, num, mean) + continue + } + } else { + num := row.ToNumber() + if num.Type == ArgNumber { + result, count = pow(result, count, num, mean) + } + } + } + } + } + } + if count > 0 && result >= 0 { + return newNumberFormulaArg(math.Sqrt(result / count)) + } + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) +} + // SUM function adds together a supplied set of numbers and returns the sum of // these values. The syntax of the function is: // // SUM(number1,[number2],...) // func (fn *formulaFuncs) SUM(argsList *list.List) formulaArg { - var ( - val, sum float64 - err error - ) + var sum float64 for arg := argsList.Front(); arg != nil; arg = arg.Next() { token := arg.Value.(formulaArg) switch token.Type { case ArgUnknown: continue case ArgString: - if token.String == "" { - continue - } - if val, err = strconv.ParseFloat(token.String, 64); err != nil { - return newErrorFormulaArg(formulaErrorVALUE, err.Error()) + if num := token.ToNumber(); num.Type == ArgNumber { + sum += num.Number } - sum += val case ArgNumber: sum += token.Number case ArgMatrix: for _, row := range token.Matrix { for _, value := range row { - if value.String == "" { - continue - } - if val, err = strconv.ParseFloat(value.String, 64); err != nil { - return newErrorFormulaArg(formulaErrorVALUE, err.Error()) + if num := value.ToNumber(); num.Type == ArgNumber { + sum += num.Number } - sum += val } } } @@ -3111,6 +3191,16 @@ func (fn *formulaFuncs) countSum(countText bool, args []formulaArg) (count, sum count++ } case ArgString: + if !countText && (arg.Value() == "TRUE" || arg.Value() == "FALSE") { + continue + } else if countText && (arg.Value() == "TRUE" || arg.Value() == "FALSE") { + num := arg.ToBool() + if num.Type == ArgNumber { + count++ + sum += num.Number + continue + } + } num := arg.ToNumber() if countText && num.Type == ArgError && arg.String != "" { count++ @@ -3329,6 +3419,48 @@ func (fn *formulaFuncs) GAMMALN(argsList *list.List) formulaArg { return newErrorFormulaArg(formulaErrorVALUE, "GAMMALN requires 1 numeric argument") } +// KURT function calculates the kurtosis of a supplied set of values. The +// syntax of the function is: +// +// KURT(number1,[number2],...) +// +func (fn *formulaFuncs) KURT(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "KURT requires at least 1 argument") + } + mean, stdev := fn.AVERAGE(argsList), fn.STDEV(argsList) + if stdev.Number > 0 { + count, summer := 0.0, 0.0 + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg) + switch token.Type { + case ArgString, ArgNumber: + num := token.ToNumber() + if num.Type == ArgError { + continue + } + summer += math.Pow((num.Number-mean.Number)/stdev.Number, 4) + count++ + case ArgList, ArgMatrix: + for _, row := range token.ToList() { + if row.Type == ArgNumber || row.Type == ArgString { + num := row.ToNumber() + if num.Type == ArgError { + continue + } + summer += math.Pow((num.Number-mean.Number)/stdev.Number, 4) + count++ + } + } + } + } + if count > 3 { + return newNumberFormulaArg(summer*(count*(count+1)/((count-1)*(count-2)*(count-3))) - (3 * math.Pow(count-1, 2) / ((count - 2) * (count - 3)))) + } + } + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) +} + // MAX function returns the largest value from a supplied set of numeric // values. The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index 1f9fea6..5b406bc 100644 --- a/calc_test.go +++ b/calc_test.go @@ -415,6 +415,15 @@ func TestCalcCellValue(t *testing.T) { "=SQRTPI(100)": "17.72453850905516", "=SQRTPI(0)": "0", "=SQRTPI(SQRTPI(0))": "0", + // STDEV + "=STDEV(F2:F9)": "10724.978287523809", + "=STDEV(MUNIT(2))": "0.577350269189626", + "=STDEV(0,INT(0))": "0", + "=STDEV(INT(1),INT(1))": "0", + // STDEVA + "=STDEVA(F2:F9)": "10724.978287523809", + "=STDEVA(MUNIT(2))": "0.577350269189626", + "=STDEVA(0,INT(0))": "0", // SUM "=SUM(1,2)": "3", `=SUM("",1,2)`: "3", @@ -507,6 +516,10 @@ func TestCalcCellValue(t *testing.T) { // GAMMALN "=GAMMALN(4.5)": "2.453736570842443", "=GAMMALN(INT(1))": "0", + // KURT + "=KURT(F1:F9)": "-1.033503502551368", + "=KURT(F1,F2:F9)": "-1.033503502551368", + "=KURT(INT(1),MUNIT(2))": "-3.333333333333336", // MAX "=MAX(1)": "1", "=MAX(TRUE())": "1", @@ -945,14 +958,19 @@ func TestCalcCellValue(t *testing.T) { // SQRTPI "=SQRTPI()": "SQRTPI requires 1 numeric argument", `=SQRTPI("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", + // STDEV + "=STDEV()": "STDEV requires at least 1 argument", + "=STDEV(E2:E9)": "#DIV/0!", + // STDEVA + "=STDEVA()": "STDEVA requires at least 1 argument", + "=STDEVA(E2:E9)": "#DIV/0!", // SUM - "=SUM((": "formula not valid", - "=SUM(-)": "formula not valid", - "=SUM(1+)": "formula not valid", - "=SUM(1-)": "formula not valid", - "=SUM(1*)": "formula not valid", - "=SUM(1/)": "formula not valid", - `=SUM("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", + "=SUM((": "formula not valid", + "=SUM(-)": "formula not valid", + "=SUM(1+)": "formula not valid", + "=SUM(1-)": "formula not valid", + "=SUM(1*)": "formula not valid", + "=SUM(1/)": "formula not valid", // SUMIF "=SUMIF()": "SUMIF requires at least 2 argument", // SUMSQ @@ -994,6 +1012,9 @@ func TestCalcCellValue(t *testing.T) { "=GAMMALN(F1)": "GAMMALN requires 1 numeric argument", "=GAMMALN(0)": "#N/A", "=GAMMALN(INT(0))": "#N/A", + // KURT + "=KURT()": "KURT requires at least 1 argument", + "=KURT(F1,INT(1))": "#DIV/0!", // MAX "=MAX()": "MAX requires at least 1 argument", "=MAX(NA())": "#N/A", @@ -1168,6 +1189,8 @@ func TestCalcCellValue(t *testing.T) { "=1+SUM(SUM(A1+A2/A3)*(2-3),2)": "1.333333333333334", "=A1/A2/SUM(A1:A2:B1)": "0.041666666666667", "=A1/A2/SUM(A1:A2:B1)*A3": "0.125", + "=SUM(B1:D1)": "4", + "=SUM(\"X\")": "0", } for formula, expected := range referenceCalc { f := prepareCalcData(cellData) @@ -1380,20 +1403,24 @@ func TestCalcVLOOKUP(t *testing.T) { } } -func TestCalcMAXMIN(t *testing.T) { +func TestCalcBoolean(t *testing.T) { cellData := [][]interface{}{ {0.5, "TRUE", -0.5, "FALSE"}, } f := prepareCalcData(cellData) formulaList := map[string]string{ - "=MAX(0.5,B1)": "0.5", - "=MAX(A1:B1)": "0.5", - "=MAXA(A1:B1)": "1", - "=MAXA(0.5,B1)": "1", - "=MIN(-0.5,D1)": "-0.5", - "=MIN(C1:D1)": "-0.5", - "=MINA(C1:D1)": "-0.5", - "=MINA(-0.5,D1)": "-0.5", + "=AVERAGEA(A1:C1)": "0.333333333333333", + "=MAX(0.5,B1)": "0.5", + "=MAX(A1:B1)": "0.5", + "=MAXA(A1:B1)": "1", + "=MAXA(0.5,B1)": "1", + "=MIN(-0.5,D1)": "-0.5", + "=MIN(C1:D1)": "-0.5", + "=MINA(C1:D1)": "-0.5", + "=MINA(-0.5,D1)": "-0.5", + "=STDEV(A1:C1)": "0.707106781186548", + "=STDEV(A1,B1,C1)": "0.707106781186548", + "=STDEVA(A1:C1,B1)": "0.707106781186548", } for formula, expected := range formulaList { assert.NoError(t, f.SetCellFormula("Sheet1", "B10", formula)) -- cgit v1.2.1