diff options
| -rw-r--r-- | calc.go | 80 | ||||
| -rw-r--r-- | calc_test.go | 15 |
2 files changed, 95 insertions, 0 deletions
@@ -533,6 +533,7 @@ type formulaFuncs struct { // SUM // SUMIF // SUMSQ +// SWITCH // SYD // T // TAN @@ -545,6 +546,7 @@ type formulaFuncs struct { // TODAY // TRANSPOSE // TRIM +// TRIMMEAN // TRUE // TRUNC // UNICHAR @@ -5741,6 +5743,49 @@ func (fn *formulaFuncs) SMALL(argsList *list.List) formulaArg { return fn.kth("SMALL", argsList) } +// TRIMMEAN function calculates the trimmed mean (or truncated mean) of a +// supplied set of values. The syntax of the function is: +// +// TRIMMEAN(array,percent) +// +func (fn *formulaFuncs) TRIMMEAN(argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, "TRIMMEAN requires 2 arguments") + } + percent := argsList.Back().Value.(formulaArg).ToNumber() + if percent.Type != ArgNumber { + return percent + } + if percent.Number < 0 || percent.Number >= 1 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + arr := []float64{} + arrArg := argsList.Front().Value.(formulaArg).ToList() + for _, cell := range arrArg { + num := cell.ToNumber() + if num.Type != ArgNumber { + continue + } + arr = append(arr, num.Number) + } + discard := math.Floor(float64(len(arr)) * percent.Number / 2) + sort.Float64s(arr) + for i := 0; i < int(discard); i++ { + if len(arr) > 0 { + arr = arr[1:] + } + if len(arr) > 0 { + arr = arr[:len(arr)-1] + } + } + + args := list.New().Init() + for _, ele := range arr { + args.PushBack(newNumberFormulaArg(ele)) + } + return fn.AVERAGE(args) +} + // VARP function returns the Variance of a given set of values. The syntax of // the function is: // @@ -6326,6 +6371,41 @@ func (fn *formulaFuncs) OR(argsList *list.List) formulaArg { return newStringFormulaArg(strings.ToUpper(strconv.FormatBool(or))) } +// SWITCH function compares a number of supplied values to a supplied test +// expression and returns a result corresponding to the first value that +// matches the test expression. A default value can be supplied, to be +// returned if none of the supplied values match the test expression. The +// syntax of the function is: +// +// +// SWITCH(expression,value1,result1,[value2,result2],[value3,result3],...,[default]) +// +func (fn *formulaFuncs) SWITCH(argsList *list.List) formulaArg { + if argsList.Len() < 3 { + return newErrorFormulaArg(formulaErrorVALUE, "SWITCH requires at least 3 arguments") + } + target := argsList.Front().Value.(formulaArg) + argCount := argsList.Len() - 1 + switchCount := int(math.Floor(float64(argCount) / 2)) + hasDefaultClause := argCount%2 != 0 + result := newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + if hasDefaultClause { + result = argsList.Back().Value.(formulaArg) + } + if switchCount > 0 { + arg := argsList.Front() + for i := 0; i < switchCount; i++ { + arg = arg.Next() + if target.Value() == arg.Value.(formulaArg).Value() { + result = arg.Next().Value.(formulaArg) + break + } + arg = arg.Next() + } + } + return result +} + // TRUE function returns the logical value TRUE. The syntax of the function // is: // diff --git a/calc_test.go b/calc_test.go index 06c7fe1..5661145 100644 --- a/calc_test.go +++ b/calc_test.go @@ -872,6 +872,9 @@ func TestCalcCellValue(t *testing.T) { "=SMALL(A1:B5,2)": "1", "=SMALL(A1,1)": "1", "=SMALL(A1:F2,1)": "1", + // TRIMMEAN + "=TRIMMEAN(A1:B4,10%)": "2.5", + "=TRIMMEAN(A1:B4,70%)": "2.5", // VARP "=VARP(A1:A5)": "1.25", // VAR.P @@ -957,6 +960,10 @@ func TestCalcCellValue(t *testing.T) { "=OR(0)": "FALSE", "=OR(1=2,2=2)": "TRUE", "=OR(1=2,2=3)": "FALSE", + // SWITCH + "=SWITCH(1,1,\"A\",2,\"B\",3,\"C\",\"N\")": "A", + "=SWITCH(3,1,\"A\",2,\"B\",3,\"C\",\"N\")": "C", + "=SWITCH(4,1,\"A\",2,\"B\",3,\"C\",\"N\")": "N", // TRUE "=TRUE()": "TRUE", // XOR @@ -2037,6 +2044,11 @@ func TestCalcCellValue(t *testing.T) { "=SMALL(A1:A5,0)": "k should be > 0", "=SMALL(A1:A5,6)": "k should be <= length of array", "=SMALL(A1:A5,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // TRIMMEAN + "=TRIMMEAN()": "TRIMMEAN requires 2 arguments", + "=TRIMMEAN(A1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=TRIMMEAN(A1,1)": "#NUM!", + "=TRIMMEAN(A1,-1)": "#NUM!", // VARP "=VARP()": "VARP requires at least 1 argument", "=VARP(\"\")": "#DIV/0!", @@ -2122,6 +2134,9 @@ func TestCalcCellValue(t *testing.T) { `=OR(A1:B1)`: "#VALUE!", "=OR()": "OR requires at least 1 argument", "=OR(1" + strings.Repeat(",1", 30) + ")": "OR accepts at most 30 arguments", + // SWITCH + "=SWITCH()": "SWITCH requires at least 3 arguments", + "=SWITCH(0,1,2)": "#N/A", // TRUE "=TRUE(A1)": "TRUE takes no arguments", // XOR |