diff options
| -rw-r--r-- | calc.go | 100 | ||||
| -rw-r--r-- | calc_test.go | 26 |
2 files changed, 126 insertions, 0 deletions
@@ -341,6 +341,8 @@ var tokenPriority = map[string]int{ // OR // PERMUT // PI +// POISSON.DIST +// POISSON // POWER // PRODUCT // PROPER @@ -365,10 +367,12 @@ var tokenPriority = map[string]int{ // SIGN // SIN // SINH +// SKEW // SMALL // SQRT // SQRTPI // STDEV +// STDEV.S // STDEVA // SUBSTITUTE // SUM @@ -3396,6 +3400,18 @@ func (fn *formulaFuncs) STDEV(argsList *list.List) formulaArg { return fn.stdev(false, argsList) } +// STDEVdotS function calculates the sample standard deviation of a supplied +// set of values. The syntax of the function is: +// +// STDEV.S(number1,[number2],...) +// +func (fn *formulaFuncs) STDEVdotS(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "STDEV.S 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: @@ -3472,6 +3488,53 @@ func (fn *formulaFuncs) stdev(stdeva bool, argsList *list.List) formulaArg { return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) } +// POISSONdotDIST function calculates the Poisson Probability Mass Function or +// the Cumulative Poisson Probability Function for a supplied set of +// parameters. The syntax of the function is: +// +// POISSON.DIST(x,mean,cumulative) +// +func (fn *formulaFuncs) POISSONdotDIST(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "POISSON.DIST requires 3 arguments") + } + return fn.POISSON(argsList) +} + +// POISSON function calculates the Poisson Probability Mass Function or the +// Cumulative Poisson Probability Function for a supplied set of parameters. +// The syntax of the function is: +// +// POISSON(x,mean,cumulative) +// +func (fn *formulaFuncs) POISSON(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "POISSON requires 3 arguments") + } + var x, mean, cumulative formulaArg + if x = argsList.Front().Value.(formulaArg).ToNumber(); x.Type != ArgNumber { + return x + } + if mean = argsList.Front().Next().Value.(formulaArg).ToNumber(); mean.Type != ArgNumber { + return mean + } + if cumulative = argsList.Back().Value.(formulaArg).ToBool(); cumulative.Type == ArgError { + return cumulative + } + if x.Number < 0 || mean.Number <= 0 { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + if cumulative.Number == 1 { + summer := 0.0 + floor := math.Floor(x.Number) + for i := 0; i <= int(floor); i++ { + summer += math.Pow(mean.Number, float64(i)) / fact(float64(i)) + } + return newNumberFormulaArg(math.Exp(0-mean.Number) * summer) + } + return newNumberFormulaArg(math.Exp(0-mean.Number) * math.Pow(mean.Number, x.Number) / fact(x.Number)) +} + // SUM function adds together a supplied set of numbers and returns the sum of // these values. The syntax of the function is: // @@ -4479,6 +4542,43 @@ func (fn *formulaFuncs) PERMUT(argsList *list.List) formulaArg { return newNumberFormulaArg(math.Round(fact(number.Number) / fact(number.Number-chosen.Number))) } +// SKEW function calculates the skewness of the distribution of a supplied set +// of values. The syntax of the function is: +// +// SKEW(number1,[number2],...) +// +func (fn *formulaFuncs) SKEW(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "SKEW requires at least 1 argument") + } + mean, stdDev, count, summer := fn.AVERAGE(argsList), fn.STDEV(argsList), 0.0, 0.0 + for arg := argsList.Front(); arg != nil; arg = arg.Next() { + token := arg.Value.(formulaArg) + switch token.Type { + case ArgNumber, ArgString: + num := token.ToNumber() + if num.Type == ArgError { + return num + } + summer += math.Pow((num.Number-mean.Number)/stdDev.Number, 3) + count++ + case ArgList, ArgMatrix: + for _, row := range token.ToList() { + numArg := row.ToNumber() + if numArg.Type != ArgNumber { + continue + } + summer += math.Pow((numArg.Number-mean.Number)/stdDev.Number, 3) + count++ + } + } + } + if count > 2 { + return newNumberFormulaArg(summer * (count / ((count - 1) * (count - 2)))) + } + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) +} + // SMALL function returns the k'th smallest value from an array of numeric // values. The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index 65f7ce1..c18683c 100644 --- a/calc_test.go +++ b/calc_test.go @@ -509,10 +509,18 @@ func TestCalcCellValue(t *testing.T) { "=STDEV(MUNIT(2))": "0.577350269189626", "=STDEV(0,INT(0))": "0", "=STDEV(INT(1),INT(1))": "0", + // STDEV.S + "=STDEV.S(F2:F9)": "10724.978287523809", // STDEVA "=STDEVA(F2:F9)": "10724.978287523809", "=STDEVA(MUNIT(2))": "0.577350269189626", "=STDEVA(0,INT(0))": "0", + // POISSON.DIST + "=POISSON.DIST(20,25,FALSE)": "0.051917468608491", + "=POISSON.DIST(35,40,TRUE)": "0.242414197690103", + // POISSON + "=POISSON(20,25,FALSE)": "0.051917468608491", + "=POISSON(35,40,TRUE)": "0.242414197690103", // SUM "=SUM(1,2)": "3", `=SUM("",1,2)`: "3", @@ -676,6 +684,10 @@ func TestCalcCellValue(t *testing.T) { "=PERMUT(6,6)": "720", "=PERMUT(7,6)": "5040", "=PERMUT(10,6)": "151200", + // SKEW + "=SKEW(1,2,3,4,3)": "-0.404796008910937", + "=SKEW(A1:B2)": "0", + "=SKEW(A1:D3)": "0", // SMALL "=SMALL(A1:A5,1)": "0", "=SMALL(A1:B5,2)": "1", @@ -1345,9 +1357,19 @@ func TestCalcCellValue(t *testing.T) { // STDEV "=STDEV()": "STDEV requires at least 1 argument", "=STDEV(E2:E9)": "#DIV/0!", + // STDEV.S + "=STDEV.S()": "STDEV.S requires at least 1 argument", // STDEVA "=STDEVA()": "STDEVA requires at least 1 argument", "=STDEVA(E2:E9)": "#DIV/0!", + // POISSON.DIST + "=POISSON.DIST()": "POISSON.DIST requires 3 arguments", + // POISSON + "=POISSON()": "POISSON requires 3 arguments", + "=POISSON(\"\",0,FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=POISSON(0,\"\",FALSE)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=POISSON(0,0,\"\")": "strconv.ParseBool: parsing \"\": invalid syntax", + "=POISSON(0,-1,TRUE)": "#N/A", // SUM "=SUM((": "formula not valid", "=SUM(-)": "formula not valid", @@ -1456,6 +1478,10 @@ func TestCalcCellValue(t *testing.T) { "=PERMUT(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=PERMUT(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", "=PERMUT(6,8)": "#N/A", + // SKEW + "=SKEW()": "SKEW requires at least 1 argument", + "=SKEW(\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=SKEW(0)": "#DIV/0!", // SMALL "=SMALL()": "SMALL requires 2 arguments", "=SMALL(A1:A5,0)": "k should be > 0", |