summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go60
-rw-r--r--calc_test.go19
2 files changed, 62 insertions, 17 deletions
diff --git a/calc.go b/calc.go
index 57b2cda..0931012 100644
--- a/calc.go
+++ b/calc.go
@@ -393,6 +393,7 @@ type formulaFuncs struct {
// COUPPCD
// COVAR
// COVARIANCE.P
+// COVARIANCE.S
// CRITBINOM
// CSC
// CSCH
@@ -645,6 +646,7 @@ type formulaFuncs struct {
// STDEV.S
// STDEVA
// STDEVP
+// STDEVPA
// SUBSTITUTE
// SUM
// SUMIF
@@ -6851,14 +6853,11 @@ func (fn *formulaFuncs) CONFIDENCEdotT(argsList *list.List) formulaArg {
}, size.Number/2, size.Number) / math.Sqrt(size.Number))
}
-// COVAR function calculates the covariance of two supplied sets of values. The
-// syntax of the function is:
-//
-// COVAR(array1,array2)
-//
-func (fn *formulaFuncs) COVAR(argsList *list.List) formulaArg {
+// covar is an implementation of the formula functions COVAR, COVARIANCE.P and
+// COVARIANCE.S.
+func (fn *formulaFuncs) covar(name string, argsList *list.List) formulaArg {
if argsList.Len() != 2 {
- return newErrorFormulaArg(formulaErrorVALUE, "COVAR requires 2 arguments")
+ return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 2 arguments", name))
}
array1 := argsList.Front().Value.(formulaArg)
array2 := argsList.Back().Value.(formulaArg)
@@ -6881,19 +6880,37 @@ func (fn *formulaFuncs) COVAR(argsList *list.List) formulaArg {
}
result += (arg1.Number - mean1.Number) * (arg2.Number - mean2.Number)
}
+ if name == "COVARIANCE.S" {
+ return newNumberFormulaArg(result / float64(n-skip-1))
+ }
return newNumberFormulaArg(result / float64(n-skip))
}
+// COVAR function calculates the covariance of two supplied sets of values. The
+// syntax of the function is:
+//
+// COVAR(array1,array2)
+//
+func (fn *formulaFuncs) COVAR(argsList *list.List) formulaArg {
+ return fn.covar("COVAR", argsList)
+}
+
// COVARIANCEdotP function calculates the population covariance of two supplied
// sets of values. The syntax of the function is:
//
// COVARIANCE.P(array1,array2)
//
func (fn *formulaFuncs) COVARIANCEdotP(argsList *list.List) formulaArg {
- if argsList.Len() != 2 {
- return newErrorFormulaArg(formulaErrorVALUE, "COVARIANCE.P requires 2 arguments")
- }
- return fn.COVAR(argsList)
+ return fn.covar("COVARIANCE.P", argsList)
+}
+
+// COVARIANCEdotS function calculates the sample covariance of two supplied
+// sets of values. The syntax of the function is:
+//
+// COVARIANCE.S(array1,array2)
+//
+func (fn *formulaFuncs) COVARIANCEdotS(argsList *list.List) formulaArg {
+ return fn.covar("COVARIANCE.S", argsList)
}
// calcStringCountSum is part of the implementation countSum.
@@ -9131,12 +9148,17 @@ func (fn *formulaFuncs) STANDARDIZE(argsList *list.List) formulaArg {
return newNumberFormulaArg((x.Number - mean.Number) / stdDev.Number)
}
-// stdevp is an implementation of the formula functions STDEVP and STDEV.P.
+// stdevp is an implementation of the formula functions STDEVP, STDEV.P and
+// STDEVPA.
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)
+ fnName := "VARP"
+ if name == "STDEVPA" {
+ fnName = "VARPA"
+ }
+ varp := fn.vars(fnName, argsList)
if varp.Type != ArgNumber {
return varp
}
@@ -9161,6 +9183,15 @@ func (fn *formulaFuncs) STDEVdotP(argsList *list.List) formulaArg {
return fn.stdevp("STDEV.P", argsList)
}
+// STDEVPA function calculates the standard deviation of a supplied set of
+// values. The syntax of the function is:
+//
+// STDEVPA(number1,[number2],...)
+//
+func (fn *formulaFuncs) STDEVPA(argsList *list.List) formulaArg {
+ return fn.stdevp("STDEVPA", argsList)
+}
+
// getTDist is an implementation for the beta distribution probability density
// function.
func getTDist(T, fDF, nType float64) float64 {
@@ -9576,6 +9607,9 @@ func (fn *formulaFuncs) vars(name string, argsList *list.List) formulaArg {
}
for arg := argsList.Front(); arg != nil; arg = arg.Next() {
for _, token := range arg.Value.(formulaArg).ToList() {
+ if token.Value() == "" {
+ continue
+ }
num := token.ToNumber()
if token.Value() != "TRUE" && num.Type == ArgNumber {
summerA += num.Number * num.Number
diff --git a/calc_test.go b/calc_test.go
index c553d12..98d3d45 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1162,6 +1162,10 @@ func TestCalcCellValue(t *testing.T) {
"=STDEVP(A1:B2,6,-1)": "2.40947204913349",
// STDEV.P
"=STDEV.P(A1:B2,6,-1)": "2.40947204913349",
+ // STDEVPA
+ "=STDEVPA(1,3,5,2)": "1.4790199457749",
+ "=STDEVPA(1,3,5,2,1,0)": "1.63299316185545",
+ "=STDEVPA(1,3,5,2,TRUE,\"text\")": "1.63299316185545",
// T.DIST
"=T.DIST(1,10,TRUE)": "0.82955343384897",
"=T.DIST(-1,10,TRUE)": "0.17044656615103",
@@ -1190,8 +1194,8 @@ func TestCalcCellValue(t *testing.T) {
"=VAR(1,3,5,0,C1)": "4.91666666666667",
"=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",
+ "=VARA(1,3,5,0,C1)": "4.91666666666667",
+ "=VARA(1,3,5,0,C1,TRUE)": "4",
// VARP
"=VARP(A1:A5)": "1.25",
"=VARP(1,3,5,0,C1,TRUE)": "3.2",
@@ -1201,8 +1205,8 @@ func TestCalcCellValue(t *testing.T) {
"=VAR.S(1,3,5,0,C1)": "4.91666666666667",
"=VAR.S(1,3,5,0,C1,TRUE)": "4",
// VARPA
- "=VARPA(1,3,5,0,C1)": "3.76",
- "=VARPA(1,3,5,0,C1,TRUE)": "3.22222222222222",
+ "=VARPA(1,3,5,0,C1)": "3.6875",
+ "=VARPA(1,3,5,0,C1,TRUE)": "3.2",
// WEIBULL
"=WEIBULL(1,3,1,FALSE)": "1.10363832351433",
"=WEIBULL(2,5,1.5,TRUE)": "0.985212776817482",
@@ -3050,6 +3054,9 @@ func TestCalcCellValue(t *testing.T) {
// STDEV.P
"=STDEV.P()": "STDEV.P requires at least 1 argument",
"=STDEV.P(\"\")": "#DIV/0!",
+ // STDEVPA
+ "=STDEVPA()": "STDEVPA requires at least 1 argument",
+ "=STDEVPA(\"\")": "#DIV/0!",
// T.DIST
"=T.DIST()": "T.DIST requires 3 arguments",
"=T.DIST(\"\",10,TRUE)": "strconv.ParseFloat: parsing \"\": invalid syntax",
@@ -4361,6 +4368,8 @@ func TestCalcCOVAR(t *testing.T) {
"=COVAR(A2:A9,B2:B9)": "16.633125",
"=COVARIANCE.P(A1:A9,B1:B9)": "16.633125",
"=COVARIANCE.P(A2:A9,B2:B9)": "16.633125",
+ "=COVARIANCE.S(A1:A9,B1:B9)": "19.0092857142857",
+ "=COVARIANCE.S(A2:A9,B2:B9)": "19.0092857142857",
}
for formula, expected := range formulaList {
assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
@@ -4373,6 +4382,8 @@ func TestCalcCOVAR(t *testing.T) {
"=COVAR(A2:A9,B3:B3)": "#N/A",
"=COVARIANCE.P()": "COVARIANCE.P requires 2 arguments",
"=COVARIANCE.P(A2:A9,B3:B3)": "#N/A",
+ "=COVARIANCE.S()": "COVARIANCE.S requires 2 arguments",
+ "=COVARIANCE.S(A2:A9,B3:B3)": "#N/A",
}
for formula, expected := range calcError {
assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 04:22:31 +0000