diff options
| -rw-r--r-- | calc.go | 77 | ||||
| -rw-r--r-- | calc_test.go | 24 |
2 files changed, 83 insertions, 18 deletions
@@ -572,8 +572,11 @@ type formulaFuncs struct { // UNICODE // UPPER // VALUE +// VAR // VAR.P +// VARA // VARP +// VARPA // VLOOKUP // WEEKDAY // WEIBULL @@ -6098,43 +6101,89 @@ func (fn *formulaFuncs) TRIMMEAN(argsList *list.List) formulaArg { return fn.AVERAGE(args) } -// VARP function returns the Variance of a given set of values. The syntax of -// the function is: -// -// VARP(number1,[number2],...) -// -func (fn *formulaFuncs) VARP(argsList *list.List) formulaArg { +// vars is an implementation of the formula functions VAR, VARA, VARP, VAR.P +// and VARPA. +func (fn *formulaFuncs) vars(name string, argsList *list.List) formulaArg { if argsList.Len() < 1 { - return newErrorFormulaArg(formulaErrorVALUE, "VARP requires at least 1 argument") + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires at least 1 argument", name)) } summerA, summerB, count := 0.0, 0.0, 0.0 + minimum := 0.0 + if name == "VAR" || name == "VARA" { + minimum = 1.0 + } for arg := argsList.Front(); arg != nil; arg = arg.Next() { for _, token := range arg.Value.(formulaArg).ToList() { - if num := token.ToNumber(); num.Type == ArgNumber { + num := token.ToNumber() + if token.Value() != "TRUE" && num.Type == ArgNumber { + summerA += (num.Number * num.Number) + summerB += num.Number + count++ + continue + } + num = token.ToBool() + if num.Type == ArgNumber { summerA += (num.Number * num.Number) summerB += num.Number count++ + continue + } + if name == "VARA" || name == "VARPA" { + count++ } } } - if count > 0 { + if count > minimum { summerA *= count summerB *= summerB - return newNumberFormulaArg((summerA - summerB) / (count * count)) + return newNumberFormulaArg((summerA - summerB) / (count * (count - minimum))) } return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) } +// VAR function returns the sample variance of a supplied set of values. The +// syntax of the function is: +// +// VAR(number1,[number2],...) +// +func (fn *formulaFuncs) VAR(argsList *list.List) formulaArg { + return fn.vars("VAR", argsList) +} + +// VARA function calculates the sample variance of a supplied set of values. +// The syntax of the function is: +// +// VARA(number1,[number2],...) +// +func (fn *formulaFuncs) VARA(argsList *list.List) formulaArg { + return fn.vars("VARA", argsList) +} + +// VARP function returns the Variance of a given set of values. The syntax of +// the function is: +// +// VARP(number1,[number2],...) +// +func (fn *formulaFuncs) VARP(argsList *list.List) formulaArg { + return fn.vars("VARP", argsList) +} + // VARdotP function returns the Variance of a given set of values. The syntax // of the function is: // // VAR.P(number1,[number2],...) // func (fn *formulaFuncs) VARdotP(argsList *list.List) formulaArg { - if argsList.Len() < 1 { - return newErrorFormulaArg(formulaErrorVALUE, "VAR.P requires at least 1 argument") - } - return fn.VARP(argsList) + return fn.vars("VAR.P", argsList) +} + +// VARPA function returns the Variance of a given set of values. The syntax of +// the function is: +// +// VARPA(number1,[number2],...) +// +func (fn *formulaFuncs) VARPA(argsList *list.List) formulaArg { + return fn.vars("VARPA", argsList) } // WEIBULL function calculates the Weibull Probability Density Function or the diff --git a/calc_test.go b/calc_test.go index a2ac719..46f4f08 100644 --- a/calc_test.go +++ b/calc_test.go @@ -912,9 +912,9 @@ func TestCalcCellValue(t *testing.T) { "=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", + "=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 @@ -922,10 +922,20 @@ func TestCalcCellValue(t *testing.T) { // TRIMMEAN "=TRIMMEAN(A1:B4,10%)": "2.5", "=TRIMMEAN(A1:B4,70%)": "2.5", + // VAR + "=VAR(1,3,5,0,C1)": "4.916666666666667", + "=VAR(1,3,5,0,C1,TRUE)": "4", + // VARA + "=VARA(1,3,5,0,C1)": "4.7", + "=VARA(1,3,5,0,C1,TRUE)": "3.86666666666667", // VARP - "=VARP(A1:A5)": "1.25", + "=VARP(A1:A5)": "1.25", + "=VARP(1,3,5,0,C1,TRUE)": "3.2", // VAR.P "=VAR.P(A1:A5)": "1.25", + // VARPA + "=VARPA(1,3,5,0,C1)": "3.76", + "=VARPA(1,3,5,0,C1,TRUE)": "3.22222222222222", // WEIBULL "=WEIBULL(1,3,1,FALSE)": "1.103638323514327", "=WEIBULL(2,5,1.5,TRUE)": "0.985212776817482", @@ -2191,12 +2201,18 @@ func TestCalcCellValue(t *testing.T) { "=TRIMMEAN(A1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", "=TRIMMEAN(A1,1)": "#NUM!", "=TRIMMEAN(A1,-1)": "#NUM!", + // VAR + "=VAR()": "VAR requires at least 1 argument", + // VARA + "=VARA()": "VARA requires at least 1 argument", // VARP "=VARP()": "VARP requires at least 1 argument", "=VARP(\"\")": "#DIV/0!", // VAR.P "=VAR.P()": "VAR.P requires at least 1 argument", "=VAR.P(\"\")": "#DIV/0!", + // VARPA + "=VARPA()": "VARPA requires at least 1 argument", // WEIBULL "=WEIBULL()": "WEIBULL requires 4 arguments", "=WEIBULL(\"\",1,1,FALSE)": "#VALUE!", |