diff options
| -rw-r--r-- | calc.go | 67 | ||||
| -rw-r--r-- | calc_test.go | 17 |
2 files changed, 84 insertions, 0 deletions
@@ -339,7 +339,9 @@ var tokenPriority = map[string]int{ // OCT2HEX // ODD // OR +// PERCENTILE // PERMUT +// PERMUTATIONA // PI // POISSON.DIST // POISSON @@ -4519,6 +4521,46 @@ func (fn *formulaFuncs) min(mina bool, argsList *list.List) formulaArg { return newNumberFormulaArg(min) } +// PERCENTILE function returns the k'th percentile (i.e. the value below which +// k% of the data values fall) for a supplied range of values and a supplied +// k. The syntax of the function is: +// +// PERCENTILE(array,k) +// +func (fn *formulaFuncs) PERCENTILE(argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, "PERCENTILE requires 2 arguments") + } + array := argsList.Front().Value.(formulaArg).ToList() + k := argsList.Back().Value.(formulaArg).ToNumber() + if k.Type != ArgNumber { + return k + } + if k.Number < 0 || k.Number > 1 { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + numbers := []float64{} + for _, arg := range array { + if arg.Type == ArgError { + return arg + } + num := arg.ToNumber() + if num.Type == ArgNumber { + numbers = append(numbers, num.Number) + } + } + cnt := len(numbers) + sort.Float64s(numbers) + idx := k.Number * (float64(cnt) - 1) + base := math.Floor(idx) + if idx == base { + return newNumberFormulaArg(numbers[int(idx)]) + } + next := base + 1 + proportion := idx - base + return newNumberFormulaArg(numbers[int(base)] + ((numbers[int(next)] - numbers[int(base)]) * proportion)) +} + // PERMUT function calculates the number of permutations of a specified number // of objects from a set of objects. The syntax of the function is: // @@ -4542,6 +4584,31 @@ func (fn *formulaFuncs) PERMUT(argsList *list.List) formulaArg { return newNumberFormulaArg(math.Round(fact(number.Number) / fact(number.Number-chosen.Number))) } +// PERMUTATIONA function calculates the number of permutations, with +// repetitions, of a specified number of objects from a set. The syntax of +// the function is: +// +// PERMUTATIONA(number,number_chosen) +// +func (fn *formulaFuncs) PERMUTATIONA(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "PERMUTATIONA requires 2 numeric arguments") + } + number := argsList.Front().Value.(formulaArg).ToNumber() + chosen := argsList.Back().Value.(formulaArg).ToNumber() + if number.Type != ArgNumber { + return number + } + if chosen.Type != ArgNumber { + return chosen + } + num, numChosen := math.Floor(number.Number), math.Floor(chosen.Number) + if num < 0 || numChosen < 0 { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + return newNumberFormulaArg(math.Pow(num, numChosen)) +} + // SKEW function calculates the skewness of the distribution of a supplied set // of values. The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index c18683c..1253ae0 100644 --- a/calc_test.go +++ b/calc_test.go @@ -680,10 +680,16 @@ func TestCalcCellValue(t *testing.T) { "=MINA(MUNIT(2))": "0", "=MINA(INT(1))": "1", "=MINA(A1:B4,MUNIT(1),INT(0),1,E1:F2,\"\")": "0", + // PERCENTILE + "=PERCENTILE(A1:A4,0.2)": "0.6", + "=PERCENTILE(0,0)": "0", // PERMUT "=PERMUT(6,6)": "720", "=PERMUT(7,6)": "5040", "=PERMUT(10,6)": "151200", + // PERMUTATIONA + "=PERMUTATIONA(6,6)": "46656", + "=PERMUTATIONA(7,6)": "117649", // SKEW "=SKEW(1,2,3,4,3)": "-0.404796008910937", "=SKEW(A1:B2)": "0", @@ -1473,11 +1479,22 @@ func TestCalcCellValue(t *testing.T) { // MINA "=MINA()": "MINA requires at least 1 argument", "=MINA(NA())": "#N/A", + // PERCENTILE + "=PERCENTILE()": "PERCENTILE requires 2 arguments", + "=PERCENTILE(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=PERCENTILE(0,-1)": "#N/A", + "=PERCENTILE(NA(),1)": "#N/A", // PERMUT "=PERMUT()": "PERMUT requires 2 numeric arguments", "=PERMUT(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=PERMUT(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", "=PERMUT(6,8)": "#N/A", + // PERMUTATIONA + "=PERMUTATIONA()": "PERMUTATIONA requires 2 numeric arguments", + "=PERMUTATIONA(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=PERMUTATIONA(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=PERMUTATIONA(-1,0)": "#N/A", + "=PERMUTATIONA(0,-1)": "#N/A", // SKEW "=SKEW()": "SKEW requires at least 1 argument", "=SKEW(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", |