diff options
| -rw-r--r-- | calc.go | 61 | ||||
| -rw-r--r-- | calc_test.go | 20 |
2 files changed, 81 insertions, 0 deletions
@@ -542,9 +542,12 @@ type formulaFuncs struct { // SMALL // SQRT // SQRTPI +// STANDARDIZE // STDEV +// STDEV.P // STDEV.S // STDEVA +// STDEVP // SUBSTITUTE // SUM // SUMIF @@ -5994,6 +5997,64 @@ func (fn *formulaFuncs) SMALL(argsList *list.List) formulaArg { return fn.kth("SMALL", argsList) } +// STANDARDIZE function returns a normalized value of a distribution that is +// characterized by a supplied mean and standard deviation. The syntax of the +// function is: +// +// STANDARDIZE(x,mean,standard_dev) +// +func (fn *formulaFuncs) STANDARDIZE(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "STANDARDIZE requires 3 arguments") + } + x := argsList.Front().Value.(formulaArg).ToNumber() + if x.Type != ArgNumber { + return x + } + mean := argsList.Front().Next().Value.(formulaArg).ToNumber() + if mean.Type != ArgNumber { + return mean + } + stdDev := argsList.Back().Value.(formulaArg).ToNumber() + if stdDev.Type != ArgNumber { + return stdDev + } + if stdDev.Number <= 0 { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + return newNumberFormulaArg((x.Number - mean.Number) / stdDev.Number) +} + +// stdevp is an implementation of the formula functions STDEVP and STDEV.P. +func (fn *formulaFuncs) stdevp(name string, argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires at least 1 argument", name)) + } + varp := fn.VARP(argsList) + if varp.Type != ArgNumber { + return varp + } + return newNumberFormulaArg(math.Sqrt(varp.Number)) +} + +// STDEVP function calculates the standard deviation of a supplied set of +// values. The syntax of the function is: +// +// STDEVP(number1,[number2],...) +// +func (fn *formulaFuncs) STDEVP(argsList *list.List) formulaArg { + return fn.stdevp("STDEVP", argsList) +} + +// STDEVdotP function calculates the standard deviation of a supplied set of +// values. +// +// STDEV.P( number1, [number2], ... ) +// +func (fn *formulaFuncs) STDEVdotP(argsList *list.List) formulaArg { + return fn.stdevp("STDEV.P", argsList) +} + // TRIMMEAN function calculates the trimmed mean (or truncated mean) of a // supplied set of values. The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index b2fd5f4..a2ac719 100644 --- a/calc_test.go +++ b/calc_test.go @@ -911,6 +911,14 @@ func TestCalcCellValue(t *testing.T) { "=SMALL(A1:B5,2)": "1", "=SMALL(A1,1)": "1", "=SMALL(A1:F2,1)": "1", + // STANDARDIZE + "=STANDARDIZE( 5.5, 5, 2 )": "0.25", + "=STANDARDIZE( 12, 15, 1.5 )": "-2", + "=STANDARDIZE( -2, 0, 5 )": "-0.4", + // STDEVP + "=STDEVP(A1:B2,6,-1)": "2.40947204913349", + // STDEV.P + "=STDEV.P(A1:B2,6,-1)": "2.40947204913349", // TRIMMEAN "=TRIMMEAN(A1:B4,10%)": "2.5", "=TRIMMEAN(A1:B4,70%)": "2.5", @@ -2166,6 +2174,18 @@ 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", + // STANDARDIZE + "=STANDARDIZE()": "STANDARDIZE requires 3 arguments", + "=STANDARDIZE(\"\",0,5)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=STANDARDIZE(0,\"\",5)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=STANDARDIZE(0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=STANDARDIZE(0,0,0)": "#N/A", + // STDEVP + "=STDEVP()": "STDEVP requires at least 1 argument", + "=STDEVP(\"\")": "#DIV/0!", + // STDEV.P + "=STDEV.P()": "STDEV.P requires at least 1 argument", + "=STDEV.P(\"\")": "#DIV/0!", // TRIMMEAN "=TRIMMEAN()": "TRIMMEAN requires 2 arguments", "=TRIMMEAN(A1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", |