summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go164
-rw-r--r--calc_test.go59
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))
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-19 12:54:27 +0000